SQL Subqueries Quick Guide

Reference for scalar, table, and correlated subqueries.

Scalar Subquery

Syntax:

(SELECT single_value FROM table WHERE condition)

Returns a single value. Can be used anywhere an expression is allowed.

Example:

SELECT name, salary, (SELECT AVG(salary) FROM employees) as avg_salary FROM employees;

💡 Note: Must return exactly one row and one column

Subquery in WHERE

Syntax:

WHERE column IN (SELECT column FROM table)

Filters based on subquery results.

Example:

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

💡 Note: Can use IN, NOT IN, =, >, <, etc.

Correlated Subquery

Syntax:

(SELECT ... FROM table2 WHERE table2.col = table1.col)

References columns from outer query. Executes once per outer row.

Example:

SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

💡 Note: Can be slower than JOINs

EXISTS

Syntax:

WHERE EXISTS (SELECT 1 FROM table WHERE condition)

Returns true if subquery returns any rows.

Example:

SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id);

💡 Note: Often faster than IN for existence checks

Subquery in FROM

Syntax:

SELECT * FROM (SELECT ... FROM table) subquery_alias

Treats subquery result as a table (derived table).

Example:

SELECT * FROM (SELECT dept, AVG(salary) as avg_sal FROM employees GROUP BY dept) sub WHERE avg_sal > 50000;

💡 Note: Alias is required for derived tables

ALL / ANY / SOME

Syntax:

WHERE col > ALL (SELECT ...) | WHERE col > ANY (SELECT ...)

ALL: true if comparison is true for all subquery values. ANY/SOME: true if any.

Example:

SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Budget');

💡 Note: ALL = and with all values, ANY = or with all values

Quick Reference Table

| Function | Purpose | |----------|---------| | Scalar Subquery | Returns a single value | | Subquery in WHERE | Filters based on subquery results | | Correlated Subquery | References columns from outer query | | EXISTS | Returns true if subquery returns any rows | | Subquery in FROM | Treats subquery result as a table (derived table) | | ALL / ANY / SOME | ALL: true if comparison is true for all subquery values |

Related Cheat Sheets

Continue learning with more SQL references: