BETWEEN, IN, LIKE and NULL

The last page gave you WHERE with comparisons and AND/OR. This page adds four tools that make everyday filters shorter and clearer: BETWEEN for ranges, IN for lists, LIKE for text patterns, and IS NULL for missing values.

The last one matters most. Real data is full of gaps: a customer who skipped the city field, a product with no stock figure, a training example nobody has labelled yet. SQL handles missing values with NULL and a three-valued logic that surprises almost everyone once. Learn it here and you will avoid a whole family of silent bugs, the kind that quietly drop rows from a report or a training set.

What you will learn

• Ranges with BETWEEN (inclusive), and why time ranges are safer as >= start AND < end.
• Lists with IN and NOT IN.
• Text patterns with LIKE, % and _, and how case sensitivity differs per database (ILIKE, LOWER).
• What NULL means, three-valued logic, and IS NULL / IS NOT NULL.
• The NOT IN trap that returns nothing when the list contains a NULL.

BETWEEN: ranges, both ends included

SELECT name, price
FROM products
WHERE price BETWEEN 1000 AND 2500
ORDER BY price;

+---------------------------+-------+
| name                      | price |
+---------------------------+-------+
| Gift Card                 |  1000 |
| Python Crash Course       |  1200 |
| Laptop Stand              |  1500 |
| USB-C Hub                 |  2200 |
| Hands-On Machine Learning |  2500 |
+---------------------------+-------+

The Gift Card (exactly 1000) and Hands-On Machine Learning (exactly 2500) are both in: BETWEEN a AND b means >= a AND <= b. Two details: the smaller value must come first (BETWEEN 2500 AND 1000 matches nothing), and NOT BETWEEN keeps everything outside the range.

Date ranges

With plain dates, BETWEEN works well. Orders placed in March 2026:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY order_date;

+----------+------------+
| order_id | order_date |
+----------+------------+
|        6 | 2026-03-08 |
|        7 | 2026-03-15 |
|        8 | 2026-03-28 |
+----------+------------+

Now imagine the column held a date and time, as most event and log tables do. An order at 2 pm on 31 March is stored as '2026-03-31 14:00', and that is greater than '2026-03-31':

SELECT '2026-03-31 14:00' BETWEEN '2026-03-01' AND '2026-03-31' AS with_time,
       '2026-03-31'       BETWEEN '2026-03-01' AND '2026-03-31' AS date_only;

+-----------+-----------+
| with_time | date_only |
+-----------+-----------+
|         0 |         1 |
+-----------+-----------+

The last day loses every event after midnight. The habit that is always right is a half-open range: include the start, exclude the next period's start.

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2026-03-01'
  AND order_date <  '2026-04-01'
ORDER BY order_date;

+----------+------------+
| order_id | order_date |
+----------+------------+
|        6 | 2026-03-08 |
|        7 | 2026-03-15 |
|        8 | 2026-03-28 |
+----------+------------+

It gives the same rows here, works for dates and timestamps alike, and needs no "how many days has this month?" thinking. In ML work it is how you cut a time-based train/test split: train on < '2026-05-01', test on >= '2026-05-01'. The boundary belongs to exactly one side, so no row leaks into both.

IN and NOT IN: lists of values

IN is a short way to write several = joined by OR:

SELECT order_id, customer_id, status
FROM orders
WHERE status IN ('shipped', 'pending', 'cancelled')
ORDER BY order_id;

+----------+-------------+-----------+
| order_id | customer_id | status    |
+----------+-------------+-----------+
|        5 |           4 | cancelled |
|       12 |           2 | shipped   |
|       13 |           6 | pending   |
+----------+-------------+-----------+

That is the same as status = 'shipped' OR status = 'pending' OR status = 'cancelled', but shorter, and immune to the AND/OR precedence bug when you add more conditions. NOT IN keeps rows whose value is not in the list:

SELECT name, city
FROM customers
WHERE city NOT IN ('Dhaka', 'Chattogram')
ORDER BY customer_id;

+---------------+----------+
| name          | city     |
+---------------+----------+
| Rafiq Islam   | Sylhet   |
| Imran Hossain | Khulna   |
| Karim Uddin   | Rajshahi |
+---------------+----------+

Sadia, whose city is NULL, is missing again. Hold that thought; the NULL section explains it.

LIKE: matching text patterns

LIKE compares text with a pattern that has two wildcards:

SELECT name
FROM products
WHERE name LIKE '%-%'          -- any name with a hyphen in it
ORDER BY product_id;

+-----------------------------+
| name                        |
+-----------------------------+
| Hands-On Machine Learning   |
| 27-inch Monitor             |
| USB-C Hub                   |
| Noise-Cancelling Headphones |
+-----------------------------+

SELECT email
FROM customers
WHERE email LIKE '____@%'      -- four characters, then @
ORDER BY customer_id;

+------------------+
| email            |
+------------------+
| mitu@example.com |
+------------------+

