SUBQUERY

Query 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

Filter by Database
SQL SUBQUERY Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all All subquery types supported. Optimization historically weak in older versions — greatly improved in 8.0 with hash joins and derived condition pushdown.
PostgreSQL Native all Excellent support for all subquery types. Also supports LATERAL subqueries and efficient EXISTS/NOT EXISTS.
SQL Server Native all Full support for scalar, correlated, and derived-table subqueries.
Oracle Native all Full support. Scalar subqueries can appear in SELECT lists and many other expression contexts.
SQLite Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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

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

Oracle Native syntax

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 Native syntax

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

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