IN, BETWEEN, and LIKE

Three operators make common conditions shorter and clearer.

IN — match any value in a list

SELECT id, status
FROM orders
WHERE status IN ('pending', 'shipped');

+----+---------+
| id | status  |
+----+---------+
|  3 | shipped |
|  6 | pending |
|  7 | shipped |
|  8 | pending |
+----+---------+

That's the same as status = 'pending' OR status = 'shipped'. NOT IN excludes the listed values.

BETWEEN — a range, inclusive

SELECT name, price
FROM products
WHERE price BETWEEN 40 AND 100;

+---------------------+-------+
|        name         | price |
+---------------------+-------+
| Mechanical Keyboard |    89 |
| Laptop Stand        |    42 |
| Webcam              |  59.9 |
+---------------------+-------+

BETWEEN 40 AND 100 includes both ends — it's the same as price >= 40 AND price <= 100.

LIKE — simple patterns

LIKE matches text against a pattern with two wildcards:

• % — any number of characters (including none)
• _ — exactly one character

SELECT name FROM customers WHERE name LIKE 'A%';

+--------------+
|     name     |
+--------------+
| Ada Lovelace |
| Alan Turing  |
+--------------+

SELECT email FROM customers WHERE email LIKE '%a%@%';

+----------------------+
|        email         |
+----------------------+
| ada@example.com      |
| alan@example.com     |
| grace@example.com    |
| margaret@example.com |
+----------------------+

The second finds emails with an a somewhere before the @.

Case sensitivity varies: SQLite's LIKE ignores case for English letters, MySQL's usually does, and PostgreSQL's doesn't — PostgreSQL has ILIKE for case-insensitive matching. For complex patterns, databases offer regular expressions (~ in PostgreSQL, REGEXP in MySQL); the Regex Tester helps you build them.

Common mistakes

• BETWEEN with timestamps: ordered_at BETWEEN '2025-04-01' AND '2025-04-30' misses everything after midnight on the 30th. For date ranges, prefer >= '2025-04-01' AND < '2025-05-01'.
• A leading wildcard (LIKE '%son') can't use an index, so it's slow on big tables. (Indexes are a Level 3 topic.)

Practice

Find products whose name contains the word USB or Stand.

SELECT name
FROM products
WHERE name LIKE '%USB%' OR name LIKE '%Stand%';

+--------------+
|     name     |
+--------------+
| USB-C Hub    |
| Laptop Stand |
+--------------+