Advanced JOIN Patterns

Reviewed & published by Brayan K

By the end of this lesson you'll reach beyond INNER and LEFT JOIN to answer the questions they can't: "who has an order?", "who has no order?", "what's each customer's biggest order?". You'll master semi-joins, anti-joins, self-joins, CROSS JOIN, and LATERAL — and the NULL trap that silently breaks NOT IN.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Semi-joins: keep rows that HAVE a match (EXISTS / IN)
• Anti-joins: keep rows with NO match (NOT EXISTS / NOT IN)
• Why NOT IN + a NULL silently returns zero rows
• Self-joins: compare a table against itself
• CROSS JOIN and the Cartesian explosion to avoid
• LATERAL / CROSS APPLY: a subquery per outer row

Our Sample Tables: customers & orders

Every query in this lesson runs against these two small tables. They share one link: orders.customer_id points back to customers.id. Notice Dan has no row in orders — that "missing" customer is the star of the anti-join section.

So Ava (1) has two orders, Ben (2), Cara (3) and Eve (5) have one each, and Dan (4) has none.

1. Semi-Joins — "Does a Match Exist?"

A semi-join keeps rows from the left table that have at least one match on the right — but it never adds the right table's columns and never duplicates a row. You're asking "is this customer on the orders list?", not "show me every order".

A semi-join is checking names off a guest list against the RSVP pile. You only care whether someone RSVP'd — not how many cards they sent. The guest appears once, ticked or not.

-- The two tables this lesson uses. Run this block first;
-- every later block queries them.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES
    (1, 'Ava'), (2, 'Ben'), (3, 'Cara'), (4, 'Dan'), (5, 'Eve');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES
    (101, 1, 50),   -- Ava has TWO orders, which is what makes the
    (102, 1, 30),   -- self-join below return exactly one pair
    (103, 2, 80),
    (104, 3, 20),
    (105, 5, 60);
-- Dan (id 4) has none, which is what the anti-join finds.

-- SEMI-JOIN: keep rows in A that HAVE at least one match in B.
-- "Which customers have placed at least one order?"

-- EXISTS runs the inner query for each customer and stops
-- at the FIRST matching order — it never multiplies rows.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1                       -- the 1 is a placeholder; EXISTS
    FROM orders o                  -- only cares IF a row comes back,
    WHERE o.customer_id = c.id     -- not WHAT it contains
);

-- Every customer EXCEPT Dan (id 4), who has no orders.
-- Each customer appears AT MOST once, even Ava (who has 2 orders).

-- ✅ Expected result:
-- id | name
-- 1 | Ava
-- 2 | Ben
-- 3 | Cara
-- 5 | Eve

The same answer reads a little shorter with IN, which checks whether each customer's id appears among the order rows:

-- Uses the customers and orders tables from the first example.
-- Same answer with IN: keep customers whose id appears
-- in the list of customer_ids that have orders.
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- IN builds the whole list first; EXISTS can short-circuit.
-- On big tables EXISTS is usually the faster of the two.

-- ✅ Expected result:
-- id | name
-- 1 | Ava
-- 2 | Ben
-- 3 | Cara
-- 5 | Eve

Your Turn: complete the semi-join

Fill in the one blank with the keyword that means "a match exists". The expected result is in the comments so you can check yourself.

-- 🎯 YOUR TURN — complete the semi-join, then press "Try it Yourself".
-- Uses the customers and orders tables from the first example.
-- Goal: list every customer who HAS placed at least one order.

SELECT c.id, c.name
FROM customers c
WHERE ___ (                        -- 👉 replace ___ with EXISTS
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- ✅ Expected result:
-- id | name
-- 1 | Ava
-- 2 | Ben
-- 3 | Cara
-- 5 | Eve

2. Anti-Joins — "Find What's Missing"

An anti-join is the mirror image: keep the left-table rows that have no match on the right. This is how you answer "which customers have never ordered?", "which products never sold?", "which users never logged in?". The cleanest tool is WHERE NOT EXISTS (...).

Back to the guest list: an anti-join finds everyone who didn't RSVP — the names with an empty spot in the reply pile. In our data that's exactly one person: Dan.

-- Uses the customers and orders tables from the first example.
-- ANTI-JOIN: keep rows in A that have NO match in B.
-- "Which customers have NEVER placed an order?"

-- NOT EXISTS is the safe, fast, NULL-proof way to do this.
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

-- Just Dan (id 4) — the only customer with zero orders.

-- ✅ Expected result:
-- id | name
-- 4 | Dan

You can write it with NOT IN too — but only if you guard against NULLs in the subquery:

-- Uses the customers and orders tables from the first example.
-- The SAME idea with NOT IN — but watch the NULL trap below.
SELECT id, name
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL   -- ⚠️ CRITICAL guard, see why next
);

