sql

🔷 SQL: Duplicate Detection and the NULL Trap

Finding duplicates:

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

The NULL comparison trap: - NULL = NULL → returns NULL (not TRUE!) - NULL != 5 → returns NULL (not TRUE!) - Always use IS NULL or IS NOT NULL - COUNT(column) ignores NULLs, COUNT(*) counts all rows - AVG(column) ignores NULLs (divides by non-NULL count)

Practice Questions

Q: Table has 100 rows. Column bonus has 20 NULLs. What does AVG(bonus) divide by?

A: 80 — it ignores the 20 NULL rows. This can be surprising if you expect it to treat NULLs as 0. If you want NULLs as 0: AVG(COALESCE(bonus, 0)).