LIKE Patterns and the NULL Trap

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.

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

Read the code 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.

Matching a prefix with %

The pattern 'The%' matches any title that starts with the three letters T-h-e, and % stands for any run of characters including none at all. That is wide enough to let Theo in alongside the two intended titles.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('The Iron Giant', 1999, 8.1, 'animation');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Theo', 2027, NULL, 'drama');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title FROM movies WHERE title LIKE 'The%';

Output

The Matrix
The Iron Giant
Theo

Theo is a real match, not a bug in the database. The pattern asked for "T-h-e followed by anything", and o is something. Sloppy patterns quietly returning extra rows is the usual way LIKE goes wrong, and the next example tightens this one up.

Tightening the pattern with a space

Adding a space to the pattern requires the word The to be followed by a word boundary, which drops Theo from the result.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('The Iron Giant', 1999, 8.1, 'animation');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Theo', 2027, NULL, 'drama');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title FROM movies WHERE title LIKE 'The %';

Output

The Matrix
The Iron Giant

The previous pattern 'The%' allowed % to match zero characters, which is what let Theo through. In 'The %' the space is an ordinary literal character that the title must actually contain, so Theo now fails on its fourth character.

Every character in a LIKE pattern that is not % or _ is matched literally, spaces very much included. That makes a single space one of the more useful precision tools available.

The single-character wildcard

The pattern '_at' matches cat and hat, but not at and not splat.

_ stands for exactly one character, no more and no fewer. So the value has to be one character followed by at. at fails because the first character is missing entirely, and splat fails because three characters sit in a slot that allows only one.

Combining them is common: '_at%' would accept cat, hat, and cattle, but still reject splat.

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 output style, 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.

Why = NULL never matches

Two queries run here, and the first one returns nothing at all. That empty result before the divider is the whole point of the example.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('The Iron Giant', 1999, 8.1, 'animation');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Theo', 2027, NULL, 'drama');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, rating FROM movies WHERE rating = NULL;
SELECT '---';
SELECT title FROM movies WHERE rating IS NULL;

Output

---
Theo

Theo genuinely has a NULL rating, yet rating = NULL does not find it. NULL means "no value recorded", so asking whether an unknown equals an unknown cannot honestly return true. SQL answers NULL, which is not true, so the row is not returned.

IS NULL is a different operator that asks specifically about the absence of a value, and it works. There is no way to spell this test with =.

NULL rows vanish from both sides of a comparison

Given a users table where some rows have a NULL email, SELECT * FROM users WHERE email != 'a@x.com'; excludes those NULL rows.

That surprises almost everyone the first time. Comparisons against NULL are never true in either direction, so a NULL email matches neither = 'a@x.com' nor != 'a@x.com'. Run both queries, add up the row counts, and you get fewer rows than the table holds.

To include them, say so explicitly:

SELECT * FROM users WHERE email != 'a@x.com' OR email IS NULL;

This silent disappearance is one of the most common real-world SQL bugs, and it is especially nasty in reports, where a total quietly comes out low and nothing looks broken.

Keeping only the rows that have a value

"Has a rating" means the rating is not NULL, and IS NOT NULL is the operator that expresses it.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('The Iron Giant', 1999, 8.1, 'animation');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Theo', 2027, NULL, 'drama');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, rating FROM movies WHERE rating IS NOT NULL;

Output

The Matrix|8.7
The Iron Giant|8.1
Arrival|7.9
Alien|8.5

Four of the five movies come back, and Theo is filtered out because its rating was never recorded.

Note that != NULL would not work here any more than = NULL did in the previous example. IS NULL and IS NOT NULL are the only two tests that can see a NULL, and they are worth committing to memory as a pair.