LIKE, IN, BETWEEN and IS NULL

When you type only part of a surname into a search box, the website still finds the right person. And how do you find “students from Sumqayıt, Quba or Naxçıvan”, “products priced between 10 and 55” or “students without an email”? SQL has four special operators for this: LIKE, IN, BETWEEN and IS NULL.

LIKE: searching by pattern

LIKE compares text with a pattern. A pattern has two special symbols: % stands for any number of characters (even zero), and _ stands for exactly one character.

SELECT first_name, last_name
FROM students
WHERE last_name LIKE '%ova'
ORDER BY id;

first_name | last_name
Aysel | Məmmədova
Leyla | Hüseynova
Günay | Abbasova
Səbinə | Nəsirova
Fidan | Cəfərova

SELECT first_name
FROM students
WHERE first_name LIKE '_u%'
ORDER BY id;

first_name
Murad
Tural

IN: a list of values

Writing city = 'Sumqayıt' OR city = 'Quba' OR city = 'Naxçıvan' is long and tiring. The IN operator says the same thing briefly: the condition is true if the value is in the list in brackets. NOT IN does the opposite and picks values that are not in the list.

SELECT first_name, city
FROM students
WHERE city IN ('Sumqayıt', 'Quba', 'Naxçıvan')
ORDER BY id;

first_name | city
Elvin | Sumqayıt
Səbinə | Quba
Fidan | Naxçıvan
Orxan | Sumqayıt

SELECT name, category
FROM products
WHERE category NOT IN ('Electronics', 'Stationery')
ORDER BY id;

name | category
Backpack | Accessories
Desk lamp | Home
Water bottle | Accessories
Chess set | Games

BETWEEN: a range

The condition x BETWEEN a AND b is the same as x >= a AND x <= b, so both ends are included. Below, the Backpack, which costs exactly 55, is in the result. The smaller value always comes first: BETWEEN 55 AND 10 returns nothing.

SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 55
ORDER BY price;

name | price
Water bottle | 12
Desk lamp | 34.99
Chess set | 42
Backpack | 55

SELECT id, customer_id, order_date
FROM orders
WHERE order_date BETWEEN '2025-02-01' AND '2025-03-31'
ORDER BY order_date;

id | customer_id | order_date
3 | 2 | 2025-02-02
4 | 3 | 2025-02-10
5 | 4 | 2025-03-05
6 | 5 | 2025-03-18

NULL: no data

Any comparison or calculation with an unknown value gives an unknown result — NULL. Even NULL = NULL is not true but NULL: we can't know whether two unknown values are equal. And WHERE keeps only the rows where the condition is true.

SELECT NULL = NULL AS a,
       NULL + 5    AS b,
       5 > NULL    AS c;

a | b | c
NULL | NULL | NULL

-- wrong: the condition is never true
SELECT COUNT(*) AS found
FROM students
WHERE email = NULL;

found
0

-- right: IS NULL
SELECT first_name, last_name, email
FROM students
WHERE email IS NULL
ORDER BY id;

first_name | last_name | email
Elvin | Quliyev | NULL
Tural | İsmayılov | NULL

IS NOT NULL picks the rows that do have a value. To show something else instead of NULL in the result, use COALESCE(a, b, ...): it returns the first value in the list that is not NULL. SQLite and MySQL also have the short form IFNULL(a, b), and SQL Server has ISNULL(a, b); COALESCE works in all of them.

SELECT first_name,
       COALESCE(email, 'no email') AS contact
FROM students
WHERE grade = 11
ORDER BY id;

first_name | contact
Elvin | no email
Tural | no email
Fidan | fidan@example.com

SELECT first_name, city, age
FROM students
WHERE city IN ('Bakı', 'Sumqayıt')
  AND age BETWEEN 14 AND 15
  AND email IS NOT NULL
ORDER BY id;

first_name | city | age
Aysel | Bakı | 15
Leyla | Bakı | 14
Kamran | Bakı | 15
Orxan | Sumqayıt | 14

SELECT name, price
FROM products
-- use BETWEEN and IN
ORDER BY price;

name | price
Desk lamp | 34.99
Chess set | 42
Headphones | 120.5

SELECT first_name, last_name, email
FROM students
-- add the two conditions
ORDER BY first_name;

first_name | last_name | email
Kamran | Həsənov | kamran@example.com
Rəşad | Kərimov | rashad@example.com