Subqueries

WHERE clauses that combine subqueries with OR

When a subquery and a non-subquery predicate are joined by OR in a WHERE clause, the APPLY operator is used. Replace OR with AND if the query logic allows it.

-- Efficient
SELECT * FROM tb1 WHERE id IN (SELECT id FROM tb2)
SELECT * FROM tb1 WHERE id IN (SELECT id FROM tb2) AND id > 3

-- Inefficient (APPLY operator used)
SELECT * FROM tb1 WHERE id IN (SELECT id FROM tb2) OR id > 3

Correlated subqueries with functions or non-equality operators

Correlated subqueries are efficient when correlated columns use equality (=). Using a function on either side of the correlation predicate, or using a non-equality operator (!=, >=, etc.), forces the APPLY operator.

-- Efficient
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name)

-- Inefficient (function on correlated column)
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE UPPER(a.name) = b.name)

-- Inefficient (function on correlated column)
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.decimal_test = ABS(b.decimal_test))

-- Inefficient (non-equality operator)
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name != b.name)

-- Inefficient (non-equality operator)
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name >= b.name)

Correlated subqueries with OR between correlated and non-correlated conditions

When a correlated predicate and a non-correlated predicate inside a subquery are joined by OR, the APPLY operator is used. Use AND instead.

-- Efficient
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name AND b.date_test < '2015-12-02')

-- Inefficient (OR between correlated and non-correlated conditions)
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name OR b.date_test < '2015-12-02')

-- Inefficient (OR between two correlated conditions)
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name OR b.date_test = a.date_test)

Scalar subqueries with correlated items

A scalar subquery (one that returns a single value) that references an outer table column uses the APPLY operator. Restructure the query as a non-correlated scalar subquery when possible.

-- Efficient (no correlation)
SELECT * FROM tb1 a WHERE id >
  (SELECT id FROM tb2 b WHERE b.date_test < '2015-12-02')

-- Inefficient (correlated scalar subquery)
SELECT * FROM tb1 a WHERE id >
  (SELECT id FROM tb2 b WHERE a.name = b.name AND b.date_test < '2015-12-02')

Subqueries with cross-level correlation

In a nested subquery, each subquery should reference only the immediately enclosing query level. Referencing a column from a non-adjacent outer level (cross-level correlation) forces the APPLY operator.

-- Efficient: tb3's subquery references tb2 (adjacent level); tb2's subquery references tb1
SELECT * FROM tb1 a WHERE id IN (
  SELECT id FROM tb2 b WHERE a.name = b.name AND
    EXISTS (SELECT name FROM tb3 c WHERE b.address = c.address)
)

-- Inefficient: tb3's subquery references tb1 (skipping a level)
SELECT * FROM tb1 a WHERE id IN (
  SELECT id FROM tb2 b WHERE a.name = b.name AND
    EXISTS (SELECT name FROM tb3 c WHERE a.address = c.address)
)

In the examples above, tb1 (aliased a) and tb2 (aliased b) are at the same correlation level, as are tb2 and tb3. The cross-level correlation occurs between tb1 and tb3, which span two levels.

Correlated GROUP BY subqueries where grouping columns exclude the correlated item

When a correlated subquery contains GROUP BY, the grouping columns must include the correlated column. If they do not, the APPLY operator is used.

-- Efficient: correlated column b.pk matches the GROUP BY column pk
SELECT * FROM tb1 a WHERE EXISTS (
  SELECT pk FROM tb2 b
  WHERE a.pk = b.pk AND b.date_test = '2003-04-05'
  GROUP BY pk
)

-- Inefficient: correlated column b.date_test is not in GROUP BY pk
SELECT * FROM tb1 a WHERE EXISTS (
  SELECT pk FROM tb2 b
  WHERE a.date_test = b.date_test AND b.date_test = '2003-04-05'
  GROUP BY pk
)

Comparison subqueries

A comparison subquery uses a comparison operator to compare a value against the result of a subquery.

Syntax

non_subquery_operand comparison_operator (subquery)
-- comparison_operator: =  >  <  >=  <=  <>  !=  <=>  LIKE

Example

SELECT * FROM tb1 WHERE 'a' = (SELECT column1 FROM t1)

The subquery must appear to the right of the comparison operator.

Subqueries with ANY, ALL, IN/NOT IN, and EXISTS/NOT EXISTS

Syntax

operand comparison_operator ANY (subquery)
operand comparison_operator ALL (subquery)
operand IN (subquery)
operand NOT IN (subquery)
operand EXISTS (subquery)
operand NOT EXISTS (subquery)

-- comparison_operator: =  >  <  >=  <=  <>  !=

Operator behavior

Examples

IN and = ANY are interchangeable:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

NOT IN and <> ALL are interchangeable:

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

EXISTS returns TRUE as long as the subquery returns a row, even if all values in that row are NULL:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Row subqueries

A row subquery compares multiple columns at once against a single row returned by a subquery.

Supported comparison operators: = > < >= <= <> != <=>

Example

Both forms are equivalent:

SELECT * FROM t1 WHERE (col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

Rows from t1 are returned only when:

1. The subquery returns exactly one row. If multiple rows are returned, an error is reported.

2. The values of col3 and col4 returned by the subquery equal col1 and col2 in the primary table.

Correlated subqueries

A correlated subquery references a column from the outer query. The subquery is re-evaluated for each row of the outer query.

Example

SELECT * FROM t1
WHERE column1 = ANY (
  SELECT column1 FROM t2
  WHERE t2.column2 = t1.column2
);

In this example, the subquery references t1.column2 from the outer query. The subquery does not contain t1 directly — the reference is inherited from the outer context.

Derived tables

A derived table is a subquery used in a FROM clause. It acts as a temporary, in-memory table for the duration of the query.

Syntax

SELECT ... FROM (subquery) [AS] tbl_name ...

Rules

1. A derived table must have an alias (for example, AS sb).

2. A derived table can return a scalar, a column, a row, or a table.

3. A derived table cannot be a correlated subquery — it cannot reference columns from the outer query.

Examples

Filter results from a derived table:

-- Create the source table
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1, '1', 1.0);
INSERT INTO t1 VALUES (2, '2', 2.0);

-- Query returns: 2, '2', 4.0
SELECT sb1, sb2, sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3 * 2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

Use a derived table to apply aggregate functions over grouped results. Nesting aggregate functions directly (for example, AVG(SUM(...))) causes an error; wrap the inner aggregate in a derived table instead:

-- Returns an error
SELECT AVG(SUM(s1)) FROM t1 GROUP BY s1;

-- Use a derived table instead; returns: 1.5000
SELECT AVG(sum_s1)
FROM (SELECT SUM(s1) AS sum_s1 FROM t1 GROUP BY s1) AS t1;