Joins

A join combines rows from two or more tables based on a related column.

Concept

A join combines rows from two or more tables based on a related column. Because a normalized database splits data across tables (customers in one, orders in another), joins are how you put it back together.

Think of a join in two steps:

Conceptually form every combination of rows from the two tables (the Cartesian product).

Keep only the combinations that satisfy the join condition, then (for outer joins) add back unmatched rows padded with NULLs.

The database does not literally do step 1 (it uses efficient algorithms, Module 20), but the result is as if it did.

We will use these two small tables for the diagrams:

Venn-style overview (L = left table, R = right table):

employees (emp_id, first_name, dept_id)        departments (dept_id, dept_name)
+--------+------------+---------+              +---------+-------------+
| emp_id | first_name | dept_id |              | dept_id | dept_name   |
+--------+------------+---------+              +---------+-------------+
| 1      | Asha       | 1       |              | 1       | Engineering |
| 2      | Vikram     | 1       |              | 2       | Sales       |
| 3      | Neha       | 1       |              | 3       | HR          |
| 4      | Rahul      | 2       |              | 4       | Finance     |  <- no employees
| 5      | Priya      | 2       |              +---------+-------------+
| 6      | Arjun      | 3       |
| 7      | Kiran      | NULL    |  <- no department
| 8      | Meera      | 1       |
+--------+------------+---------+