-- Dan (id 4). Identical to NOT EXISTS — WHEN the guard is present.

-- ✅ Expected result:
-- id | name
-- 4 | Dan

-- Uses the customers and orders tables from the first example.
-- ⚠️ THE NULL TRAP — why NOT EXISTS is the safer default.
-- Give ONE order row a NULL customer_id (an unassigned order):
INSERT INTO orders VALUES (106, NULL, 15);

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);  -- no NULL guard!

-- Nothing comes back. Not Dan, not anyone: zero rows.
--
-- Why? "id NOT IN (1, 2, 3, NULL)" becomes
-- "id <> 1 AND id <> 2 AND id <> 3 AND id <> NULL".
-- "id <> NULL" is UNKNOWN (never TRUE), so the whole row is rejected.
-- One stray NULL silently wipes out every result.
-- NOT EXISTS does not have this problem — prefer it.

-- Put the table back to five orders so the later examples still
-- describe the data this lesson started with:
DELETE FROM orders WHERE id = 106;

-- ✅ Expected result:
-- id | name

Your Turn: complete the anti-join

Fill in the blank with the two words that mean "no match exists", so the query returns customers with no orders.

-- 🎯 YOUR TURN — complete the anti-join, then press "Try it Yourself".
-- Uses the customers and orders tables from the first example.
-- Goal: find every customer who has NO orders at all.

