Skip to content

Beginner: asking questions · SELECT: filtering and sorting · topic 8 of 62 · 35 min

IN, BETWEEN, LIKE and NULL

Explanation

IN checks a value against a list, BETWEEN checks a range including both ends, and LIKE matches text patterns with % (any number of characters) and _ (exactly one). NULL means unknown: = NULL never matches, so you must write IS NULL or IS NOT NULL.

IN: one of a list

IN is a short way to write many ORs on the same column. city IN ('Pune', 'Mumbai') means city = 'Pune' OR city = 'Mumbai'. It is shorter, easier to read, and you cannot get the brackets wrong. NOT IN means "none of these".

sql
SELECT name, city FROM customers WHERE city IN ('Pune', 'Mumbai');
-- Asha Verma  | Pune
-- Imran Khan  | Pune
-- Sneha Patil | Mumbai

BETWEEN: a range, both ends included

x BETWEEN a AND b means x >= a AND x <= b. Both ends are included. That works for numbers, text and dates stored as YYYY-MM-DD.

sql
SELECT id, order_date FROM orders
WHERE order_date BETWEEN '2025-02-01' AND '2025-03-15';
-- 103 | 2025-02-14
-- 104 | 2025-02-20
-- 105 | 2025-03-02
-- 106 | 2025-03-15   <- the end date is included
Careful: BETWEEN with timestamps is a trap. '2025-03-15 10:00' BETWEEN '2025-03-01' AND '2025-03-15' is false, because as text '2025-03-15 10:00' comes after '2025-03-15'. For columns that hold a time, write order_time >= '2025-03-01' AND order_time < '2025-03-16'.

LIKE: text patterns

LIKE compares text with a pattern. Two characters have a special meaning, and they are called wildcards:

PatternMeaningMatches
'P%'Starts with PPressure Cooker 3L, Power Bank 10000mAh
'%Kumar'Ends with KumarRavi Kumar, Divya Kumar
'%box%'Contains box anywhereSteel Tiffin Box
'_a%'Second letter is aRavi Kumar, Karan Mehta

% stands for any number of characters, including none. _ stands for exactly one character. To search for a real % sign, pick an escape character: LIKE '%50!%%' ESCAPE '!' finds "Big 50% sale".

Is LIKE case-sensitive?

It depends on the database. In SQLite, LIKE ignores case for the English letters A to Z, so '%box%' matched "Box" above; for other letters, like Ä, case still matters. In PostgreSQL, LIKE is case-sensitive, and ILIKE is the case-insensitive version. In MySQL it depends on the collation (the sorting rules of the column); the default collations ignore case.

NULL and three-valued logic

NULL is not a value; it is a marker for "unknown" or "missing". Priya Nair has no email. Is her email equal to NULL? SQL cannot say yes, because an unknown thing compared with an unknown thing is still unknown. So SQL logic has three results: true, false and unknown. WHERE keeps only true rows.

sql
SELECT id, name FROM customers WHERE email = NULL;   -- no rows, ever
SELECT id, name FROM customers WHERE email IS NULL;  -- 3 | Priya Nair
                                                     -- 6 | Arjun Reddy
IS NULL and IS NOT NULL are the only correct tests for NULL.
ExpressionResult
NULL = NULLunknown (NULL)
NULL IS NULLtrue
1 <> NULLunknown
NULL OR truetrue
NULL AND falsefalse

This explains the NOT IN surprise: WHERE dept_id NOT IN (1, 2) does not return Deepak, whose dept_id is NULL. If you want him too, add OR dept_id IS NULL.

Common mistakes

  • Writing = NULL or <> NULL. Both never match. Use IS NULL and IS NOT NULL.
  • Forgetting % in LIKE: LIKE 'Kumar' matches only the exact text Kumar. Use '%Kumar'.
  • Using BETWEEN with the bigger number first: BETWEEN 100 AND 50 returns nothing. Put the smaller value first.
  • Assuming LIKE ignores case everywhere. It does in SQLite for A to Z, but not in PostgreSQL; use ILIKE there.
  • Expecting NOT IN to include NULL rows. Add OR column IS NULL when you want them.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

