IN tests membership, BETWEEN tests an inclusive range, and ANY or ALL compare a value with values returned by a subquery.
These operators let you express list and range questions compactly. They are related, but each has a different rule for what makes a comparison succeed. NULL values and empty subquery results are the details most likely to change an answer unexpectedly.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How IN, BETWEEN, ANY and ALL works
IN asks whether a value equals any member of a list or a one-column subquery. status IN ('paid','pending') expresses two acceptable labels without repeating the status column in an OR chain. A subquery version must return one compatible column. IN does not require the list to be sorted, and repeating the same literal does not make a matching row appear twice: it is a predicate applied to that row, not a join that generates matches.
BETWEEN includes both endpoints. amount BETWEEN 10 AND 20 means amount >= 10 AND amount <= 20, so both ten and twenty qualify. The lower endpoint should be written first; an ordinary BETWEEN does not swap them. For timestamps, use a half-open interval such as value >= start AND value < next_start. An inclusive midnight endpoint usually excludes the rest of that final day, while a manufactured end-of-day value can depend on timestamp precision.
How ANY and ALL compare with a subquery
ANY and ALL combine a comparison operator with a subquery. amount > ANY (...) is true if at least one returned comparison is true; amount > ALL (...) requires every returned comparison to be true. For a nonempty set of known numbers, greater than ANY resembles greater than the minimum and greater than ALL resembles greater than the maximum. That shortcut becomes unreliable when the subquery is empty or contains NULL, so reason about the actual comparison rule first.
Against an empty subquery, ANY is false and ALL is true. NULL may produce unknown if there is no decisive true result for ANY or no decisive false result for ALL. NOT IN is especially dangerous with a NULL in its list: a nonmatching value is compared against an unknown member and can become unknown rather than true. For excluding related rows, NOT EXISTS is often clearer. If you intentionally remove NULL from a subquery, explain why missing values should not participate in that business rule.
MySQL, PostgreSQL, SQL Server and SQLite
IN and BETWEEN run on all four systems. PostgreSQL, MySQL and SQL Server support quantified subquery comparisons with ANY and ALL; SQLite does not implement that syntax. PostgreSQL also has an ANY(array) form that is a different extension. A SQLite rewrite using EXISTS must account for NULL and empty-set semantics if it needs to reproduce unknown as well as true or false.
Check the engine-specific syntax and version requirements in PostgreSQL subquery comparisons.
Practice task and worked solution
Return sales with a known amount equal to five or thirty, using IN. Sort the two returned sales by id.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT id, amount FROM sales
WHERE amount IN (5,30) ORDER BY id;id | amount
102 | 30
106 | 5Sale 102 equals one listed value and sale 106 equals the other. The list’s order does not set the result order; ORDER BY id does. The missing amount does not equal either value and therefore does not pass WHERE.
Change the sample range to BETWEEN 20 AND 20: both twenty-valued sales should remain, proving that equal inclusive endpoints are valid. In PostgreSQL, MySQL or SQL Server, test a comparison against an empty ANY and ALL subquery and observe false versus true. Do not run that quantified syntax in SQLite; its supported comparison alternatives need a deliberate semantic rewrite.
SQL IN, BETWEEN, ANY and ALL: Comparison Reference Syntax
IN, BETWEEN, ANY and ALL
SELECT id, amount FROM sales
WHERE amount BETWEEN 10 AND 20
AND status IN ('paid','pending')
ORDER BY id;SQL IN, BETWEEN, ANY and ALL: Comparison Reference Summary
IN (...)tests whether a value equals any member of a list or a one-column subquery.BETWEEN low AND highis inclusive at both ends and works for numbers, text and dates.> ANY (subquery)needs at least one comparison to be true;> ALL (subquery)needs every one, and is true for an empty set.NOT INreturns no rows if the list or subquery containsNULL; useNOT EXISTSfor "has no related rows".- SQLite doesn't support
ANYorALL; rewrite them withMIN,MAXorEXISTS.
SQL IN, BETWEEN, ANY and ALL: Comparison Reference Examples
Each example shows the code, the output it produces and what each part does.
Amounts from 10 to 20 with known statuses
The range includes ten and twenty. Sale 102 is thirty and 106 is five, so both fail the range. Sale 104 has an unknown amount and does not make the complete predicate true. Both supplied status labels are accepted.
SELECT id, amount FROM sales
WHERE amount BETWEEN 10 AND 20
AND status IN ('paid','pending')
ORDER BY id;id | amount
101 | 10
103 | 20
105 | 20Common SQL IN, BETWEEN, ANY and ALL: Comparison Reference Mistakes
NOT IN with a NULL in the subquery
NOT IN (SELECT customer_id FROM sales) returns no customers here because sale 106 contributes NULL to the set. Dev should be the customer with no sales, but the NULL prevents the nonmembership condition becoming true. Use a correlated NOT EXISTS to express that relationship question.
SQL IN, BETWEEN, ANY and ALL: Comparison Reference in Real Projects
Price bands and status filters in reports
A price filter can accept a closed numeric band while an operational report accepts several status labels. Keep the two rules separate in the predicate. For date filters in real reporting systems, explicitly choose whether the ending boundary is included and use timestamp-safe half-open intervals when appropriate.
Keep range boundaries in typed parameters and document whether they are inclusive. For temporal reporting, a next-period-exclusive upper bound avoids guessing a final clock value. For exclusion lists from nullable relationships, review NOT IN specifically rather than assuming a positive IN example establishes the safety of its negated form.
SQL IN, BETWEEN, ANY and ALL: Comparison Reference FAQ
Are BETWEEN endpoints included?
Yes. For ordinary BETWEEN, both boundary comparisons are inclusive, so amount BETWEEN 10 AND 20 is the same as amount >= 10 AND amount <= 20. With timestamps, BETWEEN '2026-01-01' AND '2026-01-31' misses times later on 31 January, so use a half-open range instead.
What does greater than ALL of an empty set mean?
It is true, because no returned value violates the comparison. That surprises people: amount > ALL (SELECT amount FROM sales WHERE status = 'refunded') accepts every row when there are no refunds. Check that the subquery returns rows when an empty set should mean something else.
Does IN automatically duplicate matches?
No. It decides whether each input row passes a predicate, so a row appears once even if its value matches several list items or subquery rows. That's different from a join, which produces one output row per match.
Is there a limit on how many values IN can list?
Databases allow long lists, but there are practical limits: Oracle allows 1,000 literal values per list, and very long lists slow down parsing. For hundreds or thousands of values, load them into a temporary table and join, or pass an array where the database supports it, such as = ANY(?) with an array in PostgreSQL.
Does BETWEEN work with text values?
Yes, following the column's sort order: name BETWEEN 'A' AND 'C' includes names starting with A and B and the exact value C, but not Cara, because Cara sorts after C. Use name >= 'A' AND name < 'D' to include everything starting with A to C.
SQL IN, BETWEEN, ANY and ALL: Comparison Reference Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which sale ids have an amount IN (5,30)?
SQL IN, BETWEEN, ANY and ALL: Comparison Reference Quiz
2 questions to check what you learned.
Does amount BETWEEN 10 AND 20 include twenty?
- No; only the lower endpoint is inclusive
- Yes, both endpoints are included
- No; both endpoints are excluded
Which database does not implement ANY/ALL subquery comparison syntax?
- PostgreSQL
- SQL Server
- SQLite