Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Basics & Joins

What is a semi-join?

Mediumsemi-joinexistsin

Interview question

What is a semi-join?

How to answer

The short answer

A semi-join returns rows from the left table when a matching row exists in the right table.

Usually implemented using:

SQL
WHERE EXISTS (...)

Example:

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

This returns customers who have at least one order.

Unlike an INNER JOIN, a semi-join does not return columns from the right table and does not multiply the left row when multiple matches exist.

EXISTS vs IN

Both can implement semi-join logic. EXISTS is often convenient for correlated existence checks. IN is simple for comparing against a set of values.

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