EXISTS/NOT EXISTS
EXISTS tests whether a subquery returns any rows, which is a fast way to check for related data.
In this page:
Syntax
SELECT columns
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.key = t1.key);EXISTS/NOT EXISTS
EXISTS returns true as soon as the subquery finds one row, so it does not need to build a full result. NOT EXISTS finds rows with no related rows, and unlike NOT IN it handles NULLs correctly.
The selected columns inside EXISTS do not matter, so SELECT 1 is customary.
Note:
Prefer NOT EXISTS over NOT IN when the subquery can return NULLs.
Example: EXISTS/NOT EXISTS
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1,'Ada'),(2,'Bob'),(3,'Cy');
INSERT INTO orders VALUES (10,1),(11,1),(12,3);
SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY id;
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Output:
-- name
-- Ada
-- Cy
-- name
-- BobLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Using NOT IN with NULLs
- Forgetting the correlation condition
- Selecting expensive columns inside EXISTS
Chapter Summary
- EXISTS is true when rows exist
- NOT EXISTS finds unmatched rows
- Stops at the first match
- Safer than NOT IN with NULLs
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: