SQL ANY, ALL

Learn how the SQL ANY and ALL operators are used with subqueries to compare a value with multiple values.

What are ANY and ALL?

The ANY and ALL operators are used with subqueries to compare a value against a set of values. ANY returns true when the comparison is true for at least one value, while ALL requires the comparison to be true for every value returned by the subquery.

Important: SQLite does not directly support the ANY and ALL operators. Therefore, the executable examples below use SQLite-compatible equivalents such as EXISTS, NOT EXISTS, MAX(), and MIN() to produce the same logic.

Basic ANY Concept

Conceptually, ANY means that a comparison must be true for at least one value returned by a subquery. For example, “greater than ANY” means greater than at least one value.

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS special_prices;

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price INTEGER
);

CREATE TABLE special_prices (
  price INTEGER
);

INSERT INTO products VALUES
  (1, 'Keyboard', 800),
  (2, 'Monitor', 2500),
  (3, 'Headphones', 1200),
  (4, 'Webcam', 1800);

INSERT INTO special_prices VALUES
  (1000),
  (1500),
  (2000);

SELECT product_name, price
FROM products
WHERE EXISTS (
  SELECT 1
  FROM special_prices
  WHERE products.price > special_prices.price
)
ORDER BY price;

ANY with Greater Than

The idea behind > ANY is that the value must be greater than at least one value returned by the subquery. In SQLite, this can be represented using EXISTS.

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS salary_targets;

CREATE TABLE employees (
  employee_id INTEGER,
  employee_name TEXT,
  salary INTEGER
);

CREATE TABLE salary_targets (
  salary INTEGER
);

INSERT INTO employees VALUES
  (1, 'Aarav', 45000),
  (2, 'Meera', 62000),
  (3, 'Kabir', 75000),
  (4, 'Riya', 90000);

INSERT INTO salary_targets VALUES
  (50000),
  (70000),
  (85000);

SELECT employee_name, salary
FROM employees
WHERE EXISTS (
  SELECT 1
  FROM salary_targets
  WHERE employees.salary > salary_targets.salary
)
ORDER BY salary;

ANY with Less Than

Similarly, < ANY means that a value is less than at least one value in the subquery.

DROP TABLE IF EXISTS cars;
DROP TABLE IF EXISTS price_limits;

CREATE TABLE cars (
  car_id INTEGER,
  model TEXT,
  price INTEGER
);

CREATE TABLE price_limits (
  price INTEGER
);

INSERT INTO cars VALUES
  (1, 'Model A', 18000),
  (2, 'Model B', 25000),
  (3, 'Model C', 32000),
  (4, 'Model D', 45000);

INSERT INTO price_limits VALUES
  (20000),
  (30000),
  (40000);

SELECT model, price
FROM cars
WHERE EXISTS (
  SELECT 1
  FROM price_limits
  WHERE cars.price < price_limits.price
)
ORDER BY price;

Basic ALL Concept

ALL requires the comparison to be true for every value returned by the subquery. For example, "greater than ALL" means the value must be greater than every value in the subquery.

In SQLite, the equivalent can be written using MAX(). A value greater than every value in a set must be greater than the maximum value in that set.

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS salary_targets;

CREATE TABLE employees (
  employee_id INTEGER,
  employee_name TEXT,
  salary INTEGER
);

CREATE TABLE salary_targets (
  salary INTEGER
);

INSERT INTO employees VALUES
  (1, 'Nisha', 55000),
  (2, 'Rohan', 72000),
  (3, 'Tara', 95000),
  (4, 'Dev', 110000);

INSERT INTO salary_targets VALUES
  (60000),
  (70000),
  (80000);

SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT MAX(salary)
  FROM salary_targets
)
ORDER BY salary;

ALL with Less Than

The concept of < ALL means that a value must be less than every value returned by the subquery. The SQLite equivalent uses MIN().

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS minimum_prices;

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price INTEGER
);

CREATE TABLE minimum_prices (
  price INTEGER
);

INSERT INTO products VALUES
  (1, 'Tablet A', 9000),
  (2, 'Tablet B', 14000),
  (3, 'Tablet C', 18000),
  (4, 'Tablet D', 25000);

