Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Basics & Joins

What is an anti-join?

Mediumanti-joinnot existsleft join

Interview question

What is an anti-join?

How to answer

The short answer

An anti-join finds rows from one table that do not have a match in another table.

Example: customers who never placed an order.

Method 1: NOT EXISTS

SQL
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

Method 2: LEFT JOIN

SQL
SELECT c.*
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

Interview shortcut

> Anti-join = give me rows that don't have a match.

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill