What is a semi-join?

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

Usually implemented using:

WHERE EXISTS (...)

Example:

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.