INSERT INTO minimum_prices VALUES
  (15000),
  (20000),
  (25000);

SELECT product_name, price
FROM products
WHERE price < (
  SELECT MIN(price)
  FROM minimum_prices
)
ORDER BY price;

ANY vs ALL

Operator	Meaning	SQLite Equivalent
> ANY	Greater than at least one value	EXISTS with >
< ANY	Less than at least one value	EXISTS with <
> ALL	Greater than every value	> MAX()
< ALL	Less than every value	< MIN()

ANY and ALL with Equal Values

ANY and ALL can also be used conceptually with equality comparisons. The SQLite examples below use EXISTS and NOT EXISTS so they can run directly in the browser's SQLite engine.

DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS top_scores;

CREATE TABLE students (
  student_id INTEGER,
  student_name TEXT,
  score INTEGER
);

CREATE TABLE top_scores (
  score INTEGER
);

INSERT INTO students VALUES
  (1, 'Aman', 78),
  (2, 'Priya', 91),
  (3, 'Rahul', 85),
  (4, 'Simran', 96);

INSERT INTO top_scores VALUES
  (85),
  (91),
  (96);

SELECT student_name, score
FROM students
WHERE EXISTS (
  SELECT 1
  FROM top_scores
  WHERE students.score = top_scores.score
)
ORDER BY score DESC;

ALL Using NOT EXISTS

Another SQLite-compatible way to express > ALL is to use NOT EXISTS. The query checks that there is no value in the subquery that is greater than or equal to the current value.

DROP TABLE IF EXISTS players;
DROP TABLE IF EXISTS required_scores;

CREATE TABLE players (
  player_id INTEGER,
  player_name TEXT,
  score INTEGER
);

CREATE TABLE required_scores (
  score INTEGER
);

INSERT INTO players VALUES
  (1, 'Arjun', 70),
  (2, 'Kiran', 82),
  (3, 'Vivek', 95),
  (4, 'Sahil', 110);

INSERT INTO required_scores VALUES
  (80),
  (90),
  (100);

SELECT player_name, score
FROM players AS p
WHERE NOT EXISTS (
  SELECT 1
  FROM required_scores AS r
  WHERE r.score >= p.score
)
ORDER BY score;

ANY and ALL with Aggregates

Aggregate functions make it easy to reproduce many ANY and ALL comparisons in SQLite. MAX() is useful for "greater than every value", while MIN() is useful for "less than every value".

DROP TABLE IF EXISTS candidates;
DROP TABLE IF EXISTS benchmark_scores;

CREATE TABLE candidates (
  candidate_id INTEGER,
  candidate_name TEXT,
  score INTEGER
);

CREATE TABLE benchmark_scores (
  score INTEGER
);

INSERT INTO candidates VALUES
  (1, 'Neha', 72),
  (2, 'Ravi', 88),
  (3, 'Tanya', 94),
  (4, 'Mohan', 105);

INSERT INTO benchmark_scores VALUES
  (75),
  (85),
  (95);

SELECT candidate_name, score
FROM candidates
WHERE score > (
  SELECT MAX(score)
  FROM benchmark_scores
)
ORDER BY score DESC;

Common Uses of ANY and ALL

Compare a value against multiple values returned by a subquery.
Find values greater than at least one value in another result set.
Find values greater than every value in another result set.
Compare prices, salaries, scores, quantities, or other numeric values.
Use EXISTS, NOT EXISTS, MAX(), and MIN() when working with SQLite.

Best Practices

Remember that SQLite does not directly support the ANY and ALL operators.
Use EXISTS to reproduce many ANY comparisons in SQLite.
Use MAX() when expressing a "greater than ALL" comparison.
Use MIN() when expressing a "less than ALL" comparison.
Use NOT EXISTS when it provides a clearer equivalent for ALL logic.
Use table aliases to make correlated subqueries easier to read.

The ANY and ALL operators compare a value with multiple values returned by a subquery. Although SQLite does not directly implement these operators, their logic can be reproduced using EXISTS, NOT EXISTS, MAX(), and MIN().

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click "Run Code" to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click "Run Code" to see the result here.