INNER JOIN          LEFT JOIN           RIGHT JOIN          FULL OUTER JOIN
  ( L (##) R )       (##L##(##) R )      ( L (##)##R##)      (##L##(##)##R##)
  only matches      all L + matches     all R + matches     everything

LEFT ANTI JOIN (LEFT JOIN ... WHERE R.key IS NULL)
  (##L##(  ) R )    rows in L with no match in R

Why It Matters

Nearly every real query touches more than one table.

Wrong join types silently drop rows (INNER instead of LEFT) or duplicate them (joining on non-unique keys), producing wrong reports.

Join questions dominate SQL interviews.

Syntax

JOIN alone means INNER JOIN.

OUTER is optional: LEFT JOIN = LEFT OUTER JOIN.

Old-style (implicit) join: FROM a, b WHERE a.key = b.key — equivalent to INNER JOIN but not recommended (easy to forget the condition and create a Cartesian product; cannot express outer joins in standard syntax).

SELECT columns
FROM left_table [AS] l
{INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | CROSS} JOIN right_table [AS] r
    ON l.key = r.key            -- not used with CROSS JOIN
[WHERE ...];

Example

13.1 INNER JOIN Returns only rows with a match in both tables.

Kiran (dept_id NULL) is missing: NULL = anything is UNKNOWN, so no match.

Finance is missing: no employee references it.

13.2 LEFT JOIN (LEFT OUTER JOIN) Returns all rows from the left table, plus matching rows from the right. Where there is no match, right-side columns are NULL.

Customers with their orders (including customers with none):

Anti-join — rows in the left table with no match:

Test a column that cannot be NULL in real matches (the right table's primary key), not a nullable column.

13.3 ON vs WHERE in outer joins (critical) A condition on the right table placed in WHERE removes the NULL-padded rows and turns the LEFT JOIN into an INNER JOIN:

Rule: filters on the preserved (left) table go in WHERE; filters on the optional (right) table go in ON. For INNER JOIN, ON and WHERE are equivalent.

13.4 RIGHT JOIN (RIGHT OUTER JOIN) Returns all rows from the right table plus matches from the left.

A RIGHT JOIN B equals B LEFT JOIN A. Most teams write only LEFT JOINs for readability. SQLite supports RIGHT and FULL joins only from version 3.39.

13.5 FULL OUTER JOIN Returns all rows from both tables; unmatched rows on either side are NULL-padded.

(9 rows: 7 matched + Finance with no employee + Kiran with no department.)

MySQL does not support FULL OUTER JOIN. Emulate it:

(UNION removes the duplicated matched rows. If legitimate duplicate rows can exist, use UNION ALL with WHERE e.emp_id IS NULL on the second query instead.) 13.6 CROSS JOIN

Returns the Cartesian product: every row of A combined with every row of B. Row count = rows(A) × rows(B).

Uses: generating all combinations (every product × every store; every date × every sensor) as a "scaffold" to LEFT JOIN actual data onto, so missing combinations show as zero. Danger: an accidental cross join of two 1-million-row tables produces 10^12 rows.

13.7 SELF JOIN A table joined to itself using two different aliases. Used for hierarchical or comparative data within one table.

Employee and their manager:

Employees earning more than their manager (classic interview question):

Result: no rows in the sample data (every employee earns less than their manager). Try it after UPDATE employees SET salary = 130000 WHERE emp_id = 5; inside a transaction — Priya (130000) would exceed Rahul (120000).

Pairs of employees in the same department (avoid pairing a row with itself and avoid mirror duplicates):

13.8 Join conditions, ON, and USING Non-equi joins (conditions other than =):

13.9 Multiple joins (more than two tables) Order lines with customer and product names:

Each join adds one table; join each new table to one already in the query. Once a LEFT JOIN is used, subsequent joins to the optional side should usually also be LEFT JOINs, otherwise an INNER JOIN later removes the NULL-padded rows.

Revenue per product, including products never sold:

(This includes the cancelled order 1004; add LEFT JOIN orders o ON ... AND o.status <> 'CANCELLED' logic if needed.)

13.10 Join duplicates (row multiplication / fan-out) When you join a row to N matching rows, it appears N times. Aggregating columns from the "one" side after joining the "many" side double-counts.

The correct value is 93900 (75800 + 18100). Each of Ananya's orders has two items, so every order total was counted twice.

Always ask: "What is the grain (one row per what?) of my result after each join?" 13.11 NULL behavior in joins

NULL = NULL is UNKNOWN, so rows with NULL join keys never match in an equality join (Kiran does not match any department, even a department with a NULL id).

Outer joins produce NULLs for missing sides; distinguish "no match" NULLs from real NULL values by testing a NOT NULL column (the PK) of the optional table.

To deliberately match NULL to NULL, use ON a.col IS NOT DISTINCT FROM b.col (PostgreSQL, SQL Server 2022+), a.col <=> b.col (MySQL), or (a.col = b.col OR (a.col IS NULL AND b.col IS NULL)).

13.12 INNER JOIN vs OUTER JOIN summary LEFT/RIGHT OUTER JOIN FULL OUTER JOIN Unmatched left rows Unmatched right rows On the optional side Order of tables matters "Orders with their customers" "All customers and their orders if any" "Reconcile two lists" ON or WHERE equivalent Optional-side filters must go in ON

Careful: filters in WHERE remove padded rows

SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

SELECT c.customer_id, c.name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- Intention: all customers, with their DELIVERED orders if any
-- WRONG: WHERE filters out customers without delivered orders
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'DELIVERED';

-- RIGHT: put the right-table filter in ON
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'DELIVERED'
ORDER BY c.customer_id, o.order_id;

SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id, e.first_name;

SELECT e.first_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id, e.first_name;

-- MySQL
SELECT e.first_name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.first_name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;

CREATE TABLE quarters (q VARCHAR(2));
INSERT INTO quarters VALUES ('Q1'), ('Q2');

SELECT d.dept_name, q.q
FROM departments d
CROSS JOIN quarters q
ORDER BY d.dept_id, q.q;

Expected Result

Department headcount including empty departments:

Note COUNT(e.emp_id), not COUNT(*): COUNT(*) would return 1 for Finance because the NULL-padded row still counts as a row.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

Common Mistakes

Using INNER JOIN when unmatched rows must be kept.

Filtering the optional table in WHERE after a LEFT JOIN.

COUNT(*) instead of COUNT(right_table.pk) after a LEFT JOIN.

Missing join condition → Cartesian product (especially with old comma syntax).

Joining on non-unique columns and double counting (fan-out).

Ambiguous column names: SELECT dept_id FROM employees JOIN departments ... fails — qualify with an alias.

Joining columns of different types (INT vs VARCHAR), preventing index use.

Using DISTINCT to hide duplicates caused by a wrong join.

Best Practices

Use explicit JOIN ... ON syntax, never comma joins.

Always alias tables and qualify every column in multi-table queries.

Prefer LEFT JOIN over RIGHT JOIN for readability.

Know the grain of each table and of the result.

Index foreign key columns used in joins.

Aggregate before joining when combining multiple one-to-many relationships.

Interview Relevance

The single most tested SQL topic. Expect: explain all join types with examples; predict row counts; "find customers with no orders"; "employees and their managers"; "employees who earn more than their manager"; "difference between LEFT JOIN with filter in ON vs WHERE"; "how to do FULL OUTER JOIN in MySQL".

Row-count interview puzzle: Table A has values (1, 1, 2, NULL), table B has (1, 1, 3, NULL). How many rows does A INNER JOIN B ON A.v = B.v return? Answer: 4 (each of A's two 1s matches each of B's two 1s; NULLs never match). LEFT JOIN returns 6 (4 matches + A's 2 and NULL unmatched). FULL JOIN returns 8 (6 + B's 3 and NULL).

Practice Questions

List every order with the customer's name and city.

List every product and the number of distinct orders it appears in (including zero).

Find departments with no employees.

Find employees who have no manager, and employees who manage nobody.

For each employee, show their manager's manager (two-level self join).

Show total quantity sold per category.

Explain why this returns fewer rows than expected: SELECT ... FROM customers c LEFT JOIN orders o ON ... WHERE o.order_date >= DATE '2026-02-01'.