Filtering deep (IN, LIKE, NULL)

Chapter 3 12 code blocks 2646 words

Chapter 2 covered the everyday operators. This chapter covers the ones that make queries readable — and the NULL behaviour that makes queries silently wrong. If a student only ever remembers one chapter of SQL trivia, make it this one: the NOT IN trap below has broken production reports at real companies.

IN and NOT IN

IN is shorthand for a chain of ORs. These two are identical:

-- long form
SELECT first_name, city FROM students
WHERE city = 'Tashkent' OR city = 'Samarkand' OR city = 'Bukhara';

-- same thing, readable
SELECT first_name, city FROM students
WHERE city IN ('Tashkent', 'Samarkand', 'Bukhara');

Result 1

… first 15 of 160 rows

160 rows

Result 2

… first 15 of 160 rows

160 rows

2 ms

first_namecity

AzizaTashkent BekzodSamarkand DilnozaBukhara JasurTashkent OtabekTashkent ZarinaSamarkand

The list can also be a subquery — this is where IN becomes genuinely powerful, and it is previewed here because Chapter 6 builds on it:

-- "Which students are enrolled in Machine Learning (course 5)?"
SELECT first_name, last_name FROM students
WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_id = 5);
-- Aziza (1), Dilnoza (3), Otabek (6), Zarina (9)

… first 15 of 130 rows

130 rows

0 ms

BETWEEN BETWEEN a AND b means >= a AND <= b. Both ends are included.

SELECT enrollment_id, grade FROM enrollments
WHERE grade BETWEEN 80 AND 100
ORDER BY grade DESC;
-- 8 rows: 100, 95, 92, 91, 89, 88, 83, 81

… first 15 of 494 rows

494 rows

1 ms

:::gotcha ⚠️ BETWEEN with dates and times BETWEEN '2026-02-01' AND '2026-02-28' is fine for a pure DATE column. But if the column is a TIMESTAMP, '2026-02-28' means 2026-02-28 00:00:00 — so everything that happened during the 28th is excluded. This quietly loses a day of data in monthly reports. The safe pattern is a half-open range:

WHERE enrolled_at >= '2026-02-01' AND enrolled_at < '2026-03-01'; -- ← always correct, dates or timestamps Teach the half-open range as the default habit; it never has an edge case. :::

LIKE and wildcards

LIKE matches text against a pattern. There are exactly two wildcards:

-- starts with A
SELECT first_name FROM students WHERE first_name LIKE 'A%';        -- Aziza

-- ends with 'ova'  (a common female surname ending)
SELECT last_name FROM students WHERE last_name LIKE '%ova';
-- Karimova, Yusupova, Sobirova, Ismoilova, Qodirova — 5 rows

-- contains 'Learning' anywhere
SELECT title FROM courses WHERE title LIKE '%Learning%';
-- Machine Learning, Deep Learning

-- exactly 5 characters long
SELECT first_name FROM students WHERE first_name LIKE '_____';
-- Aziza, Jasur, Timur

-- does NOT contain 'Data'
SELECT title FROM courses WHERE title NOT LIKE '%Data%';

Result 1

… first 15 of 42 rows

42 rows

Result 2

… first 15 of 126 rows

126 rows

Result 3

2 rows

Result 4

… first 15 of 43 rows

43 rows

Result 5

14 rows

1 ms

:::gotcha ⚠️ Case sensitivity differs by database In SQLite, LIKE is case-insensitive for ASCII letters, so 'a%' finds Aziza. In PostgreSQL, LIKE is case-sensitive and you need ILIKE for case-insensitive matching. The portable solution is to normalise both sides:

WHERE LOWER(first_name) LIKE LOWER('a%');

:::

Filtering with NULL

Everything from Chapter 1 in one place, because this is where students actually meet it.

-- students with no city recorded
SELECT first_name FROM students WHERE city IS NULL;          -- Sevara

-- enrollments not yet graded
SELECT enrollment_id, status FROM enrollments WHERE grade IS NULL;
-- 6 rows: 3, 8, 12, 16, 19, 20

