How should I compare NULL values?

Definition:

SQL NULL represents missing or unknown information, not an ordinary value such as zero or an empty string. Comparisons involving NULL usually produce unknown rather than true or false, and WHERE keeps only true conditions. Special predicates such as IS NULL make the intended check explicit.

Key points:

• Use IS NULL or IS NOT NULL, not = NULL.

• NULL means unknown; ordinary comparisons with NULL are not true.

• Be careful with NOT IN if the subquery can return NULL; NOT EXISTS is often clearer.

• COUNT(*) counts rows, while COUNT(column) skips NULL values in that column.

Interview point:

• Mention three-valued logic and show why = NULL does not find missing values.