What does SELECT DISTINCT country FROM users return when four rows have a NULL country?

## The comparison rule behind duplicate elimination SQL uses three-valued logic in predicates: `NULL = NULL` evaluates to UNKNOWN, and a `WHERE` clause keeps only rows whose predicate is TRUE. If duplicate elimination were defined in terms of `=`, no two NULLs could ever be recognised as equal, and every NULL row would survive. That is not how it is defined. For duplicate removal the standard uses a different notion: two values are *not distinct* when they are both null, or both non-null and equal. Two rows are duplicates when they are not distinct in every column. `DISTINCT` therefore treats NULL as a value that equals itself, purely for the purpose of grouping duplicates together. The predicate `IS NOT DISTINCT FROM` exists to make the same comparison available inside a `WHERE` clause. ## Worked example ```sql -- users.country: 'DE', 'DE', 'FR', 'JP', NULL, NULL, NULL, NULL SELECT DISTINCT country FROM users; -- 'DE' -- 'FR' -- 'JP' -- NULL <- one row, not four and not zero ``` Four rows out of eight inputs. The three distinct non-null values each contribute a row, and the four NULLs contribute exactly one. The key point is that the NULL row is **present**: `DISTINCT` is a deduplication operator, never a filter. ## Why this trips people up Developers correctly learn early that `WHERE country = NULL` matches nothing and that you must write `WHERE country IS NULL`. They then generalise "NULL is never equal to anything" to every part of the language. But the language applies the not-distinct rule in several places where values must be grouped rather than tested: duplicate elimination for `DISTINCT`, grouping keys (all NULL keys form one group), and duplicate removal in set operations such as `UNION`. Predicates use three-valued logic; grouping-style operations use the not-distinct rule. Keeping those two contexts separate is what the question is really testing. ## Multiple columns The rule is per column, and all columns must match: ```sql -- t: ('A', NULL), ('A', NULL), ('A', 'B'), (NULL, NULL) SELECT DISTINCT c1, c2 FROM t; -- ('A', NULL) -- ('A', 'B') -- (NULL, NULL) ``` Three rows. The repeated `('A', NULL)` collapses because both columns are not distinct; `('A','B')` differs in the second column; `(NULL, NULL)` differs in the first. Note that a row of all NULLs is a perfectly ordinary duplicate-elimination candidate — it merges with other all-NULL rows and with nothing else. ## A contrast worth knowing Deduplication in the select list keeps a NULL row, whereas aggregate functions generally ignore NULL inputs, so a count of distinct values and a count of the rows returned by `SELECT DISTINCT` can legitimately differ by one. Recognising that these are two different operators with two different NULL policies avoids a whole family of off-by-one report bugs. ## Practical consequences The behaviour is usually what you want when you are enumerating the values a column actually takes: seeing a NULL row tells you the column has missing data, which is real information. It becomes a trap in two situations. First, when the deduplicated columns act as a business key. If `(email, phone)` is meant to identify a person and phone is frequently NULL, `SELECT DISTINCT email, phone` will merge all the phone-less rows for an email into one — which may or may not be the intent — while any row that does carry a phone splits off separately. Deduplicating on nullable columns quietly changes the grain of the result depending on how much data is missing. Second, when the NULL row flows downstream into a UI dropdown, a join key, or a CSV export where a blank category is meaningless. The fix is explicit, not incidental: ```sql SELECT DISTINCT country FROM users WHERE country IS NOT NULL; -- or, to fold missing data into a label: SELECT DISTINCT COALESCE(country, 'UNKNOWN') AS country FROM users; ``` The second form deduplicates on the coalesced expression, so genuine `'UNKNOWN'` values in the data would merge with the NULLs — a small reminder that the select list, not the stored column, is what gets deduplicated. ## Pitfalls Expecting NULL rows to be dropped; expecting each NULL to survive as its own row; assuming `DISTINCT` and a `=`-based predicate follow the same comparison rules; and deduplicating on nullable business keys without deciding what a missing value means.