Multiple Joins in SQL

Multiple join occurs when a query involves more than one JOIN operation. These operations are performed in sequence and the result is combination of multiple tables in a single query. For example, we will combine data from a students table, marks table and an attendance table. Rather than running separate queries, you can join all the three tables in a single query to retrieve the required information.

• By using multiple joins we can efficiently retrieve complex datasets that cover multiple tables.
• Whether you are working with sales data, customer information or employees records.
• JOINs helps you to connect related information across different tables effectively.

Steps to Implement Multiple Joins in SQL

Here we are going to implement the concept of multiple joins in SQL with the help of examples.

Step1: Set up the Database and Tables

In this example, we will first create a database called geeks that contains three tables: students, marks and attendance and then run our queries on those tables.

CREATE DATABASE geeks;

USE geeks;

create table students(id int, name varchar(50),branch varchar(50));
create table marks(id int, marks int);
create table attendance(id int, attendance int);

Step 2: Insert Data into Tables

Next we will insert sample data into these tables.

--students
insert into students values(1,'anurag','cse');
insert into students values(2,'harsh','ece');
insert into students values(3,'sumit','ece');
insert into students values(4,'kae','cse');

--marks
insert into marks values(1,95);
insert into marks values(2,85);
insert into marks values(3,80);
insert into marks values(4,65);

--attendance
insert into attendance values(1,75);
insert into attendance values(2,65);
insert into attendance values(3,80);
insert into attendance values(4,80);

Step 3: View Data from tables

select *from students;

Students Table

select *from marks;

Marks Table

select *from attendance;

Attendance table

Final Output:

Step 4: Using Multiple Joins in SQL

Let us now perform a multiple join where we join the students, marks and attendance tables. We will fetch data such that we get the marks and attendance of students who have attendance greater than or equal to 75.

Query :

SELECT s.id, s.name, m.marks, a.attendance
FROM students AS s
INNER JOIN marks AS m ON s.id = m.id
INNER JOIN attendance AS a ON s.id = a.id
WHERE a.attendance >= 75;

Output:

Explanation:

• We are joining the students table (s) with the marks table (m) using the id column.
• Next, we are joining the result with the attendance table (a) using the same id column.
• The WHERE clause filters the result to only show students with attendance >= 75

Types of Multiple Joins in SQL

1. Multiple INNER JOINS

When you use multiple INNER JOINs the query will return only the rows where there is a match in all the joined tables. For example, the below query will return data only for students who have records in both the marks and attendance tables.

Example:

SELECT s.id, s.name, m.marks, a.attendance
FROM students AS s
INNER JOIN marks AS m ON s.id = m.id
INNER JOIN attendance AS a ON s.id = a.id;

2. LEFT JOIN ( LEFT OUTER JOIN) with Multiple Tables

Using LEFT JOINs with multiple tables is used to retrieve all rows from the left table and matching rows from the other tables. NULL values will be returned for columns from non-matching tables if no match is found. For example, below query will retrieve all students including those who do not have matching records in the marks or attendance tables.

Example:

SELECT s.id, s.name, m.marks, a.attendance
FROM students AS s
LEFT JOIN marks AS m ON s.id = m.id
LEFT JOIN attendance AS a ON s.id = a.id;

3. RIGHT JOIN (RIGHT OUTER JOIN) with Multiples Tables

A RIGHT JOIN retrieves all rows from the right table and all the matched rows from the left table. If there is no match then NULL values are returned for columns from the left table.

Example:

SELECT s.id, s.name, m.marks, a.attendance
FROM students AS s
RIGHT JOIN marks AS m ON s.id = m.id
RIGHT JOIN attendance AS a ON s.id = a.id;

4. FULL OUTER JOIN with Multiple Tables

A FULL OUTER JOIN combines the effects of both LEFT JOIN and RIGHT JOIN. It returns all rows from both tables with NULL values where there is no match of values.

Example:

SELECT s.id, s.name, m.marks, a.attendance
FROM students AS s
FULL OUTER JOIN marks AS m ON s.id = m.id
FULL OUTER JOIN attendance AS a ON s.id = a.id;

Best Practices for Using Multiple Joins

1. Apply Aliases for Readability: Apply aliases to tables (s, m, a) for better readability and ease of query writing particularly when working on several joins.

2. Apply Correct Join Conditions: Make sure you join tables using the correct columns, usually foreign keys or other corresponding fields. Misjoined tables will result in incorrect or unexpected output.

3. Use WHERE and HAVING Clauses to Filter Data: To narrow down your results, utilize WHERE clauses to limit rows prior to and subsequent to the join operation. You can also apply the HAVING clause to restrict aggregated data.

4. Use Proper Join Type: Select the correct type of join (INNER, LEFT, RIGHT, FULL) depending on your needs. For instance, employ LEFT JOIN when you want all records from the left table, without consideration for matches in the right table.

Conclusion

A multiple join in SQL is a fundamental skill that helps you query relational databases more efficiently. When you chain multiple joins together, you can merge data from various tables and create detailed reports. INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN allow you to refine your queries and fetch the required data.