Understanding SQL Joins: A Comprehensive Guide

SQL joins are fundamental operations that allow you to combine data from two or more tables based on a related column. They are essential for querying relational databases efficiently and effectively. In this guide, we’ll explore the different types of SQL joins and how they work.

Note: The below SQL examples are tested in Snowflake.

Set Up

Let’s start by setting up our database, tables and inserting some sample data for our queries to run against.

CREATE DATABASE if not exists company;

USE company;

CREATE TABLE if not exists departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE if not exists employees (
    employee_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER,
    manager_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO departments (department_id, department_name) VALUES 
(1, 'HR'),
(2, 'Finance'),
(3, 'Engineering'),
(4, 'Marketing'),
(5, 'Sales');

INSERT INTO employees (employee_id, name, department_id, manager_id) VALUES 
(1, 'Alice', 1, NULL),
(2, 'Bob', 2, 1),
(3, 'Charlie', 3, 1),
(4, 'David', 3, 2),
(5, 'Eve', 4, 1),
(6, 'Frank', NULL, 1),
(7, 'Grace', 4, NULL);

Inner Join

An Inner Join returns only the rows where there is a match in both tables. It excludes rows that do not have matching values in both tables.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

+---------+-----------------+                                                   
| NAME    | DEPARTMENT_NAME |
|---------+-----------------|
| Alice   | HR              |
| Bob     | Finance         |
| Charlie | Engineering     |
| David   | Engineering     |
| Eve     | Marketing       |
| Grace   | Marketing       |
+---------+-----------------+

Left 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 employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

+---------+-----------------+                                                   
| NAME    | DEPARTMENT_NAME |
|---------+-----------------|
| Alice   | HR              |
| Bob     | Finance         |
| Charlie | Engineering     |
| David   | Engineering     |
| Eve     | Marketing       |
| Frank   | NULL            |
| Grace   | Marketing       |
+---------+-----------------+

Right 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 employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

+---------+-----------------+                                                   
| NAME    | DEPARTMENT_NAME |
|---------+-----------------|
| Alice   | HR              |
| Bob     | Finance         |
| Charlie | Engineering     |
| David   | Engineering     |
| Eve     | Marketing       |
| Grace   | Marketing       |
| NULL    | Sales           |
+---------+-----------------+

Full Outer Join

A Full Outer Join returns all rows when there is a match in either the left or right table. Rows without a match in either table will also be included, with NULLs in place of missing matches.

SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

+---------+-----------------+                                                   
| NAME    | DEPARTMENT_NAME |
|---------+-----------------|
| Alice   | HR              |
| Bob     | Finance         |
| Charlie | Engineering     |
| David   | Engineering     |
| Eve     | Marketing       |
| Frank   | NULL            |
| Grace   | Marketing       |
| NULL    | Sales           |
+---------+-----------------+

Cross Join

A Cross Join returns the Cartesian product of the two tables, meaning it combines all rows from the first table with all rows from the second table. It does not require a matching condition.

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

+---------+-----------------+                                                   
| NAME    | DEPARTMENT_NAME |
|---------+-----------------|
| Alice   | HR              |
| Alice   | Finance         |
| Alice   | Engineering     |
| Alice   | Marketing       |
| Alice   | Sales           |
| Bob     | HR              |
| Bob     | Finance         |
| Bob     | Engineering     |
| Bob     | Marketing       |
| Bob     | Sales           |
| Charlie | HR              |
| Charlie | Finance         |
| Charlie | Engineering     |
| Charlie | Marketing       |
| Charlie | Sales           |
| David   | HR              |
| David   | Finance         |
| David   | Engineering     |
| David   | Marketing       |
| David   | Sales           |
| Eve     | HR              |
| Eve     | Finance         |
| Eve     | Engineering     |
| Eve     | Marketing       |
| Eve     | Sales           |
| Frank   | HR              |
| Frank   | Finance         |
| Frank   | Engineering     |
| Frank   | Marketing       |
| Frank   | Sales           |
| Grace   | HR              |
| Grace   | Finance         |
| Grace   | Engineering     |
| Grace   | Marketing       |
| Grace   | Sales           |
+---------+-----------------+

Self Join

A Self Join is a regular join, but the table is joined with itself. It is useful for comparing rows within the same table.

SELECT a.name AS employee1, b.name AS employee2
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

+-----------+-----------+                                                       
| EMPLOYEE1 | EMPLOYEE2 |
|-----------+-----------|
| Bob       | Alice     |
| Charlie   | Alice     |
| Eve       | Alice     |
| Frank     | Alice     |
| David     | Bob       |
+-----------+-----------+

Conclusion

SQL joins are powerful tools for querying relational databases. Understanding the differences between inner joins, left joins, right joins, full joins, cross joins, and self joins will enable you to manipulate and retrieve data more effectively. Experiment with these joins to see how they can be applied to your specific data needs.