SQL Joins: A Comprehensive Guide with Examples- 5 Types

When working with databases, it’s common to need data from multiple tables at the same time. SQL Joins allow you to combine records from two or more tables in a relational database based on a related column between them. This post will cover the different types of SQL joins, their syntax, and practical examples for each.

What is a SQL Join?

A SQL join is a method to retrieve data from multiple tables based on a related column between them. It helps bring together related information into a single, combined result set.

For instance, consider two tables:

Employees:

Departments:

With SQL joins, you can combine data from these tables to understand which employees belong to which departments.

Types of SQL Joins

SQL supports several types of joins:

• INNER JOIN
• LEFT JOIN (LEFT OUTER JOIN)
• RIGHT JOIN (RIGHT OUTER JOIN)
• FULL JOIN (FULL OUTER JOIN)
• CROSS JOIN

Let’s dive into each type with examples:

01.INNER JOIN

Definition: Returns only the rows where there is a match between the tables.

Syntax:

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

Example Result:

In this example, INNER JOIN retrieves only the records where DepartmentID exists in both Employees and Departments. Since Charlie and DepartmentID 103 don’t have a corresponding entry in Departments, that row is excluded.

02.LEFT JOIN (LEFT OUTER JOIN)

Definition: Returns all rows from the left table and matched rows from the right table. If no match exists, NULL is returned for right table columns.

Syntax:

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

Example Result:

Here, LEFT JOIN includes all employees, even if there is no corresponding department. Charlie is included, but with NULL as the DepartmentName.

03.RIGHT JOIN (RIGHT OUTER JOIN)

Definition: Returns all rows from the right table and matched rows from the left table. If no match exists, NULL is returned for left table columns.

Syntax:

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

Example Result:

In this example, RIGHT JOIN retrieves all departments, even if there is no corresponding employee. Finance is included, but with NULL as EmployeeName.

04.FULL JOIN (FULL OUTER JOIN)

Definition: Returns all records when there is a match in either left or right table. Rows with no match in either table will have NULL values for the unmatched side.

Syntax:

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

Example Result:

In this example, FULL JOIN returns all rows from both tables, filling in NULL where a match is not found.

05.CROSS JOIN

Definition: Returns the Cartesian product of the two tables. Each row in the first table is combined with all rows in the second table.

Syntax:

SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Example Result:

A CROSS JOIN results in every combination of rows between the Employees and Departments tables.

Which Join to Use?

• Use an INNER JOIN when you only want the rows that have matching values in both tables.
• Use a LEFT JOIN when you want all rows from the left table, regardless of whether they have a match in the right table.
• Use a RIGHT JOIN when you want all rows from the right table, regardless of whether they have a match in the left table.
• Use a FULL JOIN when you want all rows from both tables, showing NULL where there’s no match.
• Use a CROSS JOIN when you need to pair every row of the first table with every row of the second table.

Conclusion

SQL joins are a powerful feature for combining data from multiple tables based on relationships between their columns. Understanding the different types of joins and their use cases can help you retrieve data efficiently and accurately.

By mastering these joins, you can handle complex queries, perform data analysis, and create comprehensive reports that draw on multiple sources of data in a relational database.

Happy querying!