Beyond equality and numeric comparisons, SQL has practical conditions for text patterns, membership in a list, ranges, and missing information. LIKE, IN, BETWEEN, and NULL tests make everyday queries much more flexible.
Using LIKE for pattern matching
name = 'A' matches only a name exactly equal to A, not Alice. To find names beginning with A, use name LIKE 'A%'. The percent sign is a wildcard representing any number of characters.
A% — starts with A
%a — ends with a
%Coffee% — contains Coffee
O%ka — starts O, ends ka
SELECT *
FROM products
WHERE name LIKE '%Coffee%';LIKE case sensitivity depends on the database and configuration. First learn that percent represents any sequence of characters; verify product-specific behavior in real work.
Sort strings by whether they match
Drag every string to Match or No match. On touch devices, tap a string and then a destination.
LIKE 'A%' → AliceKNOWLEDGE QUIZ
Using IN for several possible values
Tokyo, Osaka, or Kyoto can be written with three OR conditions, but that becomes repetitive. city IN ('Tokyo', 'Osaka', 'Kyoto') means city equals any listed value. Numbers work too: id IN (1, 3, 4). Use NOT IN to exclude the list.
SELECT *
FROM users
WHERE city IN ('Tokyo', 'Osaka', 'Kyoto');Using BETWEEN for ranges
price BETWEEN 40 AND 80 normally includes both boundaries, roughly matching price >= 40 AND price <= 80. BETWEEN is also common for dates; timestamps with times require extra care around the upper boundary.
Observe BETWEEN boundaries
Change price or date bounds and compare exact boundaries with nearby values. BETWEEN includes both endpoints.
| id | name | city | |
|---|---|---|---|
| 1 | Alice | [email protected] | Tokyo |
| 2 | Bob | [email protected] | Osaka |
| 3 | Chris | [email protected] | Tokyo |
| 4 | Diana | [email protected] | Kyoto |
Sandbox ready; no real database is connected.KNOWLEDGE QUIZ
NULL means no known value
NULL usually means missing, unknown, or unavailable. It is not zero and not an empty string. Bob having a NULL phone means the database has no number for him—not that it stores the text “NULL.”
| id | name | phone |
|---|---|---|
| 1 | Alice | 09012345678 |
| 2 | Bob | NULL |
| 3 | Chris | 08012345678 |
| 4 | Diana | '' |
Do not write phone = NULL. Find missing numbers with phone IS NULL; find present values with phone IS NOT NULL. NULL is unknown, so ordinary equality does not apply.
An empty string is different too. It is a real string of length zero; NULL means no known value. Therefore phone IS NULL and phone = '' may return completely different rows.
Conditions combine naturally. Users in Tokyo or Osaka with known emails can use IN plus IS NOT NULL; products from 20 to 80 whose names contain o can use BETWEEN plus LIKE.
KNOWLEDGE QUIZ
Design three LIKE patterns
Write three queries against products.name: (1) begins with Coffee, (2) ends with Mug, and (3) contains board anywhere. Change only the LIKE pattern, then explain why each percent sign is in that position.
Ranges and missing values
Write two queries: (1) return name and price for products from 40 through 80, inclusive; (2) return users whose phone has no value. Then answer whether a user with an empty-string phone is selected by the second query, and why.
Optional: portable practice data
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;
CREATE TABLE users (id INTEGER, name VARCHAR(100), phone VARCHAR(30));
CREATE TABLE products (id INTEGER, name VARCHAR(100), price DECIMAL(10, 2));
INSERT INTO users VALUES
(1, 'Alice', '09012345678'), (2, 'Bob', NULL),
(3, 'Chris', '08012345678'), (4, 'Diana', '');
INSERT INTO products VALUES
(1, 'Keyboard', 80.00), (2, 'Mouse', 40.00),
(3, 'Coffee Mug', 15.00), (4, 'Backpack', 60.00);