SUBQUERYQuery Techniques
Buy Me A Coffee

A SELECT statement nested inside another query, used in WHERE, FROM, SELECT, or HAVING clauses.

A query nested inside another query, useful for filtering, computed columns, or derived tables. Correlated subqueries (that reference outer rows) can be slow; always check if a JOIN or window function can replace them for better performance.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all All subquery types supported. Optimization historically weak in older versions — greatly improved in 8.0 with hash joins and derived condition pushdown.
PostgreSQL ✓ Supported all Excellent support for all subquery types. Also supports LATERAL subqueries and efficient EXISTS/NOT EXISTS.
SQL Server ✓ Supported all Full support for scalar, correlated, and derived-table subqueries.
Oracle ✓ Supported all Full support. Scalar subqueries can appear in SELECT lists and many other expression contexts.
SQLite ✓ Supported all Full support for correlated subqueries, EXISTS, and derived tables.

Details

Subqueries can often be rewritten as JOINs or CTEs for readability or performance. MySQL pre-8.0 had known optimizer issues with IN subqueries that were fixed in 8.0.

Standard Syntax

-- Scalar subquery in WHERE: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -- Correlated subquery: SELECT name FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id );

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

All subquery types supported. Optimization historically weak in older versions — greatly improved in 8.0 with hash joins and derived condition pushdown.

SELECT * FROM t WHERE id IN (SELECT id FROM t2 WHERE active = 1);

PostgreSQL

Excellent support for all subquery types. Also supports LATERAL subqueries and efficient EXISTS/NOT EXISTS.

SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.ref = t.id);

SQL Server

Full support for scalar, correlated, and derived-table subqueries.

SELECT * FROM t WHERE id IN (SELECT id FROM t2 WHERE active = 1);

Oracle

Full support. Scalar subqueries can appear in SELECT lists and many other expression contexts.

SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.cust_id = c.id) AS cnt FROM customers c;

SQLite

Full support for correlated subqueries, EXISTS, and derived tables.

SELECT * FROM t WHERE id IN (SELECT id FROM t2 WHERE active = 1);