LIKE Patterns and the NULL Trap

lesson 2-3 · ~13 min · 5/29

Fuzzy matching with LIKE

Every search box needs something looser than =. A user types matrix and expects The Matrix, but title = 'matrix' matches nothing: = needs an exact match. LIKE matches a pattern with two wildcards:

% matches any run of characters, including nothing at all.
_ matches exactly one character.

Pattern	Matches	Does not match
`'The%'`	The Matrix, Theo	Alien
`'%ien%'`	Alien	Arrival
`'M_chi'`	Mochi	Mochhi

In SQLite, LIKE ignores letter case for plain ASCII letters: 'the%' also matches The Matrix.

Run the block below. The pattern 'The%' means "starts with The", and notice it catches Theo as well, because % is happy to match zero extra characters right after The.

Code exercise · sql

Run it. 'The%' matches anything starting with the three letters T-h-e, so Theo sneaks in alongside The Matrix and The Iron Giant.

Code exercise · sql

Your turn. Fix the pattern so only titles starting with the word "The" (followed by a space) match. Theo should disappear.

Quiz

Which values does the pattern `'_at'` match?

NULL: the value that is not there

Look at the movie Theo in the data above: its rating is NULL. NULL means "no value here", not zero and not an empty string. The film has no rating yet.

NULL breaks the rules you just learned:

rating = NULL is never true, not even for NULL rows. NULL is not equal to anything, including NULL, because you cannot compare two unknowns.
To find NULLs you must write rating IS NULL, and the opposite is rating IS NOT NULL.
In this sandbox's output, a NULL prints as an empty spot: Theo| with nothing after the bar.

The next block proves it. The first SELECT uses = NULL and returns zero rows. Then a divider prints, then IS NULL finds Theo.

Code exercise · sql

Run it. Nothing prints before the --- line because = NULL never matches. IS NULL is the correct test.

Quiz

A users table has an email column where some rows are NULL. What does `SELECT * FROM users WHERE email != 'a@x.com';` do with the NULL rows?

Code exercise · sql

Your turn. Select the title and rating of every movie that HAS a rating. Theo should not appear.