Chapter 5 · Combining Tables
LEFT, RIGHT, FULL, CROSS and Self Joins
- Page 17 of 22
- 16 min read
An inner join keeps only rows that found a partner. Often the rows without a partner are the answer: customers who signed up and never ordered, products nobody bought, categories with nothing in them. Outer joins keep those rows and fill the missing side with NULL. This page also covers two joins with a different shape: CROSS JOIN, which pairs everything with everything, and the self join, which joins a table to itself.
In data work these are the joins that keep a dataset honest. A churn model trained only on customers who ordered has never seen a customer who left before buying. A monthly sales chart built with an inner join skips the months with zero sales instead of showing them as zero. LEFT JOIN and CROSS JOIN fix both.
What you will learn
LEFT JOIN, and the "LEFT JOIN … IS NULL" pattern for finding missing matches- Why a condition in
ONand the same condition inWHEREgive different results RIGHT JOINandFULL OUTER JOIN, including how to get a full join on MySQLCROSS JOINfor complete grids (every month × every category)- Self joins: employees and their managers
LEFT JOIN: keep every row of the left table
A LEFT JOIN B ON … returns every row of A (the table written on the left). Where a row of A has partners in B, you get one row per partner, exactly like an inner join. Where it has none, you still get the row once, with every B column set to NULL.
SELECT c.customer_id, c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (1, 4, 8)
ORDER BY c.customer_id, o.order_id;+-------------+--------------+----------+-----------+
| customer_id | name | order_id | status |
+-------------+--------------+----------+-----------+
| 1 | Nadia Rahman | 1 | delivered |
| 1 | Nadia Rahman | 3 | delivered |
| 1 | Nadia Rahman | 10 | delivered |
| 4 | Rafiq Islam | 5 | cancelled |
| 8 | Karim Uddin | NULL | NULL |
+-------------+--------------+----------+-----------+Nadia has three orders, Rafiq one, and Karim none — but Karim is still here, with NULLs where the order would be. An inner join would have returned only the first four rows. (LEFT OUTER JOIN is the same thing; OUTER is optional.)
| Join | Rows kept | Missing side filled with |
|---|---|---|
INNER JOIN | only matched pairs | — |
LEFT JOIN | matched pairs + unmatched rows of the left table | NULL in the right table's columns |
RIGHT JOIN | matched pairs + unmatched rows of the right table | NULL in the left table's columns |
FULL OUTER JOIN | matched pairs + unmatched rows of both | NULL on whichever side is missing |
CROSS JOIN | every row paired with every row | — (no condition) |
Finding missing matches: LEFT JOIN … IS NULL
Since unmatched rows get NULL in the right table's columns, keeping only those rows answers "which ones have no partner?". This is called an anti-join:
SELECT c.customer_id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;+-------------+-------------+-------------------+
| customer_id | name | email |
+-------------+-------------+-------------------+
| 8 | Karim Uddin | karim@example.com |
+-------------+-------------+-------------------+That is the list a marketing team wants for a "complete your first order" email. Test a column that can never be NULL in a real match — the right table's primary key or the join column itself — so a NULL there can only mean "no partner". The same pattern finds products that were never sold:
SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL
ORDER BY p.product_id;+------------+-----------------------------+
| product_id | name |
+------------+-----------------------------+
| 10 | Noise-Cancelling Headphones |
| 11 | Gift Card |
+------------+-----------------------------+Counting with a LEFT JOIN: COUNT(*) vs COUNT(column)
When you count per group after a LEFT JOIN, the unmatched row is still a row. COUNT(*) counts it; COUNT(o.order_id) skips the NULL:
SELECT c.name, COUNT(*) AS count_star, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name;+-----------------+------------+--------+
| name | count_star | orders |
+-----------------+------------+--------+
| Farhana Akter | 3 | 3 |
| Nadia Rahman | 3 | 3 |
| Tanvir Ahmed | 3 | 3 |
| Imran Hossain | 2 | 2 |
| Mitu Das | 1 | 1 |
| Rafiq Islam | 1 | 1 |
| Sadia Chowdhury | 1 | 1 |
| Karim Uddin | 1 | 0 |
+-----------------+------------+--------+Karim's correct order count is 0, not 1. After a LEFT JOIN, count a column from the right table. For sums, wrap them in COALESCE(SUM(…), 0), because the sum of nothing is NULL. This is how you build a per-customer feature table where every customer has a row, including the zeros.
A condition in ON or in WHERE?
For an inner join it makes no difference. For an outer join it changes the answer. Suppose we want every customer with their number of delivered orders. Putting the status test in WHERE:
SELECT c.name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;+-----------------+------------------+
| name | delivered_orders |
+-----------------+------------------+
| Nadia Rahman | 3 |
| Tanvir Ahmed | 2 |
| Farhana Akter | 3 |
| Sadia Chowdhury | 1 |
| Imran Hossain | 1 |
| Mitu Das | 1 |
+-----------------+------------------+Rafiq and Karim vanished. The LEFT JOIN kept Karim with o.status = NULL, and then WHERE o.status = 'delivered' threw him out, because NULL = 'delivered' is not true. Rafiq's only order is cancelled, so he went too. A WHERE test on the right table quietly turns a LEFT JOIN into an inner join. Move the test into ON:
SELECT c.name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;+-----------------+------------------+
| name | delivered_orders |
+-----------------+------------------+
| Nadia Rahman | 3 |
| Tanvir Ahmed | 2 |
| Farhana Akter | 3 |
| Rafiq Islam | 0 |
| Sadia Chowdhury | 1 |
| Imran Hossain | 1 |
| Mitu Das | 1 |
| Karim Uddin | 0 |
+-----------------+------------------+Condition in ON | Condition in WHERE | |
|---|---|---|
| When it applies | while matching: decides which right rows count as partners | after the join: decides which joined rows survive |
| Left rows with no partner | kept, with NULLs | dropped if the test is on a right-table column |
| Rafiq (only a cancelled order) | kept, 0 delivered | dropped |
| Karim (no orders) | kept, 0 delivered | dropped |
Rule of thumb: a test on the right table that should limit which partners count goes in ON; a test on the left table that should limit which rows you report goes in WHERE. The anti-join's WHERE o.order_id IS NULL is the deliberate exception: there you want to filter after the join.
RIGHT JOIN
A RIGHT JOIN B keeps every row of B. It is a LEFT JOIN with the tables swapped, so categories c LEFT JOIN products p gives the same rows. SQLite supports RIGHT and FULL joins only from version 3.39 (2022); MySQL and PostgreSQL have had RIGHT JOIN for decades.
SELECT c.name AS category, p.name AS product
FROM products p
RIGHT JOIN categories c ON p.category_id = c.category_id
WHERE c.category_id IN (3, 5)
ORDER BY c.category_id, p.product_id;+-----------+-------------------------+
| category | product |
+-----------+-------------------------+
| Courses | SQL Masterclass |
| Courses | AI Engineering Bootcamp |
| Furniture | NULL |
+-----------+-------------------------+Most teams write only LEFT JOINs: put the table whose rows you must keep first, and the query reads in that order. Mixing LEFT and RIGHT in one query is hard to follow.
FULL OUTER JOIN
A full join keeps unmatched rows from both sides. Products and categories have orphans on each side: the Gift Card has no category, and Furniture has no products.
SELECT p.name AS product, c.name AS category
FROM products p
FULL OUTER JOIN categories c ON p.category_id = c.category_id
ORDER BY category, product;+-----------------------------+-------------+
| product | category |
+-----------------------------+-------------+
| Gift Card | NULL |
| Laptop Stand | Accessories |
| USB-C Hub | Accessories |
| Hands-On Machine Learning | Books |
| Python Crash Course | Books |
| AI Engineering Bootcamp | Courses |
| SQL Masterclass | Courses |
| 27-inch Monitor | Electronics |
| Mechanical Keyboard | Electronics |
| Noise-Cancelling Headphones | Electronics |
| Wireless Mouse | Electronics |
| NULL | Furniture |
+-----------------------------+-------------+Twelve rows: ten matched pairs, plus one orphan from each side. A full join is the tool for reconciliation: comparing two lists (yesterday's export and today's, the payments table and the bank statement) and seeing what is only on one side. PostgreSQL runs the same query unchanged, but it sorts NULLs last, so there the Gift Card row comes at the end instead of the top.
MySQL has no FULL JOIN
-- MySQL
SELECT p.name AS product, c.name AS category
FROM products p
FULL OUTER JOIN categories c ON p.category_id = c.category_id;Error: ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN categories c ON p.category_id = c.category_id' at line 4The standard workaround: take the LEFT JOIN (all products, matched or not), then add the right-side orphans with a RIGHT JOIN filtered to unmatched rows. UNION ALL stacks the two results on top of each other (covered properly in UNION, INTERSECT and EXCEPT):
-- MySQL
SELECT p.name AS product, c.name AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
UNION ALL
SELECT p.name AS product, c.name AS category
FROM products p
RIGHT JOIN categories c ON p.category_id = c.category_id
WHERE p.product_id IS NULL
ORDER BY category, product;+-----------------------------+-------------+
| product | category |
+-----------------------------+-------------+
| Gift Card | NULL |
| Laptop Stand | Accessories |
| USB-C Hub | Accessories |
| Hands-On Machine Learning | Books |
| Python Crash Course | Books |
| AI Engineering Bootcamp | Courses |
| SQL Masterclass | Courses |
| 27-inch Monitor | Electronics |
| Mechanical Keyboard | Electronics |
| Noise-Cancelling Headphones | Electronics |
| Wireless Mouse | Electronics |
| NULL | Furniture |
+-----------------------------+-------------+The same twelve rows. The WHERE p.product_id IS NULL in the second half matters: without it, the ten matched pairs would appear twice.
CROSS JOIN: every combination
A CROSS JOIN has no condition: it pairs every row of one table with every row of the other (a Cartesian product). With 3 months and 5 categories, that is 15 rows. Let us make a small table of months:
CREATE TABLE months (month TEXT PRIMARY KEY);
INSERT INTO months (month) VALUES ('2026-01'), ('2026-02'), ('2026-03');
SELECT m.month, c.name AS category
FROM months m
CROSS JOIN categories c
ORDER BY m.month, c.category_id
LIMIT 6;
SELECT COUNT(*) AS grid_rows FROM months CROSS JOIN categories;+---------+-------------+
| month | category |
+---------+-------------+
| 2026-01 | Books |
| 2026-01 | Electronics |
| 2026-01 | Courses |
| 2026-01 | Accessories |
| 2026-01 | Furniture |
| 2026-02 | Books |
+---------+-------------+
+-----------+
| grid_rows |
+-----------+
| 15 |
+-----------+Why want this? Because "no sales" is data. Start from the complete grid, then LEFT JOIN the sales onto it, and the empty cells become 0 instead of missing rows. Below, the chain of LEFT JOINs walks category → products → order items → orders (the next page explains joining many tables); the month test sits in ON so that unmatched cells survive, and COUNT(DISTINCT …) makes an order with two products of the same category count once:
SELECT m.month, c.name AS category, COUNT(DISTINCT o.order_id) AS orders
FROM months m
CROSS JOIN categories c
LEFT JOIN products p ON p.category_id = c.category_id
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o ON o.order_id = oi.order_id AND strftime('%Y-%m', o.order_date) = m.month
WHERE c.category_id <= 3
GROUP BY m.month, c.category_id, c.name
ORDER BY m.month, c.category_id;+---------+-------------+--------+
| month | category | orders |
+---------+-------------+--------+
| 2026-01 | Books | 1 |
| 2026-01 | Electronics | 2 |
| 2026-01 | Courses | 0 |
| 2026-02 | Books | 1 |
| 2026-02 | Electronics | 1 |
| 2026-02 | Courses | 1 |
| 2026-03 | Books | 1 |
| 2026-03 | Electronics | 1 |
| 2026-03 | Courses | 1 |
+---------+-------------+--------+Courses in January shows 0 rather than being absent. For a time-series model or a chart this matters: a missing row and a zero are different things, and a model fed only the non-empty months learns that sales never drop to zero. Use CROSS JOIN deliberately and on small tables — two tables of 100,000 rows give ten billion rows.
Self join: a table joined to itself
In employees, manager_id points to another row of the same table. To show each employee next to their manager's name, use the table twice with two aliases — one playing "employee", one playing "manager":
SELECT e.name AS employee, e.department, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;+-----------------+-------------+-----------------+
| employee | department | manager |
+-----------------+-------------+-----------------+
| Ayesha Siddiqua | Management | NULL |
| Hasan Mahmud | Engineering | Ayesha Siddiqua |
| Nusrat Jahan | Engineering | Hasan Mahmud |
| Zahid Hasan | Engineering | Hasan Mahmud |
| Lamia Karim | Data | Ayesha Siddiqua |
| Shuvo Roy | Data | Lamia Karim |
| Priya Sen | Data | Lamia Karim |
+-----------------+-------------+-----------------+Ayesha has no manager (manager_id is NULL), so the LEFT JOIN keeps her with NULL; an inner join would drop the CEO. Turn it around to count each person's direct reports:
SELECT m.name AS manager, COUNT(e.employee_id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY direct_reports DESC, m.name;+-----------------+----------------+
| manager | direct_reports |
+-----------------+----------------+
| Ayesha Siddiqua | 2 |
| Hasan Mahmud | 2 |
| Lamia Karim | 2 |
| Nusrat Jahan | 0 |
| Priya Sen | 0 |
| Shuvo Roy | 0 |
| Zahid Hasan | 0 |
+-----------------+----------------+Self joins appear wherever a table refers to itself: org charts, categories with parent categories, replies to comments, and "pairs" questions such as products bought in the same order (next page). Walking a hierarchy to any depth needs a recursive CTE (see CTEs and Recursive Queries in the advanced tutorial).
In pandas
merge takes the same join types: how="left", "right", "outer" (full) and "cross". Its indicator=True option adds a _merge column saying where each row came from — a quick reconciliation check when you combine data from two sources:
import pandas as pd
from sqlhelp import con
customers = pd.read_sql("SELECT customer_id, name FROM customers", con)
orders = pd.read_sql("SELECT order_id, customer_id FROM orders", con)
both = customers.merge(orders, on="customer_id", how="left", indicator=True)
print(both["_merge"].value_counts())
print(both.loc[both["_merge"] == "left_only", ["customer_id", "name"]])_merge
both 14
left_only 1
right_only 0
Name: count, dtype: int64
customer_id name
14 8 Karim UddinCommon mistakes
- Filtering the right table in WHERE.
LEFT JOIN orders o … WHERE o.status = 'delivered'drops every customer without a delivered order. Fix: put the test inON(ON o.customer_id = c.customer_id AND o.status = 'delivered'). - COUNT(*) after a LEFT JOIN. It counts the NULL-filled row, so a customer with no orders shows 1. Fix:
COUNT(o.order_id), andCOALESCE(SUM(…), 0)for sums. - The wrong table on the left. The table whose rows you must keep goes first.
orders LEFT JOIN customerscan never show Karim, because he has no order to start from:SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id = 8;Fix:+----------+------+ | order_id | name | +----------+------+ +----------+------+FROM customers c LEFT JOIN orders o …. - Testing a nullable column in an anti-join.
WHERE o.status IS NULLworks here only becausestatusisNOT NULL. On a column that can be NULL in a real match, you would also catch matched rows. Fix: test the right table's primary key or join column. - FULL JOIN on MySQL, or RIGHT/FULL on an old SQLite. MySQL gives a syntax error; SQLite before 3.39 says "RIGHT and FULL OUTER JOINs are not currently supported". Fix: swap tables to make a LEFT JOIN, and emulate FULL with the
UNION ALLpattern above.
Try it yourself
- Easy: List every category with its number of products, including categories with none, in
category_idorder. - Medium: For every product, show the total units sold (0 if never sold). Show the four lowest, ties broken by
product_id. - Hard: Find the orders that have no payment, with the customer's name and the order status. Then explain in one sentence why order 5 and order 13 have no payment, and whether that is a data problem.
Answers
-- 1
SELECT c.name AS category, COUNT(p.product_id) AS products
FROM categories c
LEFT JOIN products p ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY c.category_id;-- 2
SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY units_sold, p.product_id
LIMIT 4;-- 3
SELECT o.order_id, c.name, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN payments pay ON pay.order_id = o.order_id
WHERE pay.payment_id IS NULL
ORDER BY o.order_id;Order 5 was cancelled and order 13 is still pending, so neither should have a payment yet; a delivered order with no payment would be the data problem worth investigating.
Summary
LEFT JOINkeeps every row of the left table and fills missing partners with NULL;LEFT JOIN … WHERE right.key IS NULLfinds rows with no match.- Conditions on the right table belong in
ON; inWHEREthey silently turn the LEFT JOIN into an inner join. - After a LEFT JOIN, count a right-table column and wrap sums in
COALESCE(…, 0). RIGHT JOINis a swapped LEFT JOIN;FULL OUTER JOINkeeps orphans from both sides (SQLite 3.39+, PostgreSQL; MySQL needs theUNION ALLemulation).CROSS JOINbuilds complete grids so empty cells become zeros; a self join uses one table twice under two aliases.
Next: Joining Many Tables, and the Duplicate-Row Trap chains three, four and five tables into real reports — and shows how joining two "many" tables at once silently doubles your totals, and how to fix it.