IN, BETWEEN, LIKE and NULL · 1/7

IN, BETWEEN, LIKE, NULL

Four handy tools for WHERE: IN, BETWEEN, LIKE, and the right way to handle NULL.

Prefer a video by a teacher? Find free videos on “sql in between like wildcard is null tutorial” on YouTube ↗

In real life: Cleaning a customer list before a campaign

Before an email offer, a marketing team pulls customers in certain cities who joined in a date range and actually have an email address. One query with IN, BETWEEN and IS NOT NULL does it. Forgetting IS NOT NULL would mean trying to send emails to blank addresses.

sql
SELECT name, email
FROM customers
WHERE city IN ('Pune', 'Mumbai', 'Delhi')
  AND joined BETWEEN '2024-01-01' AND '2025-12-31'
  AND email IS NOT NULL;
Returns Asha, Ravi, Imran and Sneha with their emails.

Practice (0/4 done)

Task 1 · Code · SQL

From customers, return name and city of customers who live in Pune or Mumbai. Use IN.

Task 2 · Code · SQL

From orders, return id and order_date of orders placed from 1 February 2025 to 15 March 2025, both days included.

Task 3 · Code · SQL

From students, return name and city of every student whose name ends with 'Kumar'.

Task 4 · Code · SQL

Which customers have no email address? Return id and name.

Quick quiz

Question 1

1.Which rows does marks BETWEEN 60 AND 80 include?

Question 2

2.Which names match LIKE '_a%'?

Question 3

3.Why does WHERE email = NULL return no rows?

Question 4

4.You need a case-insensitive pattern match in PostgreSQL. What do you use?

Question 5

5.Deepak's dept_id is NULL. Which WHERE returns him together with everyone outside departments 1 and 2?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

Which rows does marks BETWEEN 60 AND 80 include?

Answer: Marks from 60 to 80, both included

BETWEEN includes both ends.

Which names match LIKE '_a%'?

Answer: Names whose second letter is a

_ is exactly one character, then a, then % for anything.

Why does WHERE email = NULL return no rows?

Answer: Comparing with NULL gives unknown, and WHERE keeps only true rows

Use IS NULL to test for missing values.

You need a case-insensitive pattern match in PostgreSQL. What do you use?

Answer: ILIKE

PostgreSQL LIKE is case-sensitive; ILIKE ignores case.

Deepak's dept_id is NULL. Which WHERE returns him together with everyone outside departments 1 and 2?

Answer: dept_id NOT IN (1, 2) OR dept_id IS NULL

NOT IN never matches a NULL value, so add OR dept_id IS NULL.

Deepak has dept_id NULL. Does WHERE dept_id <> 1 return him?Related

Answer: No, because the comparison gives unknown, and WHERE keeps only true

Any comparison with NULL gives unknown; use IS NULL to find such rows.

From WHERE: comparison and logic

How do you find rows where email is missing?Related

Answer: WHERE email IS NULL

NULL is not equal to anything, so = NULL never matches. Use IS NULL.

From SELECT and WHERE: finding rows

The subquery SELECT dept_id FROM employees returns 1, 2, 3, 4 and one NULL. How many rows does WHERE id NOT IN (that subquery) return from departments (ids 1 to 5)?Related

Answer: 0

id <> NULL is unknown, so the NOT IN is never true for any row.

From IN, EXISTS and NOT EXISTS

What does COALESCE(NULL, NULL, 'Pune', 'Delhi') return?Related

Answer: 'Pune'

COALESCE returns the first argument that is not NULL.

From NULL: the missing value

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: ORDER BY, LIMIT and OFFSET →

Next steps

  1. 2 more topics in “SELECT: filtering and sorting” before its exam: ORDER BY, LIMIT and OFFSET, DISTINCT and how a query runs.
  2. Module 2 exam: SELECT, filtering and sorting · 15 questions · 25 min, without help. 5 topics of this module still to finish first.
  3. Then the next module, Functions, CASE and NULL. 62 topics left in the course before the final exam.

← Previous: WHERE: comparison and logic