0Pricing
SQL Academy · Lesson

Anti-Joins and Semi-Joins (NOT EXISTS)

Find 'rows in A with no match in B' (anti-join) and 'rows in A with at least one match in B' (semi-join) using EXISTS/NOT EXISTS.

Anti-Joins and Semi-Joins (NOT EXISTS) is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Semi-Join: "Has At Least One Match"

Return rows from A that have at least one matching row in B — but only A's columns. SQL implements semi-joins via EXISTS or IN.

EXISTS Semi-Join

Users who have placed at least one order:

SELECT u.* FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

IN Semi-Join

Same result, different style:

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

Why EXISTS Often Wins

EXISTS short-circuits: stops at the first match per outer row. IN may materialise the entire inner set. Modern planners often optimise to the same plan, but EXISTS is the safer bet for huge inner sets.

Anti-Join: "Has No Match"

Users who have NEVER placed an order — three idiomatic forms:

-- NOT EXISTS (preferred):
SELECT u.* FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- LEFT JOIN ... IS NULL:
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- NOT IN (risky with NULLs):
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

Why NOT IN Is Risky

If the inner subquery contains NULL, NOT IN returns NULL — and WHERE drops rows where the predicate is NULL. Result: zero rows. NOT EXISTS doesn't have this problem.

Planner Optimisation

Modern PostgreSQL recognises EXISTS and NOT EXISTS as semi/anti-join patterns and may execute them with hash joins:

EXPLAIN ANALYZE
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- → Hash Anti Join

Multi-Column Anti-Joins

Use composite keys:

SELECT * FROM order_items oi
WHERE NOT EXISTS (
  SELECT 1 FROM shipments s
  WHERE s.order_id = oi.order_id
    AND s.line_no  = oi.line_no
);

Semi-Join with EXISTS + Additional Predicate

Add filters to the inner subquery:

SELECT u.* FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.status = 'paid'
    AND o.created_at >= NOW() - INTERVAL '30 days'
);

Index the Correlation Column

For both EXISTS and NOT EXISTS, the inner subquery filters on the outer row's key. Without an index on that key, you get a scan per outer row:

CREATE INDEX orders_user_id_idx ON orders(user_id);

When to Use LEFT JOIN ... IS NULL

For dashboards that want the unmatched-side's columns too, the LEFT JOIN style fits naturally. For pure anti-join semantics, NOT EXISTS is clearer.

Beyond SQL: Bloom Filters

For huge anti-joins, a bloom-filter pre-filter can help. PostgreSQL supports bloom indexes via the bloom extension.

Recap

Semi-join = "has match"; anti-join = "no match".

  • EXISTS for semi-join
  • NOT EXISTS for anti-join (NULL-safe)
  • Index the correlation column
  • Avoid NOT IN unless inner cannot have NULL

Quick Check

You want users who have NEVER placed an order. Which is the safest and most idiomatic SQL?

Frequently asked questions

Is the “Anti-Joins and Semi-Joins (NOT EXISTS)” lesson free?

Yes — the full text of “Anti-Joins and Semi-Joins (NOT EXISTS)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Anti-Joins and Semi-Joins (NOT EXISTS)”?

Find 'rows in A with no match in B' (anti-join) and 'rows in A with at least one match in B' (semi-join) using EXISTS/NOT EXISTS. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Anti-Joins and Semi-Joins (NOT EXISTS)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Cross Joins and Cartesian Products
  2. Lateral Joins (LATERAL JOIN)
  3. Anti-Joins and Semi-Joins (NOT EXISTS)
  4. Multi-Table Join Performance Tuning
← Back to SQL Academy