-- enrollments that HAVE been graded
SELECT COUNT(*) FROM enrollments WHERE grade IS NOT NULL;     -- 14

Result 1

8 rows

Result 2

… first 15 of 674 rows

674 rows

Result 3

1 row

1 ms

:::formula The three-valued truth table TRUE FALSE UNKNOWN AND T | TRUE FALSE UNKNOWN F | FALSE FALSE FALSE ← FALSE wins for AND U | UNKNOWN FALSE UNKNOWN

OR T | TRUE TRUE TRUE ← TRUE wins for OR F | TRUE FALSE UNKNOWN U | TRUE UNKNOWN UNKNOWN

WHERE keeps a row only when the final value is TRUE. :::

Two useful consequences worth showing:

• FALSE AND UNKNOWN = FALSE. So if any part of an AND chain is definitely false, NULLs elsewhere do not matter.
• TRUE OR UNKNOWN = TRUE. So WHERE city = 'Tashkent' OR city IS NULL works exactly as you would hope.

The NOT IN + NULL trap

This is the most dangerous single behaviour in everyday SQL, and it produces no error message — just a silently empty result.

-- "Which teachers have never taught Machine Learning (course 5)?"
SELECT full_name FROM teachers
WHERE teacher_id NOT IN (
    SELECT teacher_id FROM enrollments WHERE course_id = 5
);

-- Expected: Rustam Halilov, Kamola Ergasheva, Sardor Yoqubov
-- Actual:   0 rows.  Nothing. No error.

0 rows

1 ms

Why. The subquery returns (1, 1, 1, NULL) — enrollment 19 has no teacher assigned yet. Now expand what NOT IN means for Rustam (teacher_id 2):

A single NULL anywhere in the list makes NOT IN return UNKNOWN for every non-matching row. The whole result vanishes.

Three ways to fix it

-- Fix 1: exclude NULLs from the subquery (simplest)
SELECT full_name FROM teachers
WHERE teacher_id NOT IN (
    SELECT teacher_id FROM enrollments
    WHERE course_id = 5 AND teacher_id IS NOT NULL
);

-- Fix 2: NOT EXISTS — immune to NULL by design (the professional's choice)
SELECT full_name FROM teachers t
WHERE NOT EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.course_id = 5 AND e.teacher_id = t.teacher_id
);

-- Fix 3: LEFT JOIN ... WHERE right side IS NULL  (Chapter 5)

Result 1

10 rows

Result 2

10 rows

1 ms

:::tip 💡 The rule to give students Never write NOT IN against a subquery unless you are certain the column can never be NULL. Prefer NOT EXISTS. Plain IN is safe — a NULL in the list simply never matches, which is what you would expect anyway. It is only the negation that breaks. :::

COALESCE, IFNULL, NULLIF

COALESCE(a, b, c, ...) returns the first argument that is not NULL. It works in every database, so prefer it over IFNULL (SQLite/MySQL) or ISNULL (SQL Server).

SELECT
    first_name,
    city                              AS raw_city,
    COALESCE(city, 'Not specified')   AS clean_city
FROM students
WHERE student_id IN (6, 7, 8);

3 rows

0 ms

first_nameraw_cityclean_city

OtabekTashkentTashkent SevaraNULLNot specified TimurAndijanAndijan

It takes as many arguments as you like, and falls through them in order:

SELECT COALESCE(mobile_phone, home_phone, work_phone, 'no contact') AS phone
FROM some_contacts_table;

NULLIF — the opposite direction NULLIF(a, b) returns NULL when a = b, otherwise it returns a. Its single most useful job is preventing division by zero:

-- if the denominator is 0, NULLIF makes it NULL,
-- and x / NULL is NULL instead of an error
SELECT total_score * 1.0 / NULLIF(attempts, 0) AS avg_score
FROM some_results_table;

:::gotcha ⚠️ Do not COALESCE numbers to 0 without thinking COALESCE(grade, 0) turns "not graded yet" into "scored zero". A student's average then collapses from 90 to 60, and the report is wrong in a way nobody notices. Replacing NULL is a business decision, not a formatting step: ask whether the missing value should be ignored (leave it NULL) or genuinely counted as zero. :::

Filtering by date

In SQLite, dates stored as 'YYYY-MM-DD' text compare correctly with <, > and BETWEEN, because that format sorts alphabetically in the same order it sorts chronologically.

-- enrolled during February 2026 — half-open range, always safe
SELECT enrollment_id, enrolled_at FROM enrollments
WHERE enrolled_at >= '2026-02-01' AND enrolled_at < '2026-03-01';
-- 12 rows

-- extract parts of a date (SQLite)
SELECT
    enrolled_at,
    strftime('%Y', enrolled_at) AS year,
    strftime('%m', enrolled_at) AS month
FROM enrollments LIMIT 3;

-- everything enrolled in 2025
SELECT COUNT(*) FROM enrollments
WHERE strftime('%Y', enrolled_at) = '2025';    -- 6

Result 1

0 rows

Result 2

3 rows

Result 3

1 row

1 ms

:::gotcha ⚠️ A function on the column disables the index WHERE strftime('%Y', enrolled_at) = '2025' forces the database to compute strftime for every single row before it can compare — so any index on enrolled_at is useless. The range version does the same job and stays index-friendly:

WHERE enrolled_at >= '2025-01-01' AND enrolled_at < '2026-01-01'; The general rule, and it applies far beyond dates: keep the column bare on the left side of the comparison. WHERE price * 1.1 > 1000 is slow; WHERE price > 1000 / 1.1 is fast. :::

Dialect note — the same job in other databases:

Common mistakes

Interview questions

Q: Why can NOT IN return no rows at all?

Because if the value list contains a NULL, every comparison becomes UNKNOWN and WHERE keeps only TRUE. The result is empty with no error. Use NOT EXISTS, which is unaffected.

Q: IN vs EXISTS — what is the difference?

IN compares a value against a materialised list of values. EXISTS asks "does at least one matching row exist?" and can stop at the first hit. EXISTS is NULL-safe and usually better for correlated subqueries over big tables; IN is clearer for a short literal list. Modern optimisers often rewrite one into the other anyway.

Q: Is BETWEEN inclusive?

Yes, on both ends — it is exactly >= a AND <= b. That is precisely why it is unsafe on timestamps: '2026-02-28' means midnight at the start of the 28th.

Q: Difference between % and _ in LIKE?

% matches any number of characters including zero; _ matches exactly one.

Q: How do you search for a literal % character?

Escape it: WHERE code LIKE '50\%%' ESCAPE '\' finds codes starting with "50%".

Q: What does COALESCE do, and how is it different from NULLIF?

COALESCE returns the first non-NULL argument — it removes NULLs. NULLIF(a,b) returns NULL when a equals b — it creates a NULL, most often to avoid division by zero.

Q: Why is WHERE YEAR(order_date) = 2025 considered bad practice?

Wrapping the column in a function makes the predicate non-sargable: the index on order_date cannot be used, so the engine scans every row. Rewrite as a range.

Summary

• IN = a chain of ORs, and its list can be a subquery.
• NOT IN + a NULL in the list = zero rows, silently. Use NOT EXISTS.
• BETWEEN includes both endpoints — which is why half-open ranges (>= start AND < next_start) are the safe habit for dates and timestamps.
• LIKE: % = many characters, _ = exactly one. A leading % prevents index use; case sensitivity depends on the database.
• Compare with IS NULL / IS NOT NULL, never =. Remember FALSE AND UNKNOWN = FALSE, TRUE OR UNKNOWN = TRUE.
• COALESCE replaces NULL; NULLIF creates one (division-by-zero guard). Replacing NULL with 0 is a business decision, not formatting.
• Never wrap the filtered column in a function — it disables the index. Rewrite as a range.

Next: Chapter 4 turns many rows into one number — COUNT, SUM, AVG, GROUP BY and HAVING.