IN, BETWEEN, LIKE and REGEXP
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Some filters come up so often that SQL has a short form for them: one of several values (IN), a value inside a range (BETWEEN), and text that matches a pattern (LIKE and REGEXP). Each one goes inside an ordinary WHERE clause.
IN with a list
IN tests whether a value is one of a list, as in city IN ('Pune', 'Delhi'). It means the same as joining equality tests with OR. NOT IN keeps the values that are in none of the list.
A chain of OR tests gets long. IN keeps the whole list in one bracket: the first query finds customers in three cities, the second keeps the orders that are in neither of two statuses.
Example 1 · IN and NOT IN instead of long OR chains
SELECT name, cityFROM customersWHERE city IN ('Pune', 'Delhi', 'Kochi')ORDER BY customer_id;
SELECT order_id, customer_id, statusFROM ordersWHERE status NOT IN ('DELIVERED', 'CANCELLED')ORDER BY order_id;+--------------+-------+ | name | city | +--------------+-------+ | Kabir Singh | Delhi | | Priya Sharma | Pune | | Divya Nair | Kochi | | Rahul Verma | Delhi | | Sneha Patil | Pune | +--------------+-------+ +----------+-------------+---------+ | order_id | customer_id | status | +----------+-------------+---------+ | 7 | 2 | SHIPPED | | 10 | 1 | SHIPPED | | 11 | 8 | PLACED | | 13 | 9 | PLACED | | 15 | 2 | PLACED | +----------+-------------+---------+
What this code does
- The first query keeps a customer when the city is any one of the three in the list. Writing it with OR would need three comparisons.
NOT INkeeps the orders whose status is in neither list item, so only open orders (PLACED or SHIPPED) remain.
An IN list is already grouped, so status IN ('PLACED', 'SHIPPED') AND customer_id = 2 has no AND-before-OR bug. The list can hold text, numbers or dates, but all of one kind.
NOT IN behaves strangely when the list contains NULL. The next lesson, on NULL values, explains why.BETWEEN and its inclusive ends
BETWEEN low AND high tests whether a value lies in a range, including both ends. price BETWEEN 500 AND 2000 equals price >= 500 AND price <= 2000.
Marks, prices and ages are often asked as ranges. The first query keeps prices from 499 to 899, both ends included; the second example shows a trap when the column is a DATETIME.
Example 2 · BETWEEN includes both ends
SELECT name, priceFROM productsWHERE price BETWEEN 499 AND 899ORDER BY price;
SELECT student_id, course_id, marksFROM enrollmentsWHERE course_id = 4 AND marks NOT BETWEEN 50 AND 100ORDER BY student_id;+---------------------+--------+ | name | price | +---------------------+--------+ | Clean Code | 499.00 | | Gift Card Rs. 500 | 500.00 | | Hands-On Python | 650.00 | | Logitech M331 Mouse | 849.00 | | Cotton Kurta | 899.00 | +---------------------+--------+ +------------+-----------+-------+ | student_id | course_id | marks | +------------+-----------+-------+ | 5 | 4 | 45 | +------------+-----------+-------+
What this code does
Clean Codecosts exactly 499 andCotton Kurtaexactly 899, and both are kept: BETWEEN includes its ends.ORDER BY pricelists them from cheapest.NOT BETWEEN 50 AND 100keeps the marks under 50 in Engineering Mathematics (course 4): only student 5, with 45.
Example 3 · Trap: BETWEEN on a DATETIME column
DROP TABLE IF EXISTS upi_payments;CREATE TABLE upi_payments ( txn_id INT PRIMARY KEY, payee VARCHAR(40) NOT NULL, amount DECIMAL(8,2) NOT NULL, paid_at DATETIME NOT NULL);INSERT INTO upi_payments VALUES (1, 'Hostel fees', 2800.00, '2025-06-01 08:15:00'), (2, 'Metro card', 500.00, '2025-06-14 19:02:00'), (3, 'Fest ticket', 350.00, '2025-06-30 00:00:00'), (4, 'Book store', 649.00, '2025-06-30 18:45:00'), (5, 'Phone recharge', 299.00, '2025-07-01 09:30:00');
-- Looks right, but misses the evening of 30 June.SELECT txn_id, payee, paid_atFROM upi_paymentsWHERE paid_at BETWEEN '2025-06-01' AND '2025-06-30'ORDER BY txn_id;
-- Correct: from the 1st, up to but not including 1 July.SELECT txn_id, payee, paid_atFROM upi_paymentsWHERE paid_at >= '2025-06-01' AND paid_at < '2025-07-01'ORDER BY txn_id;+--------+-------------+---------------------+ | txn_id | payee | paid_at | +--------+-------------+---------------------+ | 1 | Hostel fees | 2025-06-01 08:15:00 | | 2 | Metro card | 2025-06-14 19:02:00 | | 3 | Fest ticket | 2025-06-30 00:00:00 | +--------+-------------+---------------------+ +--------+-------------+---------------------+ | txn_id | payee | paid_at | +--------+-------------+---------------------+ | 1 | Hostel fees | 2025-06-01 08:15:00 | | 2 | Metro card | 2025-06-14 19:02:00 | | 3 | Fest ticket | 2025-06-30 00:00:00 | | 4 | Book store | 2025-06-30 18:45:00 | +--------+-------------+---------------------+
What this code does
- For a DATETIME,
'2025-06-30'means 30 June at midnight. The fest ticket paid at exactly 00:00:00 is kept, but the book store payment at 18:45 the same day is lost. - The half-open range
>= '2025-06-01' AND < '2025-07-01'takes every moment of June and nothing from July. It works the same for DATE columns, so it is a safe habit.
The low value must come first: BETWEEN 2000 AND 500 is no error, but it matches nothing.
- Numbers and DATE columns:
BETWEENis clear and safe. - DATETIME columns: use
paid_at >= '2025-06-01' AND paid_at < '2025-07-01'instead. NOT BETWEEN 35 AND 100keeps values below 35 or above 100.
Predict the output
How many rows?
Read the code and pick what it prints.
Round 1 of 2
Remember: BETWEEN includes both ends.
LIKE with % and _
LIKE compares text with a pattern. In a pattern, % stands for any number of characters, even none, and _ stands for exactly one character.
A shopper typing "python" expects "Hands-On Python", but name = 'python' finds only an exact match. The queries below find names by their start, a word inside, and an exact length.
Example 4 · LIKE: starts with, contains, exactly one character
SELECT nameFROM customersWHERE name LIKE 'R%'ORDER BY customer_id;
SELECT nameFROM productsWHERE name LIKE '%python%' OR name LIKE '%code%'ORDER BY product_id;
SELECT code, titleFROM coursesWHERE code LIKE 'CS___'ORDER BY course_id;+-------------+ | name | +-------------+ | Ravi Kumar | | Rahul Verma | +-------------+ +-----------------+ | name | +-----------------+ | Clean Code | | Hands-On Python | +-----------------+ +-------+------------------+ | code | title | +-------+------------------+ | CS201 | Data Structures | | CS202 | Database Systems | +-------+------------------+
What this code does
'R%'finds names that start with R.'%python%'findsHands-On Pythoneven though the pattern is in small letters, because LIKE ignores case with the default collation.'CS___'has three underscores, so it needs exactly three characters after CS:CS201andCS202match.
With the default MySQL 8 collation, LIKE ignores case, just like =: '%python%' matches Python.
'R%': starts with R.'%Kumar': ends with Kumar.'%python%': contains python.NOT LIKE '%@example.com': every email from another domain.- To match a real
%or_, put a backslash before it:LIKE '%10\%%'finds text containing "10%".
REGEXP for patterns such as valid PIN codes
A regular expression is a pattern language that describes an exact text shape, such as six digits with the first one not zero. REGEXP tests a column against one, which LIKE cannot do.
An Indian PIN code is 6 digits and never starts with 0. REGEXP '^[1-9][0-9]{5}$' is true only for valid ones, so NOT REGEXP lists the typos to fix before a parcel goes astray.
Example 5 · REGEXP: find invalid PIN codes
DROP TABLE IF EXISTS delivery_addresses;CREATE TABLE delivery_addresses ( address_id INT PRIMARY KEY, city VARCHAR(30) NOT NULL, pin_code VARCHAR(10) NOT NULL);INSERT INTO delivery_addresses VALUES (1, 'Bengaluru', '560001'), (2, 'Chennai', '600028'), (3, 'Mumbai', '40005A'), (4, 'Delhi', '110 001'), (5, 'Pune', '041101'), (6, 'Kochi', '682011'), (7, 'Jaipur', '30200');
-- Valid: 6 digits, first digit 1 to 9SELECT address_id, city, pin_codeFROM delivery_addressesWHERE pin_code REGEXP '^[1-9][0-9]{5}$'ORDER BY address_id;
-- Everything else needs fixingSELECT address_id, city, pin_codeFROM delivery_addressesWHERE pin_code NOT REGEXP '^[1-9][0-9]{5}$'ORDER BY address_id;+------------+-----------+----------+ | address_id | city | pin_code | +------------+-----------+----------+ | 1 | Bengaluru | 560001 | | 2 | Chennai | 600028 | | 6 | Kochi | 682011 | +------------+-----------+----------+ +------------+--------+----------+ | address_id | city | pin_code | +------------+--------+----------+ | 3 | Mumbai | 40005A | | 4 | Delhi | 110 001 | | 5 | Pune | 041101 | | 7 | Jaipur | 30200 | +------------+--------+----------+
What this code does
- The pattern means: start, one digit 1–9, exactly five more digits, end. So
40005A(a letter),110 001(a space),041101(starts with 0) and30200(only 5 digits) all fail. NOT REGEXPturns the check into a data-cleaning report: the four rows a support team must correct before delivery.
Example 6 · REGEXP: valid Indian mobile numbers
SELECT name, phone, phone REGEXP '^[6-9][0-9]{9}$' AS valid_mobileFROM customersWHERE city IN ('Chennai', 'Pune')ORDER BY customer_id;+--------------+------------+--------------+ | name | phone | valid_mobile | +--------------+------------+--------------+ | Ravi Kumar | 9876500002 | 1 | | Meena Iyer | NULL | NULL | | Priya Sharma | 9876500005 | 1 | | Sneha Patil | 9876500009 | 1 | +--------------+------------+--------------+
What this code does
- An Indian mobile number has 10 digits and starts with 6, 7, 8 or 9. The test in the SELECT list returns
1for true and0for false. - Meena Iyer has no phone, so her
phoneand the test are bothNULL, meaning "unknown". The next lesson is all about NULL.
The pieces used most:
^= start of the text,$= end. Without them the pattern may match anywhere inside.[0-9]= one digit;[6-9]= one digit from 6 to 9;[A-Z]= one letter.{5}= the item before it exactly 5 times;+= one or more times.- With the default collation
REGEXPignores case.REGEXP_LIKE(col, pattern, 'c')makes it case-sensitive. MySQL 8.0 and later support all of these.
Find the bug
Find the wrong pattern
Select the line that is wrong.
This should list invalid PIN codes but misses 7-digit codes. Which line is wrong?
Round 1 of 2Next step
Check your understanding
11 quick questions