NULL, LIKE and wildcards

Missing values and text patterns need their own rules. NULL is not zero, an empty string, or the word “NULL”.

Test missing values

Use IS NULL and IS NOT NULL. Ordinary comparisons such as email = NULL do not evaluate to true. WHERE retains true conditions, so that equality test will not find missing emails.

Unknown conditions

A comparison involving NULL usually produces unknown. For example, NOT (email = 'x') does not include a missing email. Add an explicit IS NULL rule when that is part of the question.

LIKE patterns

% matches zero or more characters and _ matches exactly one character. LIKE 'A%' searches for an A prefix; LIKE '%a%' searches anywhere in the text. Case behavior varies by database.

Literal wildcards

To search for an actual % or _, escape it using an ESCAPE clause supported by your database. Broad leading-wildcard searches can make ordinary indexes less useful.