> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference for ANY(subquery) and ALL(subquery) quantified comparison operators in Firebolt.

# ANY/ALL (subquery)

Quantified comparison operators compare a scalar value against the rows returned by a subquery using a `<comparison>` operator combined with an `ANY` or `ALL` quantifier. The subquery must return a single column.

* `ANY` returns `TRUE` if the comparison is true for at least one row returned by the subquery.
* `ALL` returns `TRUE` if the comparison is true for every row returned by the subquery.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
<value> <comparison> ANY(<subquery>)
<value> <comparison> ALL(<subquery>)
```

## Parameters

| Parameter      | Description                                                                                                               | Supported input types                                                             |
| :------------- | :------------------------------------------------------------------------------------------------------------------------ | :-------------------------------------------------------------------------------- |
| `<value>`      | A scalar value to compare against each row of the subquery result.                                                        | Any comparable type                                                               |
| `<comparison>` | A [comparison operator](/reference-sql/lexical-structure/operators#comparison): `=`, `<>`, `!=`, `<`, `>`, `<=`, or `>=`. | See [Comparison operators](/reference-sql/lexical-structure/operators#comparison) |
| `<subquery>`   | A parenthesized `SELECT` statement that returns exactly one column. The subquery may be correlated with the outer query.  | Any query that projects a single column of a comparable type                      |

The type of `<value>` and the column type returned by `<subquery>` must be comparable.

## Return type

`BOOLEAN`

## Relationship to `IN` and `NOT IN`

Quantified comparison subqueries generalize `IN` and `NOT IN`:

* `<value> = ANY(<subquery>)` is equivalent to `<value> IN (<subquery>)`.
* `<value> <> ALL(<subquery>)` is equivalent to `<value> NOT IN (<subquery>)`.

Use `ANY` and `ALL` when you need a comparison other than equality (for example, `<`, `>=`, or `<>`) against the rows of a subquery.

## NULL handling

The result follows SQL [three-valued logic](https://en.wikipedia.org/wiki/Three-valued_logic):

* If the subquery returns no rows, `ANY` returns `FALSE` and `ALL` returns `TRUE`.
* For `ANY`: returns `TRUE` as soon as a matching row is found, even if other rows are `NULL`. If no row matches and at least one row is `NULL` (or the comparison itself is `NULL`), the result is `NULL`. Otherwise the result is `FALSE`.
* For `ALL`: returns `FALSE` as soon as a non-matching row is found, even if other rows are `NULL`. If every row matches and at least one row is `NULL` (or the comparison itself is `NULL`), the result is `NULL`. Otherwise the result is `TRUE`.

## Examples

The examples below use the following tables.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE TABLE employees (
    employee_id INT,
    name TEXT,
    department_id INT,
    salary NUMERIC(8, 2)
);

INSERT INTO employees VALUES
    (100, 'Steven',  90, 24000.00),
    (101, 'Neena',   90, 17000.00),
    (102, 'Lex',     90, 17000.00),
    (103, 'Alex',    60,  9000.00),
    (104, 'Bruce',   60,  6000.00),
    (105, 'David',   60,  4800.00),
    (106, 'Valli',   60,  4800.00);

CREATE TABLE departments (
    department_id INT,
    department_name TEXT,
    location_id INT
);

INSERT INTO departments VALUES
    (60, 'IT',         1400),
    (90, 'Executive',  1700);
```

### Equality with `ANY` (equivalent to `IN`)

Find employees whose `department_id` matches any department located at `location_id = 1700`.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT name
FROM employees
WHERE department_id = ANY (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
```

**Returns**:

| name   |
| :----- |
| Steven |
| Neena  |
| Lex    |

### Non-equality with `ALL` (equivalent to `NOT IN`)

Find employees whose `department_id` does not match any department located at `location_id = 1700`.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT name
FROM employees
WHERE department_id <> ALL (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
```

**Returns**:

| name  |
| :---- |
| Alex  |
| Bruce |
| David |
| Valli |

### Range comparison with `ALL`

Find employees whose `salary` is greater than the highest salary in department `60`. Because `> ALL` is true only when the value exceeds every row, this is equivalent to comparing against the maximum.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 60
);
```

**Returns**:

| name   | salary   |
| :----- | :------- |
| Steven | 24000.00 |
| Neena  | 17000.00 |
| Lex    | 17000.00 |

### Range comparison with `ANY`

Find employees whose `salary` is greater than at least one salary in department `90`. Because `> ANY` is true as soon as the value exceeds a single row, this is equivalent to comparing against the minimum.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 90
);
```

**Returns**:

| name   | salary   |
| :----- | :------- |
| Steven | 24000.00 |

### Correlated subquery

The subquery can reference columns from the outer query. The following query finds, for each employee, whether their salary is greater than or equal to every other salary in the same department — that is, the top earner(s) per department.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT name, department_id, salary
FROM employees e
WHERE salary >= ALL (
    SELECT salary
    FROM employees
    WHERE department_id = e.department_id
)
ORDER BY department_id, name;
```

**Returns**:

| name   | department\_id | salary   |
| :----- | :------------- | :------- |
| Alex   | 60             | 9000.00  |
| Steven | 90             | 24000.00 |

### Empty subquery result

When the subquery returns no rows, `ANY` is `FALSE` and `ALL` is vacuously `TRUE`.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT 1 = ANY (SELECT department_id FROM departments WHERE location_id = 9999);
```

**Returns**: `FALSE`

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT 1 <> ALL (SELECT department_id FROM departments WHERE location_id = 9999);
```

**Returns**: `TRUE`

### `NULL` in the subquery result

If the subquery yields a `NULL`, the result follows three-valued logic. A definite match short-circuits the `NULL`:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT 1 = ANY (SELECT n FROM (VALUES (1), (NULL), (3)) v(n));
```

**Returns**: `TRUE` — the match on `1` is found before the `NULL` matters.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT 4 = ANY (SELECT n FROM (VALUES (1), (NULL), (3)) v(n));
```

**Returns**: `NULL` — no match is found, but a `NULL` prevents a definitive `FALSE`.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT 1 <> ALL (SELECT n FROM (VALUES (2), (NULL), (3)) v(n));
```

**Returns**: `NULL` — every non-`NULL` row differs from `1`, but the `NULL` prevents a definitive `TRUE`.

## See also

* [`ANY` / `ALL` (array)](/reference-sql/lexical-structure/any-all-array) — quantified comparison against the elements of an array.
* [Subquery operators](/reference-sql/lexical-structure/operators#subquery-operators) — `EXISTS`, `NOT EXISTS`, `IN`, `NOT IN`.