BETWEEN, IN, and LIKE in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this WHERE Clause and Comparison Operators, Literal Values, Data Types, and Type Casting
Builds toward Subqueries in WHERE (IN, EXISTS, ANY, ALL), Pattern Matching (LIKE, ILIKE, SIMILAR TO, Regex)
What are BETWEEN, IN, and LIKE in SQL?
BETWEEN, IN, and LIKE are three WHERE operators that make common filter patterns cleaner to write.
They don't add new capabilities. You could express the same logic with the comparison operators you already know. What they add is readability. A range check with two bounds. A membership test against a list. A pattern match on a string. Each shows up constantly in analyst work, and writing them with the shorthand operators instead of long chains of OR keeps queries much easier to read.
How do you filter a range with BETWEEN in SQL?
You're filtering the product catalog for a specific price range. Your manager wants items between $50 and $200. That's BETWEEN:
SELECT name, price
FROM products
WHERE price BETWEEN 50 AND 200Both ends of the range are included. A price of exactly $50 qualifies. A price of exactly $200 qualifies. Anything in between qualifies. BETWEEN is shorthand for >= 50 AND <= 200 — both forms produce identical results.
How do you match a list of values with IN?
IN tests whether a value matches any member of a list. Instead of chaining multiple equality conditions with OR, you write:
SELECT id, status
FROM orders
WHERE status IN ('pending', 'shipped')Easy to extend — add another status to the list without restructuring anything. NOT IN inverts it: keep rows where the value doesn't match anything in the list.
How do the % and _ wildcards work in LIKE?
LIKE matches a string against a pattern. Two wildcards handle most pattern needs: % matches any sequence of characters (including none), and _ matches exactly one character:
SELECT name, price
FROM products
WHERE name LIKE 'Crest%'Any product whose name starts with "Crest" passes. The _ wildcard is more precise: 'Apex Titan 1_' matches "Apex Titan 15" but not "Apex Titan 15 Pro" because _ requires exactly one character at that position.
Is LIKE case-sensitive in PostgreSQL?
The one thing that trips people up: LIKE is case-sensitive.
WHERE name LIKE 'crest%' will not match a product called "Crest Pro 14" — lowercase c doesn't match uppercase C. For case-insensitive matching, use ILIKE:
SELECT name, title
FROM employees
WHERE title ILIKE '%manager%'ILIKE is PostgreSQL-specific. It matches regardless of case.
You write WHERE name LIKE 'apex%'. The table has a product called 'Apex Titan 15'. Does this row match?
Practice BETWEEN, IN, and LIKE in SQL
A Brightlane buyer is reviewing the mid-range product catalogue for a quarterly selection meeting. The range of interest covers all items priced between $50 and $200, inclusive of both endpoints.
Write a query to return the name and price of every qualifying product.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - A product priced exactly at
$50or exactly at$200qualifies — both endpoints are included.
Output:
- One row per qualifying product, with columns
nameandprice.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 BETWEEN, IN, and LIKE practice problems
Write a query to return the name and price of every qualifying product.
Write a query to return the ID and status of each such order.
Write a query to return the name and price of every product whose name begins with Crest.
Write a query to return each qualifying product's name, its regular price, and its discounted member price (the regular price scaled by 0.8).
Write a query to return the ID, name, and country of every customer in those three territories.
Write a query to return the name and title of every employee currently in a management role.
Write a query to return the ID, status, and total amount for every order that is neither delivered nor cancelled.
Write a query to return the name of every product that qualifies.
Write a query to return the name and price of any products that match the user's pattern as submitted.
Start learning to practice all 9 BETWEEN, IN, and LIKE problems, with instant grading and mastery tracking.
Common questions about BETWEEN, IN, and LIKE
Does BETWEEN include both end values?
Yes, both ends. A price between fifty and two hundred keeps a row priced at exactly fifty and one priced at exactly two hundred. It is shorthand for the two comparisons written out, and returns the same rows as writing them.
What happens if you write BETWEEN with the bounds the wrong way round?
You get no rows and no error. BETWEEN expects the smaller value first, so a range from two hundred down to fifty asks for rows that are both above two hundred and below fifty, which nothing satisfies.
Is IN the same as a chain of OR conditions?
For a list of literal values, yes, and it returns the same rows. IN is worth preferring because adding another value means adding another item to a list rather than restructuring the condition, which is where mistakes creep in.
How do you match a literal percent sign with LIKE?
Escape it, with a backslash before it or with your own escape character named in an ESCAPE clause. Left unescaped, a percent sign means any sequence of characters, so a pattern meant to find a discount label quietly matches everything instead.