A pattern with no wildcard is just an equality test: LIKE 'Dhaka' behaves like = 'Dhaka' (apart from case, below). To match a real % or _, choose an escape character and put it in front: LIKE '%10!%%' ESCAPE '!' finds text containing "10%". Any character works; ! is a safer choice than a backslash, which MySQL treats as special inside strings.

Upper and lower case

Here the databases differ, and it trips people up when a query moves:

-- PostgreSQL
SELECT name FROM products WHERE name LIKE '%keyboard%';
SELECT name FROM products WHERE name ILIKE '%keyboard%';

+------+
| name |
+------+
+------+
+---------------------+
| name                |
+---------------------+
| Mechanical Keyboard |
+---------------------+

The first query finds nothing; ILIKE (PostgreSQL only) finds the keyboard. The portable way, which gives the same answer everywhere, is to lower-case both sides:

SELECT name
FROM products
WHERE LOWER(name) LIKE '%keyboard%'
ORDER BY product_id;

+---------------------+
| name                |
+---------------------+
| Mechanical Keyboard |
+---------------------+

LIKE '%word%' must read every row, because an index cannot help with a pattern that starts with %. On a few thousand rows that is fine. For searching millions of documents or prompts, use full-text search (SQLite FTS5, PostgreSQL tsvector) or a vector search, both covered in the advanced tutorial.

NULL: the value that is not there

NULL means "no value": unknown, missing, or not applicable. It is not 0 and not the empty string ''. A stock of 0 (USB-C Hub) means "we have none"; a stock of NULL (the courses) means "there is no stock figure", which for a course makes sense.

Because NULL is unknown, any comparison with it gives an unknown result, also written NULL. Even NULL = NULL is unknown: two unknown cities might or might not be the same city.

SELECT NULL = NULL  AS equal,
       NULL <> 'Dhaka' AS not_equal,
       NULL + 1     AS plus_one,
       NULL IS NULL AS is_null;

+-------+-----------+----------+---------+
| equal | not_equal | plus_one | is_null |
+-------+-----------+----------+---------+
| NULL  | NULL      | NULL     |       1 |
+-------+-----------+----------+---------+

So SQL logic has three values: true, false and unknown. WHERE keeps a row only when the condition is true; false and unknown are both dropped. That is why city <> 'Dhaka' and city NOT IN ('Dhaka', 'Chattogram') lost Sadia: for her row the answer was unknown.

A useful way to read it: "unknown AND false" is false whatever the unknown turns out to be; "unknown OR true" is true for the same reason. Anything else stays unknown.

IS NULL and IS NOT NULL

To test for NULL, never use = NULL (always unknown, so it matches nothing). Use IS NULL or IS NOT NULL:

SELECT name, category_id, stock
FROM products
WHERE category_id IS NULL OR stock IS NULL
ORDER BY product_id;

+-------------------------+-------------+-------+
| name                    | category_id | stock |
+-------------------------+-------------+-------+
| SQL Masterclass         |           3 | NULL  |
| AI Engineering Bootcamp |           3 | NULL  |
| Gift Card               | NULL        | NULL  |
+-------------------------+-------------+-------+

And the full answer to "who does not live in Dhaka?", counting the unknown city as "not Dhaka":

SELECT name, city
FROM customers
WHERE city <> 'Dhaka' OR city IS NULL
ORDER BY customer_id;

+-----------------+------------+
| name            | city       |
+-----------------+------------+
| Tanvir Ahmed    | Chattogram |
| Rafiq Islam     | Sylhet     |
| Sadia Chowdhury | NULL       |
| Imran Hossain   | Khulna     |
| Karim Uddin     | Rajshahi   |
+-----------------+------------+

SQLite (3.39+) and PostgreSQL also have city IS DISTINCT FROM 'Dhaka', a comparison that treats NULL as an ordinary value and never returns unknown. MySQL's version is the null-safe equals <=>:

-- MySQL
SELECT name, city
FROM customers
WHERE NOT (city <=> 'Dhaka')
ORDER BY customer_id;

+-----------------+------------+
| name            | city       |
+-----------------+------------+
| Tanvir Ahmed    | Chattogram |
| Rafiq Islam     | Sylhet     |
| Sadia Chowdhury | NULL       |
| Imran Hossain   | Khulna     |
| Karim Uddin     | Rajshahi   |
+-----------------+------------+

pandas does it differently

If you filter the same data in pandas, the missing city behaves the other way round:

import pandas as pd
from sqlhelp import con

df = pd.read_sql("SELECT customer_id, name, city FROM customers ORDER BY customer_id", con)
print(df[df["city"] != "Dhaka"]["name"].tolist())
print(df[df["city"].isna()]["name"].tolist())

['Tanvir Ahmed', 'Rafiq Islam', 'Sadia Chowdhury', 'Imran Hossain', 'Karim Uddin']
['Sadia Chowdhury']

