What are the common NULL pitfalls in SQL? Cover WHERE col = NULL, COUNT(*) vs COUNT(col), aggregates and <> filters.

SQL uses three-valued logic: a condition can be true, false or unknown. Any comparison with NULL gives NULL, which means unknown. WHERE keeps only the rows where the condition is true.

WHERE col = NULL: this never matches any row. Use IS NULL or IS NOT NULL instead.

COUNT(*) vs COUNT(col): COUNT(*) counts all rows. COUNT(col) counts only the rows where col is not NULL.

Aggregates: SUM, AVG, MIN and MAX ignore NULLs.

• AVG divides by the count of non-null values, not by all rows.
• SUM over zero rows returns NULL, not 0. Wrap it in COALESCE(SUM(x), 0) to get 0. COALESCE returns its first non-null argument.

<> filters: WHERE status <> 'archived' also drops the rows where status is NULL. This surprises many people. IS DISTINCT FROM treats NULL as an ordinary value when it compares, so use status IS DISTINCT FROM 'archived' to keep them.

NOT IN: if the list contains a NULL, NOT IN returns no rows.