SQL Joins – Combining Data from Multiple Tables

Lesson 10: SQL Joins – Combining Data from Multiple Tables

Estimated Read Time: 25-30 minutes

Objective: In this lesson, you will learn how to use SQL joins to combine data from multiple tables. We will explore different types of joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

1. What is a SQL JOIN?

A JOIN is used to combine rows from two or more tables based on a related column between them. Joins allow us to retrieve data from multiple tables efficiently and make sense of data relationships.

2. Types of Joins

There are different types of SQL joins:

• INNER JOIN: Combines rows from both tables where there is a match.
• LEFT JOIN (LEFT OUTER JOIN): Combines all rows from the left table with matching rows from the right table. If no match exists, NULL values are returned for columns from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Combines all rows from the right table with matching rows from the left table. If no match exists, NULL values are returned for columns from the left table.
• FULL JOIN (FULL OUTER JOIN): Combines all rows from both tables. Where there is no match, NULL values are returned for columns from the table without a match.

3. INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables. It excludes rows that do not have a match in both tables.

Syntax of INNER JOIN:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:

Let’s assume we have two tables: Employees and Departments. The Employees table has columns EmployeeID, Name, and DepartmentID. The Departments table has columns DepartmentID and DepartmentName.

Query: To get the names of employees and their department names:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Name      | DepartmentName
John      | HR
Alice     | IT

4. LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values will be returned for columns from the right table.

Syntax of LEFT JOIN:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example:

Query: To get all employees and their department names, including employees who do not belong to any department:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Name      | DepartmentName
John      | HR
Alice     | IT
Bob       | NULL

5. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values will be returned for columns from the left table.

Syntax of RIGHT JOIN:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example:

Query: To get all departments and the employees working in them, including departments without any employees:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Name      | DepartmentName
John      | HR
Alice     | IT
NULL      | Finance

6. FULL JOIN (FULL OUTER JOIN)

The FULL JOIN returns all rows from both tables. Where there is no match, NULL values will be returned for the missing side.

Syntax of FULL JOIN:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Example:

Query: To get all employees and all departments, including those employees who do not belong to a department and departments without employees:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Name      | DepartmentName
John      | HR
Alice     | IT
Bob       | NULL
NULL      | Finance

7. Using Joins with Multiple Tables

Joins are not limited to combining two tables; you can use joins to combine more than two tables as well. Here’s how you can use joins with three or more tables:

Example:

Query: To get the employee names, department names, and their manager’s name (assuming a Managers table with columns ManagerID and ManagerName):

SELECT Employees.Name, Departments.DepartmentName, Managers.ManagerName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
INNER JOIN Managers ON Departments.ManagerID = Managers.ManagerID;

8. Practice Exercises

Try these exercises to practice SQL joins:

• Write a query to find the employees who do not belong to any department using a LEFT JOIN.
• Write a query to find all departments and their employees using a RIGHT JOIN.
• Write a query to find all employees and their department names using an INNER JOIN.
• Write a query to find all departments, their employees, and their managers using multiple joins.

9. Conclusion

In this lesson, you learned how to use SQL joins to combine data from multiple tables. You explored the INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, along with examples and use cases. Joins are essential for querying relational databases effectively and retrieving related data from different tables.

Key Takeaways:

• INNER JOIN: Combines rows from both tables where there is a match.
• LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL JOIN: Returns all rows from both tables, matching or not.
• Multiple Joins: You can join more than two tables to combine data effectively.