
        
        
            
                
                
                    SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Examples

Consider the following table about some students:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

The above sample table can be created using the code here.

Also consider the following table about students' extracurricular activities:

student_id	club	date_entered
1	Football	2016-02-13
2	Boxing	2016-05-25
3	Apple	2018-08-17
4	Fishing	2017-01-01
5	NULL	NULL

The above sample table can be created using the code here.

Basic usage

To retrieve information about students and their extracurricular activities by using the common column student_id:


        
        
            
                
                
                    SELECT fname, lname, age, club
FROM students
INNER JOIN extracurricular
ON students.student_id = extracurricular.student_id;
                
            
            +----------+--------+------+----------+
| fname    | lname  | age  | club     |
+----------+--------+------+----------+
| Sky      | Towner |   17 | Football |
| Ben      | Davis  |   19 | Boxing   |
| Travis   | Apple  |   18 | Chess    |
| Arthur   | David  |   16 | Fishing  |
| Benjamin | Town   |   17 | NULL     |
+----------+--------+------+----------+

Here we return information on fname, lname and age from students table and the corresponding club information from the extracurricular table when there is a match between student_id value in both tables.

Using aliases, the above query can be shortened to:


        
        
            
                
                
                    SELECT fname, lname, age, club
FROM students s
INNER JOIN extracurricular e
ON s.student_id = e.student_id;

Ambiguous column names

The column student_id appears in both the students and extracurricular tables. In such cases if we do not specify which table’s student_id column we are referring to , the confused MySQL server will throw an error:


        
        
            
                
                
                    SELECT student_id, fname, lname, age, club
FROM students
INNER JOIN extracurricular
ON students.student_id = extracurricular.student_id;
                
            
            ERROR 1052 (23000): Column 'student_id' in field list is ambiguous

The correct way to specify the above if we wanted to retrieve student_id from extracurricular would be:


        
        
            
                
                
                    SELECT extracurricular.student_id, fname, lname, age, club
FROM students
INNER JOIN extracurricular
ON students.student_id = extracurricular.student_id;
                
            
            +------------+----------+--------+------+----------+
| student_id | fname    | lname  | age  | club     |
+------------+----------+--------+------+----------+
|          1 | Sky      | Towner |   17 | Football |
|          2 | Ben      | Davis  |   19 | Boxing   |
|          3 | Travis   | Apple  |   18 | Chess    |
|          4 | Arthur   | David  |   16 | Fishing  |
|          5 | Benjamin | Town   |   17 | NULL     |
+------------+----------+--------+------+----------+

Using aliases the above can be further shortened to:


        
        
            
                
                
                    SELECT e.student_id, fname, lname, age, club
FROM students s
INNER JOIN extracurricular e
ON s.student_id = e.student_id;

We provide students table with an alias s and the extracurricular table with an alias e. This allows us to refer to these tables using the provided aliases elsewhere in the query.

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

Official MySQL Documentation

https://dev.mysql.com/doc/refman/8.0/en/join.html

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!