EXISTSQuery Techniques
Buy Me A Coffee

Test whether a subquery returns any rows. Returns TRUE if the subquery produces at least one row, FALSE otherwise. Short-circuits on the first match.

Tests whether a subquery returns any rows, returning TRUE if at least one row exists and FALSE otherwise. More efficient than COUNT(*) > 0 since it stops at the first row. Essential for filtering based on the existence of related rows in another table.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all EXISTS and NOT EXISTS fully supported. The subquery's SELECT list is irrelevant — SELECT 1, SELECT *, SELECT NULL all perform the same. Optimizer may convert to a join internally.
PostgreSQL ✓ Supported all EXISTS and NOT EXISTS fully supported. Often faster than IN with large subquery result sets because it short-circuits. The planner typically converts to a semi-join or anti-join.
SQL Server ✓ Supported all EXISTS and NOT EXISTS fully supported. Prefer EXISTS over IN when the subquery can return NULLs — NOT IN with NULLs returns no rows (NULL != NULL), while NOT EXISTS handles NULLs correctly.
Oracle ✓ Supported all EXISTS and NOT EXISTS fully supported. Oracle's optimizer is good at converting EXISTS to efficient join plans.
SQLite ✓ Supported all EXISTS and NOT EXISTS fully supported.

Details

The NULL trap with NOT IN is the critical gotcha: NOT IN returns zero rows if the subquery result contains any NULL, because SQL can't prove that the value is not equal to NULL. NOT EXISTS handles NULLs correctly and is generally the safer choice. EXISTS short-circuits at the first matching row, making it efficient even for large subqueries.

Standard Syntax

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

Version Support

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

Per-Database Syntax & Notes

MySQL

EXISTS and NOT EXISTS fully supported. The subquery's SELECT list is irrelevant — SELECT 1, SELECT *, SELECT NULL all perform the same. Optimizer may convert to a join internally.

SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id ); -- NOT EXISTS: SELECT name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

PostgreSQL

EXISTS and NOT EXISTS fully supported. Often faster than IN with large subquery result sets because it short-circuits. The planner typically converts to a semi-join or anti-join.

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

SQL Server

EXISTS and NOT EXISTS fully supported. Prefer EXISTS over IN when the subquery can return NULLs — NOT IN with NULLs returns no rows (NULL != NULL), while NOT EXISTS handles NULLs correctly.

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

Oracle

EXISTS and NOT EXISTS fully supported. Oracle's optimizer is good at converting EXISTS to efficient join plans.

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

SQLite

EXISTS and NOT EXISTS fully supported.

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