In pandas, a missing value (NaN) is "not equal" to "Dhaka", so Sadia is kept. In SQL the same filter drops her. Move a filter from SQL to pandas (or back) and your row counts can change. Decide what missing values should do, and write it explicitly in both places: IS NULL in SQL, .isna() in pandas.

The NOT IN trap

Which categories have no products? A natural first attempt uses a subquery, a query inside brackets whose result becomes the list (subqueries get their own page later):

SELECT name
FROM categories
WHERE category_id NOT IN (SELECT category_id FROM products)
ORDER BY category_id;

+------+
| name |
+------+
+------+

Nothing, yet Furniture (category 5) has no products. The inner query returns 1, 1, 2, 2, 2, 3, 3, 4, 4, 2, NULL: the Gift Card has no category. For Furniture, 5 NOT IN (1, 2, 3, 4, NULL) means 5 <> 1 AND … AND 5 <> NULL. The last part is unknown, so the whole condition is unknown, and the row is dropped. One NULL in the list empties the result. Remove the NULLs from the list:

SELECT name
FROM categories
WHERE category_id NOT IN (SELECT category_id FROM products
                          WHERE category_id IS NOT NULL)
ORDER BY category_id;

+-----------+
| name      |
+-----------+
| Furniture |
+-----------+

IN has a milder version of the same effect: stock IN (0, NULL) finds the USB-C Hub but never the courses, because nothing is ever "equal" to NULL. The safest pattern for "rows with no match" is NOT EXISTS, shown in Subqueries, EXISTS and Correlated Queries.

Where you will use this

• Training and evaluation windows: created_at >= '2026-01-01' AND created_at < '2026-05-01' for training, the next period for testing.
• Label lists: WHERE label IN ('spam', 'ham') to drop rows with unexpected labels before training.
• Finding the work to do: WHERE embedding IS NULL finds documents that still need embeddings; WHERE label IS NULL finds examples waiting for an annotator.
• Quick text search in logs: WHERE LOWER(prompt) LIKE '%refund%' finds the conversations about refunds before you build anything fancier.

Common mistakes

1. = NULL instead of IS NULL. WHERE city = NULL returns nothing, with no error. Write WHERE city IS NULL.
2. NOT IN with a list that can contain NULL. The result is empty. Add WHERE col IS NOT NULL inside the subquery, or use NOT EXISTS.
3. BETWEEN on timestamps. BETWEEN '2026-03-01' AND '2026-03-31' misses everything after midnight on the 31st. Use >= '2026-03-01' AND < '2026-04-01'.
4. Assuming LIKE's case rules. A search that works on SQLite or MySQL finds nothing on PostgreSQL. Write LOWER(col) LIKE '%word%' when case should not matter.
5. Forgetting that _ is a wildcard. LIKE 'user_1' also matches 'userX1'. Escape it: LIKE 'user!_1' ESCAPE '!'.

Try it yourself

1. Easy: List the payments with an amount from 3000 to 5000 taka, both included, smallest first.
2. Medium: List the orders placed in February or March 2026 (use a half-open range) whose status is delivered or cancelled (use IN).
3. Medium: Find the products whose name contains "learning" or "course" in any case, in a way that gives the same answer on all three databases.
4. Hard: List the products that are not out of stock, meaning anything except a stock of exactly 0, and keep the products with no stock figure. Then explain why WHERE stock NOT IN (0) gives a different answer.

Answers

-- 1
SELECT payment_id, amount
FROM payments
WHERE amount BETWEEN 3000 AND 5000
ORDER BY amount, payment_id;

-- 2
SELECT order_id, order_date, status
FROM orders
WHERE order_date >= '2026-02-01'
  AND order_date <  '2026-04-01'
  AND status IN ('delivered', 'cancelled')
ORDER BY order_date, order_id;

-- 3: LOWER() on the column gives the same answer on every database
SELECT name
FROM products
WHERE LOWER(name) LIKE '%learning%'
   OR LOWER(name) LIKE '%course%'
ORDER BY product_id;

-- 4
SELECT name, stock
FROM products
WHERE stock <> 0 OR stock IS NULL
ORDER BY product_id;

For 4: stock NOT IN (0) is the same as stock <> 0, which is unknown for the three products with NULL stock, so they disappear. That gives 7 rows instead of 10.

Summary

• BETWEEN a AND b includes both ends. For time ranges, prefer >= start AND < next_start.
• IN (…) replaces a chain of ORs; NOT IN (…) returns nothing if the list holds a NULL.
• LIKE uses % (any run) and _ (one character). Case rules differ: SQLite and MySQL ignore case by default, PostgreSQL does not (ILIKE, LOWER).
• NULL is unknown. Comparisons with it are unknown, and WHERE keeps only true. Test with IS NULL / IS NOT NULL.
• pandas treats missing values differently in filters; be explicit about NULLs in both.

Next: Reading Errors and Debugging a Query. You can now write real filters, so you will also write real mistakes. The next page shows how to read what the database tells you, and a routine for finding the bugs it does not tell you about.