Joins

1. What is a Join?

A join is an SQL operation that combines records from two or more tables based on related columns. The primary purpose of a join is to assemble data from multiple tables to provide a comprehensive view of the information. Joins are performed using the JOIN clause, which specifies the columns on which the tables should be joined.

Credit: devtodev

2. Types of Joins

There are several types of joins in SQL, each serving a specific purpose. The most common types are:

1. Join (Inner Join)

2. Left Join (Left Outer Join)

3. Right Join (Right Outer Join)

4. Full Join (Full Outer Join)

5. Cross Join

6. Self Join

Let’s explore each type with examples.

Consider two tables, employees and departments :

Table Definitions

Employees Table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    manager_id INT
);

Departments Table:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

Inserting Data into Employees and Departments Tables:

INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES
(1, 'John', 'Doe', 1, NULL),
(2, 'Jane', 'Smith', 2, 1),
(3, 'Sam', 'Brown', NULL, 1),
(4, 'Mike', 'Davis', 1, 1),
(5, 'Sara', 'Wilson', 3, 2);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Human Resources'),
(2, 'Finance'),
(3, 'Engineering'),
(4, 'Marketing');

2.1. Inner Join

An inner join returns only the rows that have matching values in both tables. It is the most commonly used type of join.

SELECT 
    e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d 
ON e.department_id = d.department_id;

Result:

employee_id | first_name | last_name | department_name
------------------------------------------------------
1           | John       | Doe       | Human Resources
2           | Jane       | Smith     | Finance
4           | Mike       | Davis     | Human Resources
5           | Sara       | Wilson    | Engineering

NOTE: No return for employee_id = 3

2.2. Left Join (or Left Outer Join)

A left join returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.

SELECT 
    e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id;

Result:

employee_id | first_name | last_name | department_name
------------------------------------------------------
1           | John       | Doe       | Human Resources
2           | Jane       | Smith     | Finance
3           | Sam        | Brown     | NULL
4           | Mike       | Davis     | Human Resources
5           | Sara       | Wilson    | Engineering

2.3. Right Join (or Right Outer Join)

A right join returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.

SELECT 
    e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id;

Result:

employee_id | first_name | last_name | department_name
------------------------------------------------------
1           | John       | Doe       | Human Resources
2           | Jane       | Smith     | Finance
4           | Mike       | Davis     | Human Resources
5           | Sara       | Wilson    | Engineering
NULL        | NULL       | NULL      | Marketing

2.4. Full Join (or Full Outer Join)

A full join returns all rows when there is a match in either table. If there is no match, NULL values are returned for columns from the table without a match.

SELECT 
    e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d 
ON e.department_id = d.department_id;

Result:

employee_id | first_name | last_name | department_name
------------------------------------------------------
1           | John       | Doe       | Human Resources
2           | Jane       | Smith     | Finance
3           | Sam        | Brown     | NULL
4           | Mike       | Davis     | Human Resources
5           | Sara       | Wilson    | Engineering
NULL        | NULL       | NULL      | Marketing

2.5. Cross Join

A cross join returns the Cartesian product of the two tables, i.e., all possible combinations of rows from the two tables. In our example, employees table has 5 records and departments table has 4 records. Therefore, cross join should result in 5x4= 20 records/combinations.

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;

Result:

employee_id | first_name | last_name | department_name
------------------------------------------------------
1           | John       | Doe       | Human Resources
1           | John       | Doe       | Finance
1           | John       | Doe       | Engineering
1           | John       | Doe       | Marketing
2           | Jane       | Smith     | Human Resources
2           | Jane       | Smith     | Finance
2           | Jane       | Smith     | Engineering
2           | Jane       | Smith     | Marketing
3           | Sam        | Brown     | Human Resources
3           | Sam        | Brown     | Finance
3           | Sam        | Brown     | Engineering
3           | Sam        | Brown     | Marketing
4           | Mike       | Davis     | Human Resources
4           | Mike       | Davis     | Finance
4           | Mike       | Davis     | Engineering
4           | Mike       | Davis     | Marketing
5           | Sara       | Wilson    | Human Resources
5           | Sara       | Wilson    | Finance
5           | Sara       | Wilson    | Engineering
5           | Sara       | Wilson    | Marketing

2.6. Self Join

A self join is a regular join where a table is joined with itself. This is useful for scenarios where you need to compare rows within the same table. For example, let's say we want to find employees and their managers from the employees table.

SELECT 
       e1.employee_id AS employee_id, 
       e1.first_name AS employee_first_name, 
       e1.last_name AS employee_last_name,
       e2.first_name AS manager_first_name, 
       e2.last_name AS manager_last_name
FROM employees e1
LEFT JOIN employees e2 
ON e1.manager_id = e2.employee_id;

Result:

employee_id | employee_first_name | employee_last_name | manager_first_name | manager_last_name
---------------------------------------------------------------------------------------------
1           | John                | Doe                | NULL               | NULL
2           | Jane                | Smith              | John               | Doe
3           | Sam                 | Brown              | John               | Doe
4           | Mike                | Davis              | John               | Doe
5           | Sara                | Wilson             | Jane               | Smith

Below is one of the most popular diagram showing all joins, excluding cross and self joins:

Credit: CL Moffatt

3. Conclusion

Joins are a powerful feature of SQL that allow you to combine data from multiple tables in various ways to gain deeper insights and produce meaningful reports. Understanding the different types of joins and how to use them effectively is crucial for anyone working with relational databases. By mastering joins, you can write more efficient queries and build more complex and informative datasets.