In certain situations, it becomes necessary to retrieve data from multiple tables within a database. These scenarios often arise when complex relationships exist between entities, requiring data aggregation from three or more tables. This article explores how to Join 3 or more Tables in SQL with two distinct approaches to accomplish this task efficiently.
When faced with such data retrieval requirements, developers and database administrators must carefully consider the structure and relationships among the tables involved. By understanding the underlying database schema and employing the appropriate techniques, one can successfully extract the desired information in a coherent and organized manner.
Read: Types of Joins in SQL
How to Join 3 Tables in SQL?
To Join 3 or more Tables in SQL, first you need to create three tables:
Student
Marks
Student_details
Table 1: Student
Create table "Student"
create table student(
student_id int primary key,
student_name varchar(20));Insert values in the table:
insert into student values(01, 'Rahul');
insert into student values(02, 'Pranav');
insert into student values(03, 'Jaspreet');
insert into student values(04, 'Praveen');
insert into student values(05, 'Aishwarya');
insert into student values(06, 'Suraj');Output:
Table 2: Marks
Create table "Marks"
create table Marks(
Roll_Number int primary key,
student_id int,
Marks int,
Status varchar(20));Insert the value in the table:
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(1040, 5, 16, 'fail');
insert into Marks values(1020, 3, 100, 'pass');
insert into Marks values(1030, 4, 88, 'pass');Output:
Table 3: Student_details
Create table Student_details:
create table Student_details(
City varchar(20),
Email_ID varchar(20),
school_id int,
accomplishments varchar(50));Insert the values in the table:
insert into Student_details values('Banglore', 'Rahul@gmail.com',
1004, 'ACM ICPC selected');
insert into Student_details values('Hyderabad', 'Suraj@gmail.com',
1008, 'Student of the month');
insert into Student_details values('Delhi', 'Pranav@gmail.com',
1012, 'IOI finalist');
insert into Student_details values('Chennai', 'Aishwarya@gmail.com',
1040, 'Student of the year');
insert into Student_details values('Uttrakhand', ' Jaspreet@gmail.com',
1020, 'IMO finalist');
insert into Student_details values('Chennai', 'Praveen@gmail.com',
1030, 'Made a robot');Output:
Join 3 tables in SQL
When joining three or more tables in SQL, there are two common approaches.
Use Joins to join the table
Use Parent child relationship
Approach 1: Using joins in SQL to join the table:
The first approach is to use joins to connect the tables based on their related columns. In this case, the minimum number of join statements required to join 'n' tables is (n-1).
Query:
select student_name, Marks, status, City, Email_id, accomplishments
from student s
inner join Marks m on s.student_id = m.student_id
inner join Student_details d on d.Roll_Number = m.Roll_Number;In this query, we have three tables: student, Marks, and Student_details. Here's a breakdown of how the query works:
The INNER JOIN keyword is used to join the student table with the Marks table. The ON keyword specifies the condition for joining, which in this case is the equality between student_id in the student table and student_id in the Marks table.
Next, another INNER JOIN is used to join the result of the previous join (which is a temporary table) with the Student_details table. The ON keyword specifies the condition for joining, which is the equality between Roll_Number in the Student_details table and Roll_Number in the temporary table (result of the first join).
The columns student_name, Marks, status, City, Email_id, and accomplishments are selected from the resulting joined table.
Output:
Advantages of using this approach:
Flexibility in retrieving data based on specific conditions.
Precise selection of data that meets defined criteria.
Independent relationships between tables, allowing for versatile data retrieval.
Disadvantages of using this approach:
Complexity when dealing with a large number of tables or intricate relationships.
Potential performance impact if joins are poorly optimized or result sets are large.
Increased query complexity, making queries harder to read, maintain, and troubleshoot.
Approach 2: Using parent-child relationship:
The parent-child relationship is a way to establish a connection between two tables based on a common column. In this approach, one table is considered the parent table, and another table is considered the child table. The primary key of the parent table is also used as a foreign key in the child table, creating a relationship between them.
Let's look at the table which we have creates:
The "student" table has a primary key named "student_id," which is also a foreign key in the "Marks" table.
The "Marks" table has a primary key named "school_id," which is a foreign key in the "student_details" table.
The query joins the tables using the appropriate key relationships to retrieve the desired columns.
Query:
select student_name, Marks, status, City, Email_id, accomplishments
from student s, Marks m, Student_details d
where s.student_id = m.student_id and m.Roll_Number = d.Roll_Number;In the above query, WHERE is used to define the conditions for joining the tables. It specifies that the "student_id" column in the "student" table must match the "student_id" column in the "marks" table. Additionally, the "Roll_Number" column in the "marks" table must match the "Roll_Number" column in the "Student_details" table.
By using the table aliases (s, m, d) and the join conditions in the WHERE, the query retrieves the desired information by connecting the related tables through their parent-child relationships.
Output:
Advantages of using this approach:
Parent-child relationships simplify the query structure and make it easier to understand.
These relationships enforce referential integrity, ensuring consistency and preventing errors.
Ideal for representing and querying hierarchical data structures.
Disadvantages of using this approach:
Parent-child relationships may not be suitable for complex queries or non-hierarchical relationships.
Child tables duplicate primary keys, leading to increased storage requirements and potential inconsistencies.
Navigating hierarchical relationships can result in slower query performance.
Conclusion
Joining three or more tables in SQL can be a complex yet essential task when it comes to retrieving data from interconnected entities. Throughout this article, we have explored two approaches to accomplish this objective efficiently. Both approaches have their merits and should be chosen based on the specific requirements and complexities of the database schema. Understanding the structure and relationships within the database is crucial for determining the most appropriate approach.
Comments