SQL Join 4 Tables – Join Query for Four Tables with Example

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 –

SQL JOIN Query for 3 Tables

How to Join Tables in SQL

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

Table Name – subjects

This table will be joined to courses table based on a common column  course_id.

Table Name – chapters

This table will be joined to subjects table based on a common column  subject_id.

Table Name – subchapters

This table will be joined to chapters table based on a common column  chapter_id.

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 –

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.