In the joins lesson you combined orders with customers and products using INNER JOIN and LEFT JOIN. Those two cover most everyday queries. But some questions need a different shape of join.
Which customers have never placed an order? Who is each employee's manager, when managers are employees too? Which products are in the warehouse but missing from the website, and which are on the website but not in the warehouse? How do you list every size of every colour of a T-shirt?
This lesson answers each of those with a different join technique: the anti-join, the self join, RIGHT and FULL OUTER JOIN, and CROSS JOIN. You'll also learn a subtle mistake with LEFT JOIN filters that silently turns it back into an inner join.
Finding rows with no match
A LEFT JOIN keeps every row from the left table and fills the right side with NULL when there's no match. Add WHERE right_table.id IS NULL and you keep only the unmatched rows. This pattern is called an anti-join:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- customers with no orders: EmaTest a column that can never be NULL in a real match, such as the primary key. NOT EXISTS gives the same result and is equally good; use whichever reads more clearly to you.
Self joins
A self join joins a table to itself, using two different aliases. It's the natural way to handle rows that point at other rows in the same table, like employees and their managers. Here is a staff table:
| id | name | role | manager_id |
|---|---|---|---|
| 1 | Maria | Director | NULL |
| 2 | Tom | Sales lead | 1 |
| 3 | Aisha | Warehouse lead | 1 |
| 4 | Leo | Sales | 2 |
| 5 | Priya | Sales | 2 |
| 6 | Sam | Packer | 3 |
Treat it as two tables: e for employees and m for managers, and join on m.id = e.manager_id. Use a LEFT JOIN so Maria, who has no manager, still appears.
RIGHT and FULL OUTER JOIN
RIGHT JOINis a LEFT JOIN written the other way round: it keeps every row from the right table. Most people swap the table order and use LEFT JOIN instead, because it reads more naturally.FULL OUTER JOINkeeps all rows from both tables, matching where it can and filling NULLs on whichever side has no partner. It's ideal for comparing two lists.
PostgreSQL, SQL Server and SQLite (version 3.39 and later) support both. MySQL has RIGHT JOIN but no FULL OUTER JOIN; you can imitate it by combining a LEFT JOIN and a RIGHT JOIN with UNION.
CROSS JOIN and joining on several columns
CROSS JOIN pairs every row of one table with every row of another, with no ON condition. Two colours and three sizes give six combinations. It's useful for generating options, calendars or report grids, but use it carefully: 10,000 rows crossed with 10,000 rows is 100 million.
A join condition can include more than one test: ON a.store_id = b.store_id AND a.day = b.day. Any condition that compares the two tables can go in ON.
SQL Joins Syntax
Join patterns
-- anti-join
SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL;
-- self join
SELECT e.name, m.name FROM staff e LEFT JOIN staff m ON m.id = e.manager_id;
-- other join types
SELECT ... FROM a RIGHT JOIN b ON ...;
SELECT ... FROM a FULL OUTER JOIN b ON ...;
SELECT ... FROM a CROSS JOIN b;SQL Joins Summary
- LEFT JOIN plus
WHERE right.id IS NULLfinds rows with no match (an anti-join). - A self join uses two aliases of the same table, for example employees and managers.
- RIGHT JOIN keeps all rows of the right table; FULL OUTER JOIN keeps all rows of both.
- CROSS JOIN creates every combination of rows, so watch the size.
- With LEFT JOIN, filter the right-hand table in ON, not WHERE.
SQL Joins Examples
Each example shows the code, the output it produces and what each part does.
Each employee and their manager
SELECT e.name AS employee,
m.name AS manager
FROM staff e
LEFT JOIN staff m ON m.id = e.manager_id
ORDER BY e.id;employee | manager
---------+--------
Maria | NULL
Tom | Maria
Aisha | Maria
Leo | Tom
Priya | Tom
Sam | AishaLine-by-line explanation
- Lines 3 to 4The same table appears twice with different aliases: e is the employee, m the manager.
- Line 4Match each employee's manager_id to a manager's id. LEFT JOIN keeps Maria, whose manager_id is NULL.
Products that have never been ordered
SELECT p.name
FROM products p
LEFT JOIN orders o ON o.product_id = p.id
WHERE o.id IS NULL;name
----------
Lunch boxLine-by-line explanation
- Line 3Every product is kept, with order columns filled with NULL when it was never ordered.
- Line 4Keep only those with no order. The lunch box is also out of stock, which might explain it.
Comparing two stock lists
The warehouse lists SKUs A1, B2 and C3. The website lists A1, C3 and D4.
SELECT w.sku AS warehouse_sku,
s.sku AS website_sku
FROM warehouse w
FULL OUTER JOIN website s ON s.sku = w.sku
ORDER BY COALESCE(w.sku, s.sku);warehouse_sku | website_sku
--------------+------------
A1 | A1
B2 | NULL
C3 | C3
NULL | D4Line-by-line explanation
- Line 4Keep rows from both sides, matched where possible.
- Line 5COALESCE sorts by whichever SKU is present. The result shows B2 is in stock but not for sale online, and D4 is for sale but not in the warehouse: two problems revealed by one query.
Common SQL Joins Mistakes
Filtering a LEFT JOIN in WHERE
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-07-05'; -- customers without recent orders vanishSELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
AND o.order_date >= '2024-07-05';Self join without aliases
SELECT name, name FROM staff JOIN staff ON id = manager_id; -- ambiguousSELECT e.name, m.name FROM staff e JOIN staff m ON m.id = e.manager_id;An accidental CROSS JOIN
SELECT c.name, o.id FROM customers c, orders o; -- 5 x 6 = 30 rowsSELECT c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id;Listing tables with commas and forgetting the condition pairs every row with every row.
SQL Joins in Real Projects
Joins in practice
Anti-joins power everyday reports: customers who haven't ordered in a year, products never viewed, staff without training records. Self joins model organisation charts, folder trees, comment replies and "people who referred other people". FULL OUTER JOIN is a favourite of data teams reconciling two systems, such as a payment provider's records against the shop's own orders.
SQL Joins FAQ
Does MySQL support FULL OUTER JOIN?
No. MySQL has no FULL OUTER JOIN; combine a LEFT JOIN and a RIGHT JOIN with UNION to get the same result. PostgreSQL and SQL Server support it directly, and SQLite has supported it since version 3.39.
When is a CROSS JOIN useful?
When you genuinely want every combination, such as every product for every day of a month to build a calendar of sales, including days with no sales. It's also used to attach a single-row result, such as an overall average, to every row. On two large tables it produces a huge result, so use it deliberately.
What is an anti-join?
A query that keeps rows that have no match in another table, such as customers who have never ordered. Write it as LEFT JOIN ... WHERE right_table.id IS NULL or with NOT EXISTS. Both are reliable; NOT IN is risky if the subquery can return NULLs.
Should I use a self join or a recursive query for hierarchies?
A self join reaches one level at a time, such as each employee and their direct manager. For a whole chain of unknown depth, such as every manager above an employee or every subcategory in a tree, use a recursive CTE, covered in the CTEs lesson.
Should I use NATURAL JOIN?
It's best avoided. NATURAL JOIN joins on every column the two tables share by name, so adding an unrelated column such as created_at to both tables can silently change the join and its results. Write the join condition explicitly with ON.
SQL Joins Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Complete the anti-join that finds customers who have no orders.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id ;What is a self join?
A table of 4 colours is CROSS JOINed with a table of 5 sizes. How many rows come back?
Which join keeps every row from both tables?
Why does adding WHERE o.status = 'paid' to a LEFT JOIN from customers to orders remove customers with no orders?
SQL Joins Quiz
5 questions to check what you learned.
Which join would you rewrite as a LEFT JOIN by swapping the table order?
- INNER JOIN
- CROSS JOIN
- RIGHT JOIN
- FULL OUTER JOIN
In a self join of staff to staff, why use LEFT JOIN for managers?
- It's faster
- Self joins require LEFT JOIN
- To remove duplicates
- So employees without a manager, such as the director, still appear
Which database lacks FULL OUTER JOIN?
- PostgreSQL
- SQL Server
- SQLite 3.39+
- MySQL
What does
FROM a, bwithout a WHERE condition produce?- An error
- Every combination of rows, like a CROSS JOIN
- An inner join on id
- Only matching rows
Which query finds products never ordered?
- SELECT name FROM products JOIN orders ON orders.product_id = products.id
- SELECT name FROM products WHERE id = NULL
- SELECT p.name FROM products p LEFT JOIN orders o ON o.product_id = p.id WHERE o.id IS NULL
- SELECT name FROM products CROSS JOIN orders