SQL JOINs

A JOIN lines up rows from two tables using a condition, usually a foreign key matching a primary key. These tables have deliberate gaps: a department with nobody in it and an employee with no department.

-- setup.sql
CREATE TABLE departments (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       TEXT NOT NULL,
    dept_id    INT REFERENCES departments(id),   -- nullable: not yet assigned
    manager_id INT REFERENCES employees(id)
);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Legal');
INSERT INTO employees VALUES
    (1, 'Ana',  1, NULL), (2, 'Ben', 1, 1), (3, 'Cai', 2, 1),
    (4, 'Dee',  2, 3),    (5, 'Eli', NULL, 1);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 5

INNER JOIN

An inner join keeps only rows that match on both sides.

-- inner.sql
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;

$ psql -d sqldemo -f inner.sql
 name |    dept     
------+-------------
 Ana  | Engineering
 Ben  | Engineering
 Cai  | Sales
 Dee  | Sales
(4 rows)

Five employees, four rows. Eli has no dept_id, so there is nothing to match and Eli vanishes. Legal has no employees and vanishes too. JOIN on its own means INNER JOIN.

LEFT JOIN

A left join keeps every row from the left table, matched or not.

-- left.sql
SELECT d.name AS dept, e.name AS employee
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
ORDER BY d.id, e.id;

$ psql -d sqldemo -f left.sql
    dept     | employee 
-------------+----------
 Engineering | Ana
 Engineering | Ben
 Sales       | Cai
 Sales       | Dee
 Legal       | 
(5 rows)

Legal now appears once, with an empty employee column: that blank is a NULL the join filled in because no employee matched. Which table is “left” matters; it is the one written before LEFT JOIN.

Finding Rows With No Match

Keep only the NULL-filled rows and you get an anti-join: departments with no staff.

-- anti_join.sql
SELECT d.name AS empty_dept
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;

$ psql -d sqldemo -f anti_join.sql
 empty_dept 
------------
 Legal
(1 row)

Testing e.id, the right table’s primary key, is safe because a real employee never has a NULL id. WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id) returns the same row.

The WHERE Clause That Breaks a LEFT JOIN

Goal: list every department, with only the employees whose name starts with “C”.

-- left_where_trap.sql
SELECT d.name AS dept, e.name AS employee
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.name LIKE 'C%'
ORDER BY d.id;

$ psql -d sqldemo -f left_where_trap.sql
 dept  | employee 
-------+----------
 Sales | Cai
(1 row)

Engineering and Legal are gone. The WHERE runs after the join, and for the rows the left join filled with NULL, NULL LIKE 'C%' is not true, so they are removed. The query quietly became an inner join. Move the condition into ON.

-- left_where_fixed.sql
SELECT d.name AS dept, e.name AS employee
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id AND e.name LIKE 'C%'
ORDER BY d.id;

$ psql -d sqldemo -f left_where_fixed.sql
    dept     | employee 
-------------+----------
 Engineering | 
 Sales       | Cai
 Legal       | 
(3 rows)

A condition in ON decides which rows count as a match; every left row survives either way. A condition in WHERE filters the final result. For outer joins the two are not interchangeable.

RIGHT and FULL OUTER JOIN

RIGHT JOIN keeps every row of the right table; FULL JOIN keeps unmatched rows from both.

-- full.sql
SELECT e.name AS employee, d.name AS dept
FROM employees e
FULL JOIN departments d ON d.id = e.dept_id
ORDER BY e.id NULLS LAST;

$ psql -d sqldemo -f full.sql
 employee |    dept     
----------+-------------
 Ana      | Engineering
 Ben      | Engineering
 Cai      | Sales
 Dee      | Sales
 Eli      | 
          | Legal
(6 rows)

Eli appears with no department and Legal appears with no employee, in one result. A RIGHT JOIN is a LEFT JOIN with the tables swapped, so most teams write every outer join as LEFT JOIN for readability.

CROSS JOIN

A cross join pairs every row with every row: no condition at all.

-- cross.sql
SELECT s.size, c.color
FROM (VALUES ('S'), ('M')) AS s(size)
CROSS JOIN (VALUES ('red'), ('blue'), ('green')) AS c(color)
ORDER BY s.size DESC, c.color;

$ psql -d sqldemo -f cross.sql
 size | color 
------+-------
 S    | blue
 S    | green
 S    | red
 M    | blue
 M    | green
 M    | red
(6 rows)

Two sizes times three colors is six rows. That multiplication is useful for generating combinations, and dangerous when it happens by accident: a join with a missing condition on two 10,000-row tables returns 100 million rows.

Self-Join

A table can join to itself. Here each employee’s manager_id points at another employee.

-- self.sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

$ psql -d sqldemo -f self.sql
 employee | manager 
----------+---------
 Ana      | 
 Ben      | Ana
 Cai      | Ana
 Dee      | Cai
 Eli      | Ana
(5 rows)

The aliases e and m make the same table play two roles. LEFT JOIN keeps Ana, who has no manager; an inner join would have dropped the person at the top of the tree.

Joining Three Tables

Many-to-many relationships need a linking table. Each join adds one more table.

-- three.sql
CREATE TABLE projects (id INT PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE assignments (employee_id INT REFERENCES employees(id),
                          project_id  INT REFERENCES projects(id));
INSERT INTO projects VALUES (10, 'Website'), (20, 'CRM');
INSERT INTO assignments VALUES (1, 10), (2, 10), (2, 20), (3, 20);

SELECT e.name, p.title
FROM employees e
JOIN assignments a ON a.employee_id = e.id
JOIN projects p    ON p.id = a.project_id
ORDER BY e.name, p.title;

$ psql -d sqldemo -f three.sql
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 4
 name |  title  
------+---------
 Ana  | Website
 Ben  | CRM
 Ben  | Website
 Cai  | CRM
(4 rows)

Ben works on two projects, so Ben appears twice. assignments holds only pairs of ids; the joins translate them into names at both ends.

Practice

1. List every employee with their department name, showing (none) for Eli.

-- practice_coalesce_dept.sql
SELECT e.name, COALESCE(d.name, '(none)') AS dept
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;

$ psql -d sqldemo -f practice_coalesce_dept.sql
 name |    dept     
------+-------------
 Ana  | Engineering
 Ben  | Engineering
 Cai  | Sales
 Dee  | Sales
 Eli  | (none)
(5 rows)

2. Find employees who are not assigned to any project.

-- practice_no_project.sql
SELECT e.name
FROM employees e
LEFT JOIN assignments a ON a.employee_id = e.id
WHERE a.employee_id IS NULL
ORDER BY e.name;

$ psql -d sqldemo -f practice_no_project.sql
 name 
------
 Dee
 Eli
(2 rows)

3. Count employees per department, including departments with zero.

-- practice_dept_counts.sql
SELECT d.name, count(e.id) AS staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY staff DESC, d.name;

$ psql -d sqldemo -f practice_dept_counts.sql
    name     | staff 
-------------+-------
 Engineering |     2
 Sales       |     2
 Legal       |     0
(3 rows)

4. List each manager with the number of people who report to them.

-- practice_reports.sql
SELECT m.name AS manager, count(*) AS reports
FROM employees e
JOIN employees m ON m.id = e.manager_id
GROUP BY m.name
ORDER BY reports DESC;

$ psql -d sqldemo -f practice_reports.sql
 manager | reports 
---------+---------
 Ana     |       3
 Cai     |       1
(2 rows)