SQL JOINS: Defeat the monster!

A couple of weeks ago, during one of our private workshops, one of our attendees mentioned that SQL joins were very confusing for him.

Suddenly, most of the attendees agreed.

So, we told them that the "SQL joins" monster is a myth, and with a simple guide, we'd help them defeat this monster!

And here are the keys to slaying the SQL joins beast once and for all:

Initial Tables Overview

These are the initial tables that we'll be using throughout the guide to demonstrate each type of SQL join.

We're following Rails standards for the SQL, but it's obviously open to non-Rails developers as we're only showing SQL code:

employees table:

 id |  name    | department_id 
----+----------+--------------
 1  | Alice    | 10
 2  | Bob      | 20
 3  | Charlie  | NULL
 4  | David    | 10
 5  | Emma     | 30

(5 rows)

departments table:

 id |  name      
----+-----------
 10 | Sales     
 20 | Marketing 
 30 | IT        
 40 | HR 

(4 rows)

INNER JOIN

The INNER JOIN returns only the records where there is a match between the left table (employees) and the right table (departments).

Query:

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

Result:

 emp_id |  name   | dept_name 
--------+---------+-----------
 1      | Alice   | Sales
 2      | Bob     | Marketing
 4      | David   | Sales
 5      | Emma    | IT

(4 rows)

What it does

Retrieves employees who have a matching department.

Why

Use INNER JOIN to find data that exists in both tables, such as employees who are assigned to departments.

LEFT JOIN

The LEFT JOIN returns all records from the left table (employees) and any matched records from the right table (departments). If there is no match, NULL is returned for columns from the right table.

Query:

SELECT employees.id, employees.name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

Result:

 emp_id |  name    | dept_name 
--------+----------+-----------
 1      | Alice    | Sales
 2      | Bob      | Marketing
 3      | Charlie  | NULL
 4      | David    | Sales
 5      | Emma     | IT

(5 rows)

What it does

Shows all employees, even those without a matching department.

Why

Useful when you want all records from the left table (employees), with data from the right table (departments) where available. Helps identify employees without any department assigned.

RIGHT JOIN

The RIGHT JOIN returns all records from the right table (departments) and any matched records from the left table (employees). If there is no match, NULL is returned for columns from the left table.

Query:

SELECT employees.id, employees.name, departments.name AS department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Result:

 emp_id |  name   | dept_name 
--------+---------+-----------
 1      | Alice   | Sales
 2      | Bob     | Marketing
 4      | David   | Sales
 5      | Emma    | IT
 NULL   | NULL    | HR

(5 rows)

What it does

Shows all departments, even those without employees.

Why

Helps find departments that currently do not have any employees assigned. Useful for finding gaps or unused departments.

FULL JOIN

The FULL JOIN returns all records when there is a match in either the left table (employees) or the right table (departments). If there is no match, NULL is returned in the missing side’s columns.

Query:

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

Result:

 emp_id |  name    | dept_name 
--------+----------+-----------
 1      | Alice    | Sales
 2      | Bob      | Marketing
 3      | Charlie  | 
 4      | David    | Sales
 5      | Emma     | IT
 NULL   | NULL     | HR

(6 rows)

What it does

Combines all records from both tables, even if they don’t match.

Why

This gives a complete view of both tables. Useful when you need a full overview of both employees and departments, including those without any relationships.

CROSS JOIN

The CROSS JOIN returns the Cartesian product of the two tables, meaning each row from the left table (employees) is combined with every row from the right table (departments).

Query:

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

Result:

 emp_id |  name   | dept_name 
--------+---------+-----------
 1      | Alice   | Sales
 1      | Alice   | Marketing
 1      | Alice   | IT
 1      | Alice   | HR
 2      | Bob     | Sales
 2      | Bob     | Marketing
 2      | Bob     | IT
 2      | Bob     | HR
 3      | Charlie | Sales
 3      | Charlie | Marketing
 3      | Charlie | IT
 3      | Charlie | HR
 4      | David   | Sales
 4      | David   | Marketing
 4      | David   | IT
 4      | David   | HR
 5      | Emma    | Sales
 5      | Emma    | Marketing
 5      | Emma    | IT
 5      | Emma    | HR

(20 rows)

What it does

Pairs each employee with every department.

Why

Useful for generating all possible combinations. Typically used in limited cases, like testing combinations or generating data for specific analysis.

Conclusion

SQL joins may seem intimidating at first, but with a solid understanding of each type and how they work, they become powerful tools in managing relational data. Practice these joins, explore different scenarios, and soon the "SQL joins" monster will be a thing of the past!

Try It Out Yourself

You can open an SQL console, create a database, create the two tables, and add a few records to test it yourself with the following queries.

Database Setup Code

CREATE DATABASE my_super_db;

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', NULL),
(4, 'David', 10),
(5, 'Emma', 30);

INSERT INTO departments (id, name) VALUES
(10, 'Sales'),
(20, 'Marketing'),
(30, 'IT'),
(40, 'HR');

All Joins Code Block

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

-- LEFT JOIN
SELECT employees.id, employees.name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

-- RIGHT JOIN
SELECT employees.id, employees.name, departments.name AS department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

-- FULL JOIN
SELECT employees.id, employees.name, departments.name AS department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;

-- CROSS JOIN
SELECT employees.id, employees.name, departments.name AS department_name
FROM employees
CROSS JOIN departments;

Voilà!