Joining Three or More Tables in SQL

Joining multiple tables in SQL is some tricky task. It can be more difficult if you need to join more than two tables in single SQL query, we will analyze how to retrieve data from multiple tables using INNER JOINs. In this section, we have used two approaches to join three or more tables in SQL.

Example:

We are creating three tables, as follows:

student
marks
details

Table 1: student

create table student(s_id int primary key, s_name varchar(17));
insert into student values(1, 'Jack');
insert into student values(2, 'Rithvik');
insert into student values(3, 'Jaspreet');
insert into student values(4, 'Praveen');
insert into student values(5, 'Bisa');
insert into student values(6, 'Suraj');

STUDENT TABLE

In the above table s_id is the primary key.

Table 2: marks

create table marks(school_id int primary key, s_id int, score int, status varchar(20));
insert into marks values(1004, 1, 23, 'fail');
insert into marks values(1008, 6, 95, 'pass');
insert into marks values(1012, 2, 97, 'pass');
insert into marks values(1016, 7, 67, 'pass'); 
insert into marks values(1020, 3, 100, 'pass');
insert into marks values(1025, 8, 73, 'pass');
insert into marks values(1030, 4, 88, 'pass');
insert into marks values(1035, 9,  13, 'fail');
insert into marks values(1040, 5,  16, 'fail');
insert into marks values(1050, 10, 53, 'pass');

MARKS TABLE

In the above table, school_id is primary key and s_id is the foreign key.

Table 3: details

create table details(address_city varchar(20), email_ID varchar(20),    school_id int, accomplishments varchar(50));

insert into details values('Bangalore',  'jsingh@jtp.com',
                                1020, 'ACM ICPC selected');
insert into details values('Hyderabad', 'praveen@jtp.com',
                                1030, 'Geek of the month');
insert into details values('Delhi',     'rithvik@jtp.com',
                                    1012, 'IOI finalist');
insert into details values('Chennai',   'om@jtp.com',
                                 1111, 'Geek of the year');
insert into details values('Banglore', ' suraj@jtp.com',
                                 1008, 'IMO finalist');
insert into details values('Mumbai',    'sasukeh@jtp.com',
                                  2211, 'Made a robot');
insert into details values('Ahmedabad', 'itachi@jtp.com',
                               1172, 'Code Jam finalist');
insert into details values('Jaipur',    'kumar@jtp.com',
                                   1972, 'KVPY finalist');

In the above table, school_id is the foreign key.

There are two approaches to join three or more tables in SQL:

1. Using JOINS in SQL:

The same logic is applied here which is used to join two tables i.e., the minimum number of join statements to join n tables are (n-1).

select s_name, score, status, address_city, email_id,
accomplishments from student s inner join mark m on
s.s_id = m.s_id inner join details d on 
d.school_id = m.school_id;

Output:

2. Using the Parent-child Relationship:

In the parent-child relationship, we use where clause to join two or more tables. Create column X as a primary key in one table and a foreign key in another table

Look at the tables which are created:
s_id is the primary key in the student table and foreign key in the marks table. (student (parent) - marks(child)).
school_id is the primary key in the marks table and foreign key in the student table. (marks(parent) - details(child)).

Query:

select s_name, score, status, address_city, 
email_id, accomplishments from student s, 
marks m, details d where s.s_id = m.s_id and 
m.school_id = d.school_id;

Output: