Joining Three or More Tables in SQL

SQL joins are an essential part of relational database management, allowing users to combine data from multiple tables efficiently. When the required data is spread across different tables, joining these tables efficiently is necessary.

In this article, we’ll cover everything we need to know about joining three or more tables in SQL. From basic to advanced techniques, this article will help us understand how to use SQL joins effectively, with practical examples and explanations.

Why We need to Join Multiple Tables in SQL?

In SQL, a JOIN is used to combine rows from two or more tables based on a related column between them. A table join can be used to fetch data across multiple tables, which is a common scenario in database queries.

Sometimes, the data we need is spread across multiple tables. By joining these tables, we can:

• Combine data from different tables into a single result set.
  
• Retrieve related information based on conditions.
  
• Perform complex queries that require multiple sources of data.

How to Join Three or More Tables in SQL?

To join three or more tables in SQL, we need to specify how the tables relate to each other using common columns. There are two main methods for joining three or more tables: using SQL joins and using a parent-child relationship. Let's explore both approaches in detail.

1. Using SQL Joins to Join Three Tables

The most common and efficient way to join three or more tables is by using the JOIN keyword. We can apply the same logic as joining two tables but extend it by chaining multiple JOIN statements. The minimum number of JOIN statements required to join n tables is n-1.

Example: Joining Three Tables Using INNER JOIN

Let's say we have three tables: student, marks, and details. These tables store different pieces of information, and we need to combine them into a single query result.

Table 1: student

Stores student information like s_id (student ID) and s_name (student name).

Table 2: marks

Stores the student marks, status, and school_id (ID of the school). s_id is a foreign key referencing the student table.

Table 3: details

Contains additional details like address_city, email_id, and accomplishments. school_id is a foreign key referencing the marks table.

Now, to fetch a student's name, score, status, address, and accomplishments from these three tables, we use the INNER JOIN statement:

Query:

SELECT s.s_name, m.score, m.status, d.address_city, d.email_id, d.accomplishments
FROM student s
INNER JOIN marks m ON s.s_id = m.s_id
INNER JOIN details d ON m.school_id = d.school_id;

Output:

Explanation:

• The first INNER JOIN combines the student and marks tables on the s_id column.
  
• The second INNER JOIN combines the result of the first join with the details table on the school_id column.
  
• This query will return a result combining data from all three tables, where records with matching s_id and school_id are included.

2. Using parent-child relationship:

Another way to join three or more tables is by utilizing a parent-child relationship between the tables. In this method, each table is connected through foreign keys, where one table's primary key is referenced as a foreign key in another table. This establishes a parent-child relationship, ensuring that data from multiple tables is linked properly. In this case:

• The student table is the parent, and the marks table is the child (since s_id in marks references s_id in student).
  
• The marks table is the parent, and the details table is the child (since school_id in details references school_id in marks).

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:

Explanation:

• Here, we use the WHERE clause to specify the conditions for matching records across the three tables.
  
• This approach combines the tables without explicitly using the JOIN keyword but still follows the relational logic between the tables.

Advanced Techniques for Joining Multiple Tables

1. Using LEFT JOIN for Including Unmatched Rows

If we want to include unmatched rows from one of the tables, we can use a LEFT JOIN. This is particularly useful when we want to keep all records from the first table (e.g., student) even if there is no match in the oother tables.

Query:

SELECT s.s_name, m.score, m.status, d.address_city, d.email_id, d.accomplishments
FROM student s
LEFT JOIN marks m ON s.s_id = m.s_id
LEFT JOIN details d ON m.school_id = d.school_id;

Explanation:

• This query retrieves all students, including those without corresponding records in the marks or details tables.
• If there is no match, the fields from the unmatched tables will be NULL.

2. Using FULL OUTER JOIN to Get All Possible Matches

To include all records from all tables, whether they match or not, you can use a FULL OUTER JOIN. This is useful when you need to fetch every row from all tables, even if they don't match.

Query:

SELECT s.s_name, m.score, m.status, d.address_city, d.email_id, d.accomplishments
FROM student s
FULL OUTER JOIN marks m ON s.s_id = m.s_id
FULL OUTER JOIN details d ON m.school_id = d.school_id;

Explanation: This query returns all records from the student, marks, and details tables, with NULL values in columns where no match exists.

Conclusion

Joining multiple tables in SQL is an essential skill for database management and reporting. By mastering SQL join techniques, you can efficiently retrieve and analyze data from multiple related tables. Whether using basic INNER JOIN or more advanced joins like LEFT JOIN and FULL OUTER JOIN, knowing when and how to use them will allow us to handle complex queries with ease.