EXISTS
Checks if a subquery returns at least one row (true/false).
SELECT ... WHERE EXISTS (SELECT 1 FROM other WHERE other.fk = t.id);This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.
What it does
Overview
Checks if a subquery returns at least one row (true/false).
The EXISTS operator is a boolean operator used in a WHERE clause to test for the existence of records in a subquery. It returns TRUE if the subquery returns one or more rows, and FALSE otherwise. Technically, EXISTS is highly optimized for performance because it employs 'short-circuit' evaluation: the database engine stops processing the subquery the moment a single matching record is found, rather than scanning the entire dataset to build a result set. This makes it generally superior to the IN operator when the subquery filters on large tables with existing indexes. A key edge case is the handling of NULL values; unlike the IN operator, which can return UNKNOWN and cause logic errors when encountering NULLs in a subquery, EXISTS simply cares if a row is present. If the subquery returns a row containing only a NULL, EXISTS still evaluates to TRUE. When using date comparisons within these subqueries, it is recommended to use ISO 8601 strings (e.g., '2023-12-31') to ensure reliable ordering and cross-platform compatibility.
Quick reference
Syntax
SELECT ... WHERE EXISTS (SELECT 1 FROM other WHERE other.fk = t.id);Inputs
Parameters
See it in practice
Examples
Basic Relationship Check
SELECT name FROM suppliers s WHERE EXISTS (SELECT 1 FROM products p WHERE p.supplier_id = s.id AND p.price > 100);[{"name": "Global Tech"}, {"name": "Precision Parts"}]
Returns suppliers who provide at least one product priced over 100. The SELECT 1 is a convention indicating we only care about row existence, not column data.
Finding Orphaned Records with NOT EXISTS
SELECT title FROM books b WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.id);[{"title": "The Forgotten Manuscript"}]
Identifies books that have never been loaned out. This is often more efficient than a LEFT JOIN with a NULL check on large tables.
Correlated Subquery with Date Filtering
SELECT email FROM users u WHERE EXISTS (SELECT 1 FROM subscriptions s WHERE s.user_id = u.id AND s.end_date >= '2024-01-01');[{"email": "active_user@example.com"}]
Filters for users with active subscriptions starting from a specific ISO date string. This ensures reliable ordering across different SQL dialects.
Debug faster
Common Errors
PerformanceAntipattern
Cause: Using EXISTS when a simple JOIN would suffice on very small tables, or failing to index the join keys (foreign keys) used in the subquery correlation.
Fix: Ensure that the columns used in the subquery's WHERE clause (e.g., p.supplier_id = s.id) are indexed to allow the engine to find the first match instantly.
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.unindexed_col = t1.id);LogicError
Cause: Confusing NOT EXISTS with NOT IN when NULLs are present. If a subquery returns a NULL, NOT IN will fail to return any rows, whereas NOT EXISTS handles it predictably.
Fix: Use NOT EXISTS instead of NOT IN if the subquery column allows NULL values to avoid 'empty set' surprises.
SELECT * FROM orders WHERE id NOT IN (SELECT order_id FROM returns); -- Returns nothing if one order_id is NULLRuntime support
Compatibility
Source: MDN Web Docs
Common questions
Frequently Asked Questions
Checks if a subquery returns at least one row (true/false).
subquery: Subquery to test for at least one matching row.
PerformanceAntipattern: Ensure that the columns used in the subquery's WHERE clause (e.g., p.d = s.id) are indexed to allow the engine to find the first match instantly. LogicError: Use NOT EXISTS instead of NOT IN if the subquery column allows NULL values to avoid 'empty set' surprises.