LEFT JOIN, RIGHT JOIN, and FULL JOIN

INNER JOIN gives you only rows that match in both tables. That's most of what you want — but not all. Two of the most useful queries a tester writes are "users who have never placed an order" and "order items whose parent order doesn't exist." Both ask about missing matches, and INNER JOIN can't answer them. The outer joins — LEFT JOIN, RIGHT JOIN, and FULL JOIN — can.

LEFT JOIN — keep everything from the left, even unmatched rows

LEFT JOIN (also written as LEFT OUTER JOIN — same thing) returns every row from the left table, plus the matching rows from the right where they exist. If there's no match on the right, the right-side columns come back as NULL.

SELECT u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.name, o.id;

Result:

| name        | order_id | total  |
|-------------|----------|--------|
| Alice Khan  | 1        | 145.00 |
| Bob Patel   | 2        |  48.50 |
| Bob Patel   | 3        | 320.00 |
| Carol Singh | NULL     | NULL   |
| Dan Müller  | 4        |  63.00 |
| Esha Roy    | 5        |  25.00 |

Notice Carol — she's still in the result, but her order columns are NULL because she hasn't placed an order. With INNER JOIN she'd be missing entirely.

The "find rows with no match" pattern

The single most useful application of LEFT JOIN: filter to just the rows where the right side is NULL, and you've got everything that didn't match.

-- Users who have NEVER placed an order
SELECT u.id, u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Result:

| id | name        | email             |
|----|-------------|-------------------|
| 3  | Carol Singh | carol@example.com |

That pattern — LEFT JOIN ... WHERE right.id IS NULL — is the canonical "anti-join" in SQL. You'll use it dozens of times to find:

• Users who never logged in
• Products that never sold
• Categories with no products
• Comments whose post was deleted (orphans)

RIGHT JOIN — symmetrical to LEFT JOIN

RIGHT JOIN is exactly the mirror of LEFT JOIN: keep everything on the right, with NULLs where the left has no match.

SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

You can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the two tables — and most teams do, because it reads more naturally to put the "main" table on the left:

-- Same result as the RIGHT JOIN above, more conventional
SELECT u.name, o.id AS order_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

In practice you'll meet RIGHT JOIN much less often than LEFT JOIN. SQLite doesn't even support it directly. If you're writing the query yourself, prefer LEFT JOIN for consistency.

FULL OUTER JOIN — keep everything, matched or not

FULL OUTER JOIN returns every row from both tables, with NULLs filling in wherever a side has no match. It's the one to reach for when you need complete visibility — common in data-reconciliation and migration verification.

SELECT u.name, o.id AS order_id, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

A small dialect note that catches people out:

• PostgreSQL, SQL Server, Oracle, SQLite (3.39+): support FULL OUTER JOIN directly.
• MySQL: does not. The workaround is UNION of LEFT and RIGHT JOIN:

-- MySQL FULL JOIN substitute
SELECT u.name, o.id, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.id, o.total FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

All four joins, side by side

Picking the right one is mostly about which side might have missing matches. INNER if both sides definitely match. LEFT if the left side might have rows with no right match. FULL if either side might.

QA use cases for outer joins

Three real scenarios where outer joins do work INNER JOIN can't:

-- 1. Users who have never placed an order — test coverage gap?
-- Are we missing UI flows that should drive these users to convert?
SELECT u.id, u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
 
-- 2. Orphaned order_items — child rows whose parent order was deleted
-- A real data integrity bug if any rows come back
SELECT oi.id, oi.order_id, oi.product_id, oi.quantity
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
 
-- 3. Categories with no products — empty leaves in the catalog
-- Probably a content bug if a category is launched empty
SELECT c.id, c.name
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
WHERE p.id IS NULL;

Each of those queries surfaces a class of bug that isn't visible from the UI alone. Run them as part of a release sanity check and you'll catch problems before customers do.

Why "WHERE right.id IS NULL" works

A small mental model that makes outer joins click. With LEFT JOIN, the database produces a row for every left-side row. If there's a match on the right, the right columns hold the match's values. If there's no match, the right columns are filled with NULL. So WHERE o.id IS NULL keeps only the left-side rows that didn't match — the very rows INNER JOIN would have dropped.

That's the whole trick. Every "find X with no Y" query is the same shape: outer-join Y onto X, then filter to Y's id being NULL.

⚠️ Common Mistakes

• Putting the right-table filter in WHERE. LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed' accidentally turns the LEFT JOIN into an INNER JOIN — because the WHERE filter discards every row where o.status is NULL (the unmatched left rows). If you need to filter the joined side, put the condition in the ON clause: LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'.
• Confusing LEFT and RIGHT. LEFT JOIN keeps the table on the left of the JOIN keyword, i.e., the one in FROM. Read the query top-to-bottom: FROM A LEFT JOIN B → A is left, B is right.
• MySQL's missing FULL OUTER JOIN. A query that runs on PostgreSQL fails on MySQL with no obvious error message. If you're writing portable SQL, stick to INNER + LEFT or use the UNION workaround above.

🎯 Practice Task

25 minutes. Run each query against the e-commerce database.

1. List every user, even those without orders. Show name and any order id (NULL if none). Count the rows — should be 5 (one for Carol, with NULL).
2. Find every user who has never placed an order. (LEFT JOIN + IS NULL.)
3. Find every product that has never appeared in an order. (LEFT JOIN order_items onto products, filter where the order_items side is NULL.)
4. Find every category that has at least one product, then a separate query to find categories with no products. Both queries against the same data — make sure your two result sets don't overlap.
5. Run the orphan-detection query for order_items. In our seed data the result should be empty — but try DELETE'ing one order (we'll cover DELETE in Chapter 4) and re-running. (Don't DELETE if your test database is shared!)
6. Stretch: show every user with the count of orders they've placed (0 for users with none). You'll need a LEFT JOIN, a COUNT(), and GROUP BY — the latter two are the next lesson, so you're previewing the punchline. (COUNT(o.id) not COUNT(*) — the difference matters when there are NULLs.)

That stretch is exactly the bridge to our next lesson. GROUP BY and HAVING turn a JOIN result into per-user, per-product, per-category summaries — the queries you'll use when a PM asks "how many orders did each tester place?".