The puzzle looks like a loop until you name the rows that belong together. Set-based thinking turns three familiar SQL problems into short, testable queries.
Begin Set-Based Thinking With the Output Grain
Before writing syntax, say what one answer row represents. One row per duplicated email, one row per customer without an order, or one row per store’s latest sale are three different grains. The grain tells you whether to group, exclude, or rank. Most puzzle confusion starts before the SELECT.
I ask which rows must be present in the final result and which facts are merely used to decide membership. That question stops accidental joins from multiplying rows. It also turns a long procedural description into a relation you can test with a few sample cases.
Do not memorize a trick for every puzzle. Learn to identify sets and boundaries. A query is easier to change when the business requirement changes from latest sale to latest two sales. A clever loop has a harder time admitting that it solved the wrong grain.
Puzzle One: Find Duplicate Emails
Suppose dbo.Contact has ContactId and EmailAddress. The request is one row for each email that occurs more than once. GROUP BY forms the groups, and HAVING filters groups by count. A WHERE clause cannot test COUNT before aggregation. That is the difference between filtering rows and filtering groups.
Decide what NULL means. SQL Server groups NULL values together, so several missing emails can appear as one duplicate group. If missing email is not a duplicate identity, filter NULL before grouping. Normalize case and surrounding spaces only if the business rule treats those variants as equal.
I inspect the underlying ContactIds for any group before taking action. A duplicate candidate does not justify deleting records. The query identifies a set for review. That is enough for the puzzle and a safer start for the real cleanup.
SELECT UPPER(TRIM(EmailAddress)) AS NormalizedEmail,
COUNT_BIG(*) AS ContactCount
FROM dbo.Contact
WHERE NULLIF(TRIM(EmailAddress), N'') IS NOT NULL
GROUP BY UPPER(TRIM(EmailAddress))
HAVING COUNT_BIG(*) > 1;Puzzle Two: Find Customers Without Orders
The next request is one row per customer with no order. Think of it as an anti-set: customers minus customers that have a matching order. NOT EXISTS expresses that directly. It avoids the NULL complications of NOT IN when a subquery can return a NULL.
Keep the correlation on the key, not on a display name. Two customers can share a name. An index on Order.CustomerId helps SQL Server test existence without reading every order row. The query needs no ORDER BY unless the presentation requires one.
I test a customer with several orders, one with none, and one with a canceled order. If canceled orders do not count, put that condition inside the NOT EXISTS query. The location of the predicate decides who appears in the answer.
SELECT c.CustomerId, c.CustomerName
FROM dbo.Customer AS c
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.[Order] AS o
WHERE o.CustomerId = c.CustomerId
);
Puzzle Three: Keep the Latest Row per Store
A store can have many sales. The request is one latest sale per store. ROW_NUMBER partitions rows by StoreId and orders each partition by SaleDate descending. Add a unique tie breaker such as SaleId, or two sales at the same time can produce an arbitrary winner.
The window result needs an outer query because the row number is assigned after the FROM and WHERE input is formed. A CTE names that ranked set and lets the outer SELECT filter rn = 1. It does not automatically cache results. It makes the logic readable.
I ask whether latest means the latest date or all rows on the latest date. ROW_NUMBER returns one row. RANK or a join to MAX date can return ties. The business answer determines the window function.
WITH ranked AS
(
SELECT StoreId, SaleId, SaleDate, Amount,
ROW_NUMBER() OVER
(PARTITION BY StoreId ORDER BY SaleDate DESC, SaleId DESC) AS rn
FROM dbo.Sale
)
SELECT StoreId, SaleId, SaleDate, Amount
FROM ranked
WHERE rn = 1;Test Counterexamples, Not Just Examples
Each puzzle has a tempting wrong answer. A join can duplicate customers. NOT IN can behave unexpectedly with NULL. A latest-row query without a tie breaker can change winners. Write tiny test sets that include these counterexamples before trusting the final statement.
I keep the expected output beside each test case. That matters more than an attractive execution plan at first. When the result is correct, inspect performance under representative data. Correctness and speed are separate gates, and the first one should not be skipped.
Try changing one requirement: case-sensitive email, only paid orders, or latest two sales. Solutions built on set-based thinking adapt with a collation rule, predicate, or rn filter. That is a useful measure of understanding. A memorized query tends to break when one word changes.
Read the Plan After Set-Based Thinking Gets the Logic Right
The optimizer can choose a hash aggregate for duplicate groups, an anti semi join for NOT EXISTS, and sorting or ordered index access for ROW_NUMBER. Those are implementation choices. Inspect actual plans and reads on your data before creating an index or rewriting a query.
For the latest sale puzzle, an index starting with StoreId and SaleDate can help. For the anti-join, an index on the order’s customer key can help. The right index depends on table size, selectivity, and existing workload. Do not build three indexes just because a blog example names three operators.
I compare one change at a time. The query’s relational shape should remain easy to read. SQL Server can optimize clear set operations well, but it cannot fix a requirement that was never made precise.
Carry Set-Based Thinking Into Daily SQL
When a new puzzle arrives, state the answer grain, list the input sets, name the membership rule, and test a counterexample. Grouping, anti-joins, and windows cover a large share of everyday problems. The syntax becomes easier once the sets are clear.
A quick sketch on paper can save a page of SQL. I use one box for each input relation and arrows for keys. Then I mark whether the output keeps rows, removes rows, or ranks them. The sketch is not fancy. It is useful.
Set-based thinking does not mean every query must fit on one line. It means the query describes relationships among rows rather than instructions for visiting them one by one. That description is easier to verify and usually easier to tune.
Which rule in the puzzle depends on order, and which rule depends only on membership? Write those assumptions beside the sample rows. A compact answer that relies on accidental ordering will fail when the optimizer picks another plan. I try a second data set with ties and missing values before accepting an elegant-looking solution.
Related reading on this blog: SQL Puzzle: Retrieve the Unique Evens Under 10 and SQL Puzzle: Solution to Strange Results: IN and IS NOT NULL.
A SQL puzzle is not a syntax trick, it is a question about which rows belong in a set.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
