Joins that multiply rows, semi-joins, and APPLY

The basic joins behave in SQL Server exactly as in standard SQL. What interviews probe is what happens to the number of rows, and the one operator other databases spell differently: APPLY.

Fan-out: every match is a row

A join returns one row per matching pair. Join orders to payments and to shipments in the same query, and an order with two payments and three shipments becomes six rows. Sum the order total over those rows and it is counted six times. Nothing errors; the number is just wrong.

The fix is to aggregate each child table to one row per order before joining, or to use a subquery that returns a single value.

1CREATE TABLE orders (id INT PRIMARY KEY, total INT NOT NULL);2CREATE TABLE shipments (id INT PRIMARY KEY, order_id INT NOT NULL);3INSERT INTO orders VALUES (1, 100), (2, 40);4INSERT INTO shipments VALUES (1, 1), (2, 1), (3, 1), (4, 2);

1SELECT 'joined then summed' AS method, SUM(o.total) AS revenue2FROM orders AS o3JOIN shipments AS s ON s.order_id = o.id4UNION ALL5SELECT 'EXISTS', SUM(o.total)6FROM orders AS o7WHERE EXISTS (SELECT 1 FROM shipments AS s WHERE s.order_id = o.id);

Semi-joins and anti-joins

"Customers who have at least one order" is a question about existence, not about pairs. EXISTS answers it without multiplying rows; an inner join followed by DISTINCT gets the same answer by producing the duplicates and then removing them. NOT EXISTS is the anti-join, and unlike NOT IN it is safe with NULLs. LEFT JOIN ... WHERE child.id IS NULL is the other common spelling of an anti-join.

APPLY: a join to a query that depends on the row

CROSS APPLY runs a table expression once per row of the left side, and that expression may refer to the left row's columns. That is what a normal join cannot do, and it makes "top N per group" direct:

SELECT c.name, o.placed_on, o.total
FROM customers AS c
CROSS APPLY (
  SELECT TOP (2) placed_on, total
  FROM orders
  WHERE orders.customer_id = c.id     -- correlated to the outer row
  ORDER BY placed_on DESC, id DESC
) AS o;

CROSS APPLY behaves like an inner join: a customer for whom the inner query returns nothing disappears. OUTER APPLY behaves like a left join and keeps them with NULLs. PostgreSQL calls the same thing a LATERAL join.

1CREATE TABLE customers (id INT PRIMARY KEY, name NVARCHAR(20) NOT NULL);2CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, placed_on DATE NOT NULL, total INT NOT NULL);3INSERT INTO customers VALUES (1, N'Nino'), (2, N'Luka'), (3, N'Ana');4INSERT INTO orders VALUES5  (10, 1, '2024-03-01', 50), (11, 1, '2024-05-20', 80),6  (12, 3, '2024-04-02', 30), (13, 3, '2024-04-02', 45);

1SELECT 'CROSS' AS apply_kind, c.name, o.id AS order_id, o.total2FROM customers AS c3CROSS APPLY (SELECT TOP (1) id, total FROM orders4             WHERE orders.customer_id = c.id5             ORDER BY placed_on DESC, id DESC) AS o6UNION ALL7SELECT 'OUTER', c.name, o.id, o.total8FROM customers AS c9OUTER APPLY (SELECT TOP (1) id, total FROM orders10             WHERE orders.customer_id = c.id11             ORDER BY placed_on DESC, id DESC) AS o12ORDER BY apply_kind, name;

APPLY is also how you call a table-valued function per row, and CROSS APPLY (VALUES ...) is a neat way to turn columns into rows.

Common mistakes

• Summing a parent's value across a one-to-many join. The first example counts the 100 of an order with three shipments as 300; with a second child table joined in, the copies multiply again.
• CROSS APPLY when customers without rows must stay. Use OUTER APPLY; the second example shows Luka only in the outer version.
• TOP (1) without a unique ORDER BY. Two orders on the same day make "the latest" arbitrary; add the id as a tie-breaker.

What interviewers ask

• Why did my total double after adding a join? The join matched several rows per parent; aggregate the child first or use EXISTS.
• What is the difference between CROSS APPLY and OUTER APPLY? Inner versus outer semantics: whether left rows with no result from the right-hand expression are kept.
• How would you return each customer's three most recent orders? CROSS APPLY with TOP (3) ... ORDER BY, or ROW_NUMBER() partitioned by customer and filtered to .