-- Basic correlated subquery pattern

SELECT e1.name, e1.salary

FROM employees e1

WHERE e1.salary > (

SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department = e1.department -- References outer query

);

-- EXISTS pattern (most efficient)

SELECT c.name

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

);

References outer query

Executes per outer row

Can use in SELECT, WHERE, HAVING

Good optimizer

Standard

Good optimizer

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.