Filtering deep (IN, LIKE, NULL)

Chapter 3 13 code blocks 2652 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

1 ms

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

WHERE enrolled_at >= '2026-02-01'
  AND enrolled_at <  '2026-03-01';   -- ← always correct, dates or timestamps

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

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

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

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

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;

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

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.