MySQL Join 3 Tables
Joining three tables in a single SQL query can be very tricky if you are not good with the concept of SQL Join.
SQL Joins have always been tricky not only for new programmers but for many others, who are in programming and SQL for more than 2 to 3 years.
There are enough to confuse someone on SQL JOIN ranging from various types of SQL JOIN like INNER JOIN and OUTER JOIN, LEFT JOIN and RIGHT outer JOIN, CROSS JOIN, etc.
Between all of these fundamentals, What is most important about Join is, combining multiple tables. If you need data from multiple tables in one SELECT query you need to use either subquery or JOIN.
Important Points To Remember
To join 3 tables, You should know the following points –
- All the 3 tables should be in a relationship with a foreign key(FK).
- Each table must have a common column.
- The common column must contain the matching values.
- A common column of the first tables must have a primary key and another common column of the second tables must have a foreign key.
- datatype & name of the common column may be the same or different.
- All 3 tables contain the same or the different numbers of rows or columns with some values.
Three table JOIN syntax in SQL
SELECT t1.col, t3.col FROM table1 JOIN table2 ON table1.primarykey = table2.foreignkey JOIN table3 ON table2.primarykey = table3.foreignkey
Example
To understand SQL join Query for 3 tables. You have to see an example. So, Let’s discuss it with the three tables like students, contacts, universities. Each table is related to each other with the Foreign keys.
Now, Lets see the structure of the following tables closely.
The students table will be joined to universities table based on a common column university_id.
The contacts table will be joined to students table based on a common column student_id .
INNER JOIN 3 Tables
If you use the INNER JOIN keyword with the select statement, It will return a new result table by joining the above three tables with the matching records of each table.
Syntax:
You can join 3 tables using the following INNER JOIN syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
FROM table1
INNER JOIN table2 ON table1.table1_id = table2.table1_id
INNER JOIN table3 ON table2.table2_id = table3.table2_id;
Query for the above tables–
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities INNER JOIN students ON universities.university_id = students.university_id INNER JOIN contacts ON students.student_id = contacts.student_id;
Output –
| full_name | gender | mobile_number | email_address | university_name |
| Noor Khan | Male | 2222222222 | aaaa222@gmail.com | SRM University |
| Avneesh Mishra | Male | 5555555555 | bbbb555@gmail.com | Amity University |
| Monika Singh | Female | 7777777777 | cccc777@gmail.com | Anna University |
| Aaliya Khan | Female | 8888888888 | dddd888@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1010101010 | eeee101@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1212121212 | ffff121@gmail.com | Hindustan University |
| Avneesh Mishra | Male | 1313131313 | gggg131@gmail.com | Amity University |
| Avneesh Mishra | Male | 1414141414 | iiii141@gmail.com | Amity University |
LEFT JOIN 3 Tables
If you use the LEFT JOIN keyword with the select statement, It will return a new result table by joining the above three tables with all the records of left the table and matching values of the right tables.
Syntax
You can join 3 tables using the following INNER JOIN syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
FROM table1
LEFT JOIN table2 ON table1.table1_id = table2.table1_id
LEFT JOIN table3 ON table2.table2_id = table3.table2_id;
Example Query
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities LEFT JOIN students ON universities.university_id = students.university_id LEFT JOIN contacts ON students.student_id = contacts.student_id;
Output
| full_name | gender | mobile_number | email_address | university_name |
| Noor Khan | Male | 2222222222 | aaaa222@gmail.com | SRM University |
| Avneesh Mishra | Male | 5555555555 | bbbb555@gmail.com | Amity University |
| Monika Singh | Female | 7777777777 | cccc777@gmail.com | Anna University |
| Aaliya Khan | Female | 8888888888 | dddd888@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1010101010 | eeee101@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1212121212 | ffff121@gmail.com | Hindustan University |
| Avneesh Mishra | Male | 1313131313 | gggg131@gmail.com | Amity University |
| Avneesh Mishra | Male | 1414141414 | iii141@gmail.com | Amity University |
| Sunil Kumar | Male | NULL | NULL | Hindustan University |
| Mamta Gupta | Female | NULL | NULL | Hindustan University |
| Rapson Jani | Male | NULL | NULL | SRM University |
| Kundan Bharti | Male | NULL | NULL | SRM University |
RIGHT JOIN 3 Tables
If you use the RIGHT JOIN keyword with the select statement, It will return a new result table by joining the above three tables with all the records of right the table and matching values of the left tables.
Syntax
You can join 3 tables using the following RIGHT JOIN syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
FROM table1
RIGHT JOIN table2 ON table1.table1_id = table2.table1_id
RIGHT JOIN table3 ON table2.table2_id = table3.table2_id;
Example Query
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities RIGHT JOIN students ON universities.university_id = students.university_id RIGHT JOIN contacts ON students.student_id = contacts.student_id;
Output
| full_name | gender | mobile_number | email_address | university_name |
| Noor Kha | Male | 2222222222 | aaaa222@gmail.com | SRM University |
| Avneesh Mishra | Male | 5555555555 | bbbb555@gmail.com | Amity University |
| Monika Singh | Female | 7777777777 | cccc777@gmail.com | Anna University |
| Aaliya Khan | Female | 8888888888 | dddd888@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1010101010 | eeee101@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1212121212 | ffff121@gmail.com | Hindustan University |
| Avneesh Mishra | Male | 1313131313 | gggg131@gmail.com | Amity University |
| Avneesh Mishra | Male | 1414141414 | iiii141@gmail.com | Amity University |
| NULL | NULL | 1515151515 | jjjj151@gmail.com | NULL |
FULL JOIN 3 Tables
If you use the RIGHT JOIN keyword with the select statement, It will return a new result table by joining the above three tables with all the records of right the table and left tables.
Syntax
You can join 3 tables using the following INNER JOIN syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
FROM table1
FULL JOIN table2 ON table1.table1_id = table2.table2_id
FULL JOIN table3 ON table2.table2_id = table3.table2_id;
Query
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities FULL JOIN students ON universities.university_id = students.university_id FULL JOIN contacts ON students.student_id = contacts.student_id;
Output
| full_name | gender | mobile_number | email_address | university_name |
| Noor Khan | Male | 2222222222 | aaaa222@gmail.com | SRM University |
| Avneesh Mishra | Male | 5555555555 | bbbb555@gmail.com | Amity University |
| Monika Singh | Female | 7777777777 | cccc777@gmail.com | Anna University |
| Aaliya Khan | Female | 8888888888 | dddd888@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1010101010 | eeee101@gmail.com | Hindustan University |
| Aaliya Khan | Female | 1212121212 | ffff121@gmail.com | Hindustan University |
| Avneesh Mishra | Male | 1313131313 | gggg131@gmail.com | Amity University |
| Avneesh Mishra | Male | 1414141414 | iiii141@gmail.com | Amity University |
| Sunil Kuma | Male | NULL | NULL | Hindustan University |
| Mamta Gupta | Female | NULL | NULL | Hindustan University |
| Rapson Jani | Male | NULL | NULL | SRM University |
| Kundan Bharti | Male | NULL | NULL | SRM University |
| Manmohan Singh | Male | NULL | NULL | NULL |
| Manisha Chaudhry | Male | NULL | NULL | NULL |
| NULL | NULL | 1515151515 | jjjj151@gmail.com | NULL |
MySQL Join 3 Tables with WHERE Clause Conditions
If you want to select those records that have a gender value is ‘male’ then you can join 3 tables with where clause.
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities LEFT JOIN students ON universities.university_id = students.university_id LEFT JOIN contacts ON students.student_id = contacts.student_id WHERE students.gender='male';
Join 3 Tables with ORDER BY Clause
If you want to select records based on full_name in alphabetical order then you can join 3 tables with ORDER BY clause.
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities LEFT JOIN students ON universities.university_id = students.university_id LEFT JOIN contacts ON students.student_id = contacts.student_id ORDER BY students.full_name;
Join 3 Tables with GROUP BY clause
If you want to count records by grouping gender then you can join 3 tables with the GROUP BY clause
SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name FROM universities LEFT JOIN students ON universities.university_id = students.university_id LEFT JOIN contacts ON students.student_id = contacts.student_id GROUP BY students.gender;
