You are going to learn SQL JOIN 4 tables with a simple example. If you need to join four tables in SQL, you are reading the right tutorial that will be very useful for your project.
Join 4 Tables in SQL
You can join 4 tables in SQL using any one of the following keywords. It depends on your project requirement. Don’t worry, Here we will learn to use all these keywords for joining four tables –
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
Learn more –
The Important Points
To join 4 tables, You should keep mind the following points –
- All the 4 tables must be stabilized a relationship with a foreign key.
- Each table must contain a common column.
- The common column may have matching values.
- A common may have the same or different datatype & name.
- All 4 tables may have the same or the different numbers of rows or columns.
The Example –
you can understand SQL join query for 4 tables with this example. So, Let’s discuss it with the four tables like courses, subjects, chapters, subchapters. Each table is related to each other with the Foreign keys.
Now, See the structure of the following tables.
Table Name – courses
| course_id | course_name |
| 5011 | Web Designing |
| 5012 | Web Development |
| 5013 | Programming |
Table Name – subjects
This table will be joined to courses table based on a common column course_id.
| subject_id | subject_name | course_name |
| 221 | HTML | 5011 |
| 222 | CSS | 5011 |
| 223 | JavaScript | 5011 |
| 224 | PHP | 5012 |
| 225 | Python | 5012 |
| 226 | .Net | 5012 |
| 227 | Java | 5013 |
| 228 | C++ | 5013 |
Table Name – chapters
This table will be joined to subjects table based on a common column subject_id.
| chapter_id | chapter_name | subject_id |
| 101 | HTML Text | 221 |
| 102 | HTML Image | 221 |
| 103 | HTML List | 221 |
| 104 | HTML Link | 221 |
| 105 | HTML Form | 221 |
| 106 | CSS Border | 222 |
| 107 | CSS Position | 222 |
| 108 | CSS Selector | 222 |
| 109 | PHP conditions | 224 |
| 110 | PHP arrays | 224 |
| 111 | Java Methods | 227 |
Table Name – subchapters
This table will be joined to chapters table based on a common column chapter_id.
| subchapter_id | subchapter_name | chapter_id |
| 1201 | HTML Paragraph | 101 |
| 1202 | HTML Heading | 101 |
| 1203 | HTML Ordered List | 103 |
| 1204 | HTML Unordered List | 103 |
| 1205 | HTML Text Link | 104 |
| 1206 | HTML Image Link | 104 |
| 1207 | CSS Border style | 106 |
| 1208 | CSS Border Width | 106 |
| 1209 | CSS Border Color | 106 |
| 1210 | CSS Absolute Position | 107 |
| 1211 | CSS Element Selector | 108 |
| 1212 | PHP if Condition | 109 |
| 1213 | PHP Switch Condition | 109 |
| 1214 | PHP Index Array | 110 |
| 1215 | PHP Associative Array | 110 |
| 1216 | Java Method Parameter | 111 |
| 1217 | Java Method Overloading | 111 |
| 1218 | SQL INNER JOIN | 224 |
INNER JOIN 4 Tables
If you need to join 4 tables using INNER JOIN, you will get the result in a new table with only matching values in all tables.
Syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
table4.column1_name, table4.column2_name,...,
FROM table1
INNER JOIN table2 ON table1.table1_id = table2.table1_id
INNER JOIN table3 ON table2.table2_id = table3.table2_id;
INNER JOIN table4 ON table3.table3_id = table4.table3_id;
Query –
SELECT courses.course_name, subjects.subject_name, chapters.chapter_name, subchapters.subchapter_name FROM courses INNER JOIN subjects ON courses.course_id = subjects.course_id INNER JOIN chapters ON subjects.subject_id = chapters.subject_id INNER JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;
Output –
| course_name | subject_name | chapter_name | subchapter_name |
| Web Designing | HTML | HTML Text | HTML Heading |
| Web Designing | HTML | HTML Text | HTML Paragraph |
| Web Designing | HTML | HTML List | HTML Ordered List |
| Web Designing | HTML | HTML List | HTML Unordered List |
| Web Designing | HTML | HTML Link | HTML Image Link |
| Web Designing | HTML | HTML Link | HTML Text Link |
| Web Designing | CSS | CSS Border | CSS Border Color |
| Web Designing | CSS | CSS Border | CSS Border Style |
| Web Designing | CSS | CSS Border | CSS Border Width |
| Web Designing | CSS | CSS Position | CSS Absolute Position |
| Web Designing | CSS | CSS Selector | CSS Element Selector |
| Web Development | PHP | PHP Conditions | PHP Switch Condition |
| Web Development | PHP | PHP Conditions | PHP if Condition |
| Web Development | PHP | PHP Arrays | PHP Associative array |
| Web Development | PHP | PHP Arrays | PHP Index Array |
| Programming | Java | Java Methods | ava Method Overloading |
| Programming | Java | Java Methods | ava Method Parameter |
LEFT JOIN 4 Tables
If you need to join 4 tables using LEFT JOIN, you will get the result in a new table with matching values in all left tables.
Syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
table4.column1_name, table4.column2_name,...,
FROM table1
LEFT JOIN table2 ON table1.table1_id = table2.table1_id
LEFT JOIN table3 ON table2.table2_id = table3.table2_id;
LEFT JOIN table4 ON table3.table3_id = table4.table3_id;
Query –
SELECT courses.course_name,
subjects.subject_name,
chapters.chapter_name,
subchapters.subchapter_name
FROM courses
LEFT JOIN subjects ON courses.course_id = subjects.course_id
LEFT JOIN chapters ON subjects.subject_id = chapters.subject_id
LEFT JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;
RIGHT JOIN 4 Tables
If you need to join 4 tables using INNER JOIN, you will get the result in a new table with matching values in all right tables.
Syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
table4.column1_name, table4.column2_name,...,
FROM table1
RIGHT JOIN table2 ON table1.table1_id = table2.table1_id
RIGHT JOIN table3 ON table2.table2_id = table3.table2_id;
RIGHT JOIN table4 ON table3.table3_id = table4.table3_id;
Query –
SELECT courses.course_name,
subjects.subject_name,
chapters.chapter_name,
subchapters.subchapter_name
FROM courses
RIGHT JOIN subjects ON courses.course_id = subjects.course_id
RIGHT JOIN chapters ON subjects.subject_id = chapters.subject_id
RIGHT JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;
FULL JOIN 4 Tables
If you need to join 4 tables using FULL JOIN, you will get the result in a new table with or without matching values in all tables.
Syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
table4.column1_name, table4.column2_name,...,
FROM table1
FULL JOIN table2 ON table1.table1_id = table2.table1_id
FULL JOIN table3 ON table2.table2_id = table3.table2_id;
FULL JOIN table4 ON table3.table3_id = table4.table3_id;
Query –
SELECT courses.course_name,
subjects.subject_name,
chapters.chapter_name,
subchapters.subchapter_name
FROM courses
FULL JOIN subjects ON courses.course_id = subjects.course_id
FULL JOIN chapters ON subjects.subject_id = chapters.subject_id
FULL JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;
Join 4 Tables with Conditions
Now, you will learn to join 4 tables with different conditions using the INNER JOIN keyword. after learning it, you will be able to use LEFT JOIN, RIGHT JOIN & FULL JOIN keywords.
Join 4 Tables with WHERE Clause
If you want to join all four tables and select those records that have subject_name values are ‘HTML’ then you can use where clause.
SELECT courses.course_name,
subjects.subject_name,
chapters.chapter_name,
subchapters.subchapter_name
FROM courses
INNER JOIN subjects ON courses.course_id = subjects.course_id
INNER JOIN chapters ON subjects.subject_id = chapters.subject_id
INNER JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id
WHERE subjects.subject_name='HTML';
Join 4 Tables with ORDER BY Clause
If you want to join all four tables and select records based on course_name in alphabetical order then you use the ORDER BY clause.
SELECT courses.course_name,
subjects.subject_name,
chapters.chapter_name,
subchapters.subchapter_name
FROM courses
INNER JOIN subjects ON courses.course_id = subjects.course_id
INNER JOIN chapters ON subjects.subject_id = chapters.subject_id
INNER JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id
ORDER BY courses.course_name DESC;
Join 4 Tables with GROUP BY clause
If you want to join all four tables and count all records by grouping course_name then you can use the GROUP BY clause
Query –
SELECT courses.course_name, COUNT(*) FROM courses INNER JOIN subjects ON courses.course_id = subjects.course_id INNER JOIN chapters ON subjects.subject_id = chapters.subject_id INNER JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id GROUP BY courses.course_name;
How to Join 4 Tables in SQL
To join 4 tables, you have to configure the following steps –
- First, make sure that the SQL package is installed on your computer
- Create and use a MySQL Database
- Create 4 tables in MySQL database
- Insert some records in all 4 tables
- Join all three 4 tables using INNER JOIN
Query –
mysql>CREATE DATABASE join_db;
mysql>USE DATABASE join_db;
mysql>CREATE TABLE courses
(
course_id int(10),
course_name varchar(255)
);
mysql>INSERT INTO courses (course_id, course_name)
VALUES (511, 'Web Designing'),
(512, 'Web Development'),
(513, 'Programming');
mysql>CREATE TABLE subjects
(
subject_id int(10),
subject_name varchar(255),
course_id int(10)
);
mysql>INSERT INTO subjects (subject_id, subject_name, course_id)
VALUES (221, 'HTML', 511),
(222, 'CSS', 511),
(223, 'Javascript', 511),
(224, 'PHP', 512),
(225, 'Python', 512),
(226, '.Net', 512),
(227, 'Java', 513),
(228, 'C++', 513);
mysql>CREATE TABLE chapters
(
chapter_id int(10),
chapter_name varchar(255),
subject_id int(10)
);
mysql>INSERT INTO chapters (chapter_id, chapter_name, subject_id)
VALUES (101, 'HTML Text', 221),
(102, 'HTML Image', 221),
(103, 'HTML List', 221),
(104, 'HTML Link', 221),
(105, 'HTML Form', 221),
(106, 'CSS Border',222),
(107, 'CSS Position',222),
(108, 'CSS Selector', 222),
(109, 'PHP Conditions',224),
(110, 'PHP Arrays', 224),
(111, 'Java Methods', 227);
mysql>CREATE TABLE subchapters
(
subchapter_id int(10),
subchapter_name varchar(255),
chapter_id int(10)
);
mysql>INSERT INTO subchapters (subchapter_id, subchapter_name, chapter_id)
VALUES (1201, 'HTML Paragraph', 101),
(1202, 'HTML Heading', 101),
(1203, 'HTML Ordered List', 103),
(1204, 'HTML Unordered List', 103),
(1205, 'HTML Text Link', 104),
(1206, 'HTML Image Link',104),
(1207, 'CSS Border Style',106),
(1208, 'CSS Border Width', 106),
(1209, 'CSS Border Color', 106),
(1210, 'CSS Absolute Position', 107),
(1211, 'CSS Element Selector', 108),
(1212, 'PHP if Condition', 109),
(1213, 'PHP Switch COndition', 109),
(1214, 'PHP Index Array', 110),
(1215, 'PHP Associative Array', 110),
(1216, 'Java Method Parameter', 111),
(1217, 'Java Method Overloading', 111),
(1218, 'SQL INNER JOIN', 224);
mysql>SELECT courses.course_name,
subjects.subject_name,
chapters.chapter_name,
subchapters.subchapter_name
FROM courses
INNER JOIN subjects ON courses.course_id = subjects.course_id
INNER JOIN chapters ON subjects.subject_id = chapters.subject_id
INNER JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;
Tutorial Summary
Dear Developer, I have this tutorial with complete information. I hope that You have learned to join four tables using SQL. Now you can join any type of table.
If you have any questions, Kindly ask me through the comment box. Even, you should share this tutorial with your friends.