In that spirit, let me walk through a bite-sized SQL puzzle that helps reinforce some key concepts. Let us see Retrieve the Unique Evens Under 10 puzzle.
Count Distinct in Access: Use a Subquery in FROM
Count distinct in Access with a subquery in the FROM clause, because Access SQL does not accept COUNT(DISTINCT). Includes NULL and multi-column cases.
EXCEPT Operator in SQL Server: Distinct Rows and Differences
The EXCEPT operator returns the rows of one query that are missing from another, with duplicates removed. This post shows the difference between two small order tables, the old WHERE 1 = 0 trick for distinct rows, and how NULLs behave.
Join Duplicates: Stop Hiding Extra Rows With SELECT DISTINCT
Diagnose join duplicates before using DISTINCT, verify order and payment totals, and choose aggregation or EXISTS for the required result.
Finding Rows That Differ Between Two Tables With EXCEPT
Use EXCEPT to find differing rows in both directions, compare selected columns, handle NULLs, and distinguish changed keys from additions.
UNION vs UNION ALL in SQL Server: What Each One Does
UNION vs UNION ALL comes down to duplicates: UNION removes them and UNION ALL keeps every row. This post tests both on two small store tables. It also covers sorting, a total row at the bottom, mixed operators and data type rules.
Count Distinct Pairs: Keep the Columns Together
Count distinct pairs by keeping their columns together. A small T-SQL example shows why concatenated labels can collide. Choose whether incomplete pairs belong in the count.