SQL-Engineering-Handbook

IN, ANY, & ALL: Quantified Comparison Operators & Set Algebra

Quantified comparison operators (= ANY, > ANY, < ALL, != ALL) extend scalar comparison logic across multi-row subquery result sets. In ANSI SQL standard relational algebra, IN is syntactically equivalent to = ANY, while NOT IN is equivalent to <> ALL. Understanding the formal mathematical equivalence and 3-valued truth table evaluations of these operators is essential for query optimization and bug prevention.

Learning Objectives

Formalize the mathematical equivalence between IN, = ANY, NOT IN, and <> ALL.
Evaluate 3-valued logic truth matrices for ANY and ALL under NULL conditions.
Contrast query optimizer AST rewrites for ANY vs aggregate min/max operations.
Architect high-performance set filtering on multi-million row datasets.
Implement safe quantified queries across distributed relational database systems.

Business Context

Quantified operators provide clean abstractions for multi-level threshold filtering:

Retail Risk Management: Flagging stores whose monthly shrinkage rate is higher than any store in a benchmark comparison region (> ANY).
SaaS Customer Success: Identifying enterprise accounts whose aggregate API usage exceeds all standard pricing tier caps (> ALL).
Human Resources: Selecting departments where every employee’s salary is above a baseline threshold (MIN(salary) > X).

Concept

Let $S = { s_1, s_2, \dots, s_k }$ be a set returned by a subquery.

1. The `ANY` Operator (Existential Quantification $\exists$)

$v \;\text{op}\; \text{ANY} (S) \iff \exists s \in S : (v \;\text{op}\; s)$

v = ANY (S) $\equiv v \in S$ (IN)
v > ANY (S) $\equiv v > \min(S)$

2. The `ALL` Operator (Universal Quantification $\forall$)

$v \;\text{op}\; \text{ALL} (S) \iff \forall s \in S : (v \;\text{op}\; s)$

v <> ALL (S) $\equiv v \notin S$ (NOT IN)
v > ALL (S) $\equiv v > \max(S)$

Syntax

-- Quantified ANY Comparison
SELECT emp_name, hire_date
FROM employes
WHERE hire_date > ANY (
    SELECT hire_date FROM employes WHERE dept_id = 1
);

-- Quantified ALL Comparison
SELECT emp_name, hire_date
FROM employes
WHERE hire_date > ALL (
    SELECT hire_date FROM employes WHERE dept_id = 1
);

Mental Model

Truth matrix evaluation under 3-valued logic:

Expression	Condition	Result
`10 > ANY (5, 8, 12)`	$10 > 5$ is `TRUE`	`TRUE`
`10 > ANY (15, 20, NULL)`	All false, but 1 `NULL`	`UNKNOWN`
`10 > ALL (5, 8, 4)`	$10 > 5, 8, 4$ are all `TRUE`	`TRUE`
`10 > ALL (5, 12, 4)`	$10 > 12$ is `FALSE`	`FALSE`
`10 > ALL (5, 8, NULL)`	No `FALSE`, but 1 `NULL`	`UNKNOWN`

Execution Order

Subquery Set Materialization: Inner query runs and creates an ordered array or hash table of values $S$.
Short-Circuit Scan:
- For ANY: Engine scans $S$. Stops on first TRUE comparison.
- For ALL: Engine scans $S$. Stops on first FALSE comparison.
Truth Fallback: If no terminating condition is met and $S$ contains NULL, returns UNKNOWN.

Optimizer Behaviour

PostgreSQL converts ANY array expressions into SubPlan or ScalarArrayOpExpr nodes. If the inner subquery is uncorrelated, the engine rewrites:

v > ANY (SELECT col FROM T) $\longrightarrow$ v > (SELECT MIN(col) FROM T)
v > ALL (SELECT col FROM T) $\longrightarrow$ v > (SELECT MAX(col) FROM T)

This rewrite unlocks Index Min/Max Optimization ($\mathcal{O}(\log N)$ B-Tree lookup).

Execution Plan Discussion

Annotated PostgreSQL plan showing Min/Max optimization for quantified rewrite:

Result  (cost=1.15..1.16 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.15..1.15 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)
          ->  Index Scan Backward using idx_emp_hire_date on employes  (cost=0.15..12.50 rows=10 width=4)
                Index Cond: (hire_date IS NOT NULL)

Cross Database Notes

Engine	`= ANY` Optimization	`> ANY` Rewrite	`NOT IN` Safety
PostgreSQL 16+	Native `ANY(array)` operator.	Replaces with `MIN()` scan.	Strict 3-valued logic.
MySQL 8.0+	Converts to `IN` subquery.	Rewrites to subquery aggregate.	Strict 3-valued logic.
SQL Server 2022	Transformed to Semi Join.	Transformed to scalar aggregate.	Transformed to Anti Semi Join.
Oracle 23c	Transformed to `IN` list / View.	Replaced with `MIN`/`MAX` node.	Strict 3-valued logic.

Common Mistakes

1. Using `!= ANY` expecting “Not Equal To Any”

Writing col != ANY (1, 2) means “Is col different from at least one element?”. Since 1 != 2, every number is different from at least one of (1, 2)! This predicate evaluates to TRUE for all numbers!

-- ❌ BUG: Evaluates to TRUE for ALL rows!
SELECT * FROM employes WHERE dept_id != ANY (ARRAY[1, 2]);

-- ✅ CORRECT: Use NOT IN or <> ALL to exclude a set
SELECT * FROM employes WHERE dept_id <> ALL (ARRAY[1, 2]);

Performance Notes

Converting > ANY or > ALL into explicit MIN() / MAX() scalar subqueries inside SQL application code allows database query planners to leverage index boundary scans, completely bypassing subquery materialization.

Production Notes

Array Parameters: In PostgreSQL application microservices, use WHERE col = ANY($1::int[]) to pass dynamic array lists safely without string concatenation vulnerabilities.

Real Company Example

Logistics: Delivery SLA Violation Detection

FedEx/DHL identifies transit hubs whose average package processing time exceeds all benchmark target SLAs:

SELECT 
    h.hub_id,
    h.hub_name,
    h.avg_processing_hours
FROM transit_hubs h
WHERE h.avg_processing_hours > ALL (
    SELECT s.target_sla_hours
    FROM sla_benchmarks s
    WHERE s.service_tier = 'EXPRESS_OVERNIGHT'
);

Engineering Notes

Universal quantification (ALL) over an empty set ($S = \emptyset$) evaluates to TRUE for all outer rows ($v \;\text{op}\; \text{ALL} (\emptyset) \equiv \text{TRUE}$). Conversely, existential quantification (ANY) over an empty set evaluates to FALSE ($v \;\text{op}\; \text{ANY} (\emptyset) \equiv \text{FALSE}$).

Interview Questions

Q1: What is the difference between `= ANY` and `IN` in SQL?

Answer: There is zero functional or performance difference. ANSI SQL defines IN as a syntactic alias for = ANY. Both evaluate set membership and compile into identical physical execution plans (Hash Semi Joins).

Summary

Expression	Equivalent Aggregate	Empty Set Outcome	NULL in Set Outcome
`x > ANY (S)`	`x > MIN(S)`	`FALSE`	`UNKNOWN` (if no true match)
`x > ALL (S)`	`x > MAX(S)`	`TRUE`	`UNKNOWN` (if no false match)
`x = ANY (S)`	`x IN (S)`	`FALSE`	`UNKNOWN` (if no match)
`x <> ALL (S)`	`x NOT IN (S)`	`TRUE`	`UNKNOWN` (Fails Query!)