SQL Joins Guide

SQL joins are essential when combining data from multiple tables in a relational database. It is one of the popular SQL topics asked in Data Science Interviews. So, if you want to learn about SQL joins in detail, this article is for you. In this article, I’ll take you through a guide to SQL joins.

SQL Joins Guide

SQL Joins are used to combine rows from two or more tables based on a related column between them. The most common types of SQL joins are:

1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN (FULL OUTER JOIN)
5. CROSS JOIN

Let’s understand all these SQL joins step by step. To understand all these joins, we will be using the tables shown below.

Now, let’s go through all types of SQL joins.

INNER JOIN

An INNER JOIN returns records that have matching values in both tables.

Here’s an implementation of INNER JOIN on our example tables:

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

Only the rows with matching DepartmentID in both tables are included in the result.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all records from the left table (Employees), and the matched records from the right table (Departments). If there is no match, the result is NULL from the right side.

Here’s an implementation of LEFT JOIN on our example tables:

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

All records from the Employees table are returned, and for Linda, there is no corresponding DepartmentID, so DepartmentName is NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN returns all records from the right table (Departments), and the matched records from the left table (Employees). If there is no match, the result is NULL from the left side.

Here’s an implementation of RIGHT JOIN on our example tables:

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

All records from the Departments table are returned. For the Marketing department, there is no corresponding EmployeeID, so EmployeeName is NULL.

FULL JOIN (FULL OUTER JOIN)

A FULL JOIN returns all records when there is a match in either the left or right table. If there is no match, the result is NULL on the side that doesn’t have a match.

Here’s an implementation of FULL JOIN on our example tables:

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

All records from both tables are returned, with NULL in places where there is no match.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the two tables.

Here’s an implementation of CROSS JOIN on our example tables:

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

Every row from Employees is paired with every row from Departments, which results in all possible combinations.

Summary

So, here’s how SQL joins work:

1. INNER JOIN: Only returns rows where there is a match in both tables.
2. LEFT JOIN: Returns all rows from the left table, with matching rows from the right table. If no match, returns NULL.
3. RIGHT JOIN: Returns all rows from the right table, with matching rows from the left table. If no match, returns NULL.
4. FULL JOIN: Returns all rows when there is a match in either table. If no match, returns NULL on the side that doesn’t have a match.
5. CROSS JOIN: Returns all possible combinations of rows between the two tables.