ANY and ALL Operators in SQL with Examples

IN checks for exact membership within a subquery's result list, but sometimes you need a looser or stricter comparison — greater than at least one value, or greater than every value. ANY and ALL extend familiar comparison operators like > and < to work meaningfully against multi-row subquery results.

Key Definitions

• ANY: Modifies a comparison operator to succeed if the condition holds true for at least one value in the subquery's result set.
• ALL: Modifies a comparison operator to succeed only if the condition holds true for every value in the subquery's result set.

What You'll Learn

• Define ANY and ALL and how they modify standard comparison operators.
• Write queries using > ANY, > ALL, < ANY, and < ALL.
• Understand how ANY relates to a looser, 'at least one' comparison.
• Understand how ALL relates to a stricter, 'every value' comparison.

Detailed Explanation

`salary > ANY (SELECT salary FROM doctors WHERE department_id = 1)` means: is this salary greater than at least one salary within department 1? If department 1 contains salaries [95000, 88000], then any salary above 88000 (the lower of the two) satisfies this condition, since it only needs to beat one value, not both.

`salary > ALL (SELECT salary FROM doctors WHERE department_id = 1)` means something stricter: is this salary greater than every salary within department 1? Now the value must exceed 95000, the higher of the two, to satisfy the condition for all values simultaneously.

A useful mental shortcut: `> ANY` behaves like being greater than the minimum of the list, while `> ALL` behaves like being greater than the maximum of the list — and the reverse pattern applies for `< ANY` (less than the maximum) and `< ALL` (less than the minimum). This equivalence with MIN/MAX is frequently tested, since many ANY/ALL queries can also be rewritten using a single-row subquery with MIN or MAX directly.

Visual Summary

A number line showing department 1's salaries as two dots: 88000 and 95000. A shaded region starting just above 88000 labeled '> ANY 88000 or 95000 — beats at least one', and a narrower shaded region starting just above 95000 labeled '> ALL 88000 and 95000 — beats both'.

Quick Reference

Expression	Equivalent To	Meaning
salary > ANY (list)	salary > MIN(list)	Greater than at least one value
salary > ALL (list)	salary > MAX(list)	Greater than every value
salary < ANY (list)	salary < MAX(list)	Less than at least one value
salary < ALL (list)	salary < MIN(list)	Less than every value

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- ANY: doctors earning more than at least one Cardiology (dept 1) doctor
SELECT doctor_name, salary
FROM doctors
WHERE salary > ANY (SELECT salary FROM doctors WHERE department_id = 1);

-- ALL: doctors earning more than every Cardiology (dept 1) doctor
SELECT doctor_name, salary
FROM doctors
WHERE salary > ALL (SELECT salary FROM doctors WHERE department_id = 1);

Department 1 (Cardiology) contains Dr. Verma (95000) and Dr. Khan (88000). The ANY query returns any doctor earning more than 88000, the lower of the two, which includes Dr. Verma himself (95000, beats Dr. Khan) and Dr. Sen (120000). The ALL query is stricter, requiring a salary above 95000, the higher of the two, which only Dr. Sen (120000) satisfies.

Real-World Examples

• Compensation analysis tools use ANY/ALL-style comparisons to flag employees earning more than every peer in a reference group, or at least one peer.
• Real estate platforms use similar range comparisons to find properties priced above every comparable listing in a neighborhood, or above at least one.
• Academic systems use ANY/ALL-style logic to find students who scored higher than every classmate, or higher than at least one classmate, in a subject.

Common Mistakes to Avoid

• Confusing ANY (a looser, 'at least one' condition) with ALL (a stricter, 'every value' condition).
• Assuming ANY means 'matches any single specific value' the way IN does, rather than its actual looser comparison behavior.
• Not recognizing that ANY/ALL expressions can often be simplified to a single-row subquery using MIN or MAX directly.

Interview Questions

Q1. What does the ANY operator do in SQL?

ANY modifies a comparison operator so the condition succeeds if it holds true for at least one value returned by the subquery, effectively behaving like a comparison against the minimum (for >) or maximum (for <) of that list.

Q2. What does the ALL operator do in SQL?

ALL modifies a comparison operator so the condition must hold true for every value returned by the subquery, effectively behaving like a comparison against the maximum (for >) or minimum (for <) of that list.

Q3. How does `salary > ALL (subquery)` relate to using MAX?

`salary > ALL (subquery)` is functionally equivalent to `salary > (SELECT MAX(...) FROM subquery)`, since exceeding every value in a list is the same as exceeding the largest value in that list.

Practice MCQs

1. What is `salary > ANY (list)` equivalent to?

1. salary > MAX(list)
2. salary > MIN(list)
3. salary = AVG(list)
4. salary < MIN(list)

Answer: B. salary > MIN(list)

Explanation: ANY only requires beating at least one value, which is equivalent to beating the smallest (minimum) value in the list.

2. What is `salary > ALL (list)` equivalent to?

1. salary > MIN(list)
2. salary > MAX(list)
3. salary = MAX(list)
4. salary < MAX(list)

Answer: B. salary > MAX(list)

Explanation: ALL requires beating every value in the list, which is equivalent to beating the largest (maximum) value.

Quick Revision Points

• > ANY behaves like > MIN(list); > ALL behaves like > MAX(list).
• < ANY behaves like < MAX(list); < ALL behaves like < MIN(list).
• This MIN/MAX equivalence is a frequently tested interview and exam shortcut.

Conclusion

• ANY loosens a comparison to succeed against at least one value in a subquery result.
• ALL tightens a comparison to require success against every value in a subquery result.
• Both can often be rewritten more simply using MIN or MAX in a single-row subquery.

ANY and ALL extend standard comparison operators to work meaningfully against multi-row subquery results. ANY succeeds if the condition holds for at least one value in the list — equivalent to comparing against the list's minimum (for >) or maximum (for <) — while ALL requires the condition to hold for every value, equivalent to comparing against the maximum (for >) or minimum (for <). Recognizing this equivalence with MIN and MAX makes ANY/ALL expressions much easier to reason about and often reveals a simpler, single-row subquery alternative.