SELECT c.id, c.name
FROM customers c
WHERE ___ EXISTS (                 -- 👉 replace ___ with NOT
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- ✅ Expected result:
-- id | name
-- 4 | Dan

3. Self-Joins — A Table Meets Itself

A self-join joins a table to a second copy of itself using two aliases. It's how you compare rows within one table — find pairs, build employee→manager hierarchies, or compare consecutive rows. Here we'll find customers who placed more than one order by pairing each order with another order from the same customer.

-- Uses the orders table from the first example.
-- SELF-JOIN: join a table to ITSELF using two aliases.
-- "Which customers placed more than one order, and which pair?"

-- We give 'orders' two names (o1, o2) so SQL treats it as two tables.
SELECT o1.customer_id,
       o1.id AS first_order,
       o2.id AS second_order
FROM orders o1
JOIN orders o2
    ON o1.customer_id = o2.customer_id   -- same customer...
    AND o1.id < o2.id;                   -- ...but two DIFFERENT orders

-- The "o1.id < o2.id" does two jobs:
--   • removes a row paired with itself (101 with 101)
--   • removes mirror duplicates — keeps (101,102), drops (102,101)
-- Only Ava (customer_id 1) has two orders, so exactly one row comes back.

-- ✅ Expected result:
-- customer_id | first_order | second_order
-- 1 | 101 | 102

4. CROSS JOIN — Cartesian Products

A CROSS JOIN pairs every row of one table with every row of another — no ON clause. The output size is the two row counts multiplied. It's invaluable for generating every combination (sizes × colours, dates × products), but a missing join condition silently becomes one and explodes your result.

5 customers × 5 orders = 25 rows. Harmless here — but 1,000 × 1,000 = 1,000,000 rows. Always know why you're cross-joining.

-- Uses the customers and orders tables from the first example.
-- CROSS JOIN: pair EVERY row of A with EVERY row of B.
-- Also called a Cartesian product. There is no ON clause.
-- 5 customers × 5 orders = 25 rows, so count them rather than
-- printing all 25:
SELECT COUNT(*) AS pair_count
FROM customers c
CROSS JOIN orders o;

-- Drop the COUNT and SELECT c.name, o.id to see every pairing.
-- ⚠️ This grows fast: 1,000 × 1,000 = 1,000,000 rows.
-- Use CROSS JOIN deliberately — to build combinations or fill gaps,
-- never by accident (a missing JOIN condition becomes a CROSS JOIN).

-- ✅ Expected result:
-- pair_count
-- 25

5. LATERAL / CROSS APPLY — A Subquery Per Row

A normal subquery in the FROM clause can't see the outer row. LATERAL removes that wall: it runs the subquery once for each outer row and lets it reference that row's columns — like a for-each loop. It's the cleanest way to get "the top N rows per group", here each customer's single biggest order.

-- engine: postgres — SQLite has no LATERAL, so this one block needs a
-- PostgreSQL server. Pressing Run on this site uses SQLite in your
-- browser and will answer with a syntax error near "SELECT"; that is
-- the engine talking, not a broken snippet.
-- Uses the customers and orders tables from the first example.
-- LATERAL: run a subquery FOR EACH row of the outer table,
-- and let that subquery SEE the outer row's columns.
-- "Get each customer's single biggest order."

SELECT c.name, top_order.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.amount
    FROM orders o
    WHERE o.customer_id = c.id     -- ← references the OUTER c.id (that's LATERAL)
    ORDER BY o.amount DESC
    LIMIT 1
) AS top_order;

-- CROSS JOIN LATERAL drops customers whose subquery is empty,
-- so Dan (no orders) does NOT appear → 4 rows.
-- Want Dan too? Use LEFT JOIN LATERAL ( ... ) ON true (amount = NULL).

-- Engine note:
--   • PostgreSQL / MySQL 8+ : CROSS JOIN LATERAL ( ... )
--   • SQL Server / Oracle   : CROSS APPLY ( ... )   -- (OUTER APPLY keeps Dan)
-- Same idea, different keyword.

-- ✅ Expected result:
-- name | amount
-- Ava | 50
-- Ben | 80
-- Cara | 20
-- Eve | 60

Common Errors (and the fix)

• NOT IN returns nothing: the subquery contained a NULL, which makes every comparison UNKNOWN and rejects all rows. Switch to NOT EXISTS, or add WHERE col IS NOT NULL inside the subquery.
• Result has way too many rows: you wrote a CROSS JOIN, or forgot the ON condition on a regular join — which becomes a Cartesian product. Add the join condition that links the two tables.
• Customers appear multiple times: you used JOIN orders when you only needed to know a match exists. A one-to-many join repeats the left row per match — use WHERE EXISTS (...) instead of joining and de-duplicating with DISTINCT.
• "missing FROM-clause entry for table o" / unknown alias: in a self-join you must give each copy its own alias (orders o1, orders o2) and qualify every column (o1.id), or SQL can't tell the copies apart.
• LATERAL subquery "cannot reference c.id": a plain FROM (subquery) can't see the outer row. Add the LATERAL keyword (or use CROSS APPLY on SQL Server) to allow the reference.

Frequently Asked Questions

Q: Why does SELECT 1 appear inside EXISTS?

EXISTS only checks whether a row comes back, not what's in it, so the select list is irrelevant. 1 is a conventional placeholder; SELECT * would behave identically.

Q: Should I always prefer EXISTS over IN?

For correctness with NOT IN, yes — NOT EXISTS avoids the NULL trap. For positive checks, modern optimisers often run IN and EXISTS the same way; EXISTS tends to win on large or correlated subqueries.

Q: When would I use a self-join instead of a window function?

Self-joins shine for pairing rows or walking a hierarchy. For "rank within a group" or "compare to the previous row", window functions (the next lesson) are usually clearer and faster.

Q: Does my database support LATERAL?

PostgreSQL and MySQL 8+ use LATERAL; SQL Server and Oracle use CROSS APPLY / OUTER APPLY. SQLite has neither — there you'd fall back to a correlated subquery or a window function.

Mini-Challenge: High-Value Customers

Support is faded now — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

-- 🎯 MINI-CHALLENGE — a semi-join with a twist.
-- Using ONLY this lesson's ideas (EXISTS and a condition in the subquery):
--   List every customer who has placed an order worth MORE THAN 40.
--   Each customer should appear at most once.
--
-- ✅ Expected: 3 rows — Ava (order 50), Ben (order 80), Eve (order 60).
--    Cara is excluded (her only order is 20). Dan is excluded (no orders).

-- your query here

🎉 Lesson Complete

• ✅ Semi-join (EXISTS / IN) keeps rows that have a match, without duplicating them
• ✅ Anti-join (NOT EXISTS / NOT IN) keeps rows with no match — find what's missing
• ✅ NOT IN + a single NULL returns zero rows; reach for NOT EXISTS instead
• ✅ Self-joins compare a table to itself; o1.id < o2.id kills mirror duplicates
• ✅ CROSS JOIN multiplies rows — powerful for combinations, dangerous by accident
• ✅ LATERAL / CROSS APPLY runs a subquery per outer row for clean top-N queries
• ✅ Next: Window Functions — rank, total, and compare rows without collapsing them

Practice quiz

Continue this course

• Previous: Query Optimization Deep Dive: Execution Plans, Cost Estimation & Hints
• Next: Window Functions Mastery: PARTITION, ORDER, Frames, Ranking Functions — ROW_NUMBER, RANK, LAG, LEAD, and running totals with window functions
• Quick reference: SQL cheat sheet › More Joins
• From the blog: SQL Joins Explained With Examples

Related lessons

• INNER and LEFT JOINs