ANY Operator Variants

The ANY operator supports three comparison patterns:

Equality with ANY

Functionally equivalent to IN operator:

SELECT * FROM employees
WHERE salary = ANY (
    SELECT salary 
    FROM employees 
    WHERE position = 'MANAGER'
);

Greater Than ANY

Returns records exceeding the minimum value from subquery results:

SELECT * FROM employees
WHERE salary > ANY (
    SELECT salary 
    FROM employees 
    WHERE position = 'MANAGER'
);

With subquery returning values 2975, 2850, and 2450, this retrieves all records where salary exceeds 2450.

Less Than ANY

Returns records below the maximum value from subquery results:

SELECT * FROM employees
WHERE salary < ANY (
    SELECT salary 
    FROM employees 
    WHERE position = 'MANAGER'
);

This retrieves all records where salary is less than 2975.

ALL Operator Patterns

Greater Than ALL

Returns records exceeding the maximum value from subquery results:

SELECT * FROM employees
WHERE salary > ALL (
    SELECT salary 
    FROM employees 
    WHERE position = 'MANAGER'
);

Retrieves records where salary surpasses 2975.

Less Than ALL

Return records below the minimum value from subquery results:

SELECT * FROM employees
WHERE salary < ALL (
    SELECT salary 
    FROM employees 
    WHERE position = 'MANAGER'
);

Retrieves records where salary is less then 2450.

EXISTS Predicate

Checks for existence of rows in subquery:

SELECT * FROM employees
WHERE EXISTS (
    SELECT 1 
    FROM employees 
    WHERE employee_id = 7839
);

Alternative form demonstrating EXISTS behavior:

SELECT * FROM employees
WHERE EXISTS (
    SELECT 'x' 
    FROM DUAL 
    WHERE 1 = 1
);

EXISTS evaluates to true when subquery returns at least one row, regardless of actual data content.

NOT EXISTS Pattern

Negates the EXISTS condition:

SELECT * FROM employees
WHERE NOT EXISTS (
    SELECT 'x' 
    FROM DUAL 
    WHERE 1 = 2
);

Since the subquery codnition is false and returns no rows, NOT EXISTS evaluates to true.