In a ROW_NUMBER dedup partitioned by email, what happens to rows whose email is NULL?

## Two different NULL rules, and which one applies SQL uses NULL inconsistently on purpose. In a **predicate**, `NULL = NULL` evaluates to unknown, so a join or a `WHERE` never matches two nulls. In **grouping** contexts — `GROUP BY`, `DISTINCT`, `PARTITION BY` — nulls are placed together: they are not equal, but they are *not distinct*, and grouping works on not-distinct. Window partitioning follows the grouping rule. That single fact decides what a deduplication query does with missing keys. `PARTITION BY email` builds one partition per distinct email value **plus one partition holding every row where email is NULL**, however many that is and however unrelated those customers are. ## What that does to the numbering ```sql SELECT customer_id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id) AS rn FROM customers; ``` If 400 rows have no email, they form one partition and receive `rn` values 1 through 400. The function is behaving exactly as specified — but the query has silently asserted that all 400 are the same customer. In a read-only query this shows up as a suspiciously small result: you expected "one row per customer" and got one row standing in for hundreds. In a cleanup it is far worse: `DELETE ... WHERE rn > 1` removes 399 real records, and the survivor is whichever one the ordering happened to favour. ## Fixing it The right fix depends on what a missing key means in your data. **Exclude null keys from the destructive step.** Most common and usually correct: an unknown email is not evidence of duplication, so those rows are simply not candidates. ```sql WITH ranked AS ( SELECT customer_id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC) AS rn FROM customers ) DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM ranked WHERE rn > 1 AND email IS NOT NULL); ``` Filtering `WHERE email IS NOT NULL` inside the CTE works equally well and is cheaper, since those rows never get numbered at all. **Pick a different key for those rows.** If missing emails are common, the real duplicate key may be something else — `(phone, birth_date)` or an external system id. Deduplicate the null-email population separately under that key rather than lumping it in. **Do not reach for COALESCE.** `PARTITION BY COALESCE(email, '')` is sometimes offered as a fix; it changes nothing, because it maps every unknown to the same empty string and re-creates exactly the single mega-partition. It is only useful when the empty string genuinely is a real key value you want merged with NULL. ## The wider habit The same trap appears whenever the deduplication key is composite and any of its columns is nullable: `PARTITION BY email, phone` groups all rows where both are missing, and groups rows sharing an email whose phones are both missing. Before writing the delete, run a quick census of the key: ```sql SELECT COUNT(*) AS total, COUNT(email) AS with_email, COUNT(*) - COUNT(email) AS null_email FROM customers; ``` If the null count is anything but zero, decide explicitly what those rows should do rather than letting the partitioning rule decide for you. The senior instinct here is not memorising the NULL rule — it is refusing to run a destructive statement whose behaviour on missing data you have not checked. ## Interview signals Strong candidates state the grouping rule confidently ("partitioning uses not-distinct, so nulls group together, unlike a join predicate"), predict the concrete blast radius, and name the `IS NOT NULL` guard on the delete. Weak candidates assert that NULL never equals NULL so each null row gets its own partition — a plausible-sounding answer that leads directly to mass deletion in production.