EXISTS & NOT EXISTS
In this page:
SELECT columns FROM outer_table AS o
WHERE [NOT] EXISTS (
SELECT 1 FROM inner_table AS i
WHERE i.column = o.column
);Introduction to EXISTS
The EXISTS operator tests if a subquery returns any rows for the current outer row being evaluated. If the subquery finds a match, it returns TRUE. Otherwise, it returns FALSE, without ever looking at the actual row contents.
Example: Introduction to EXISTS
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);How EXISTS Evaluates
EXISTS is highly efficient because of how it short-circuits. It stops searching as soon as it finds the very first matching row in the subquery. It does not look for more matches, unlike COUNT() which would scan everything.
Example: How EXISTS Evaluates
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 1);
-- Stops at the first matching order, doesn't scan all three
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Using NOT EXISTS
NOT EXISTS is the opposite of EXISTS, checking for absence instead of presence. It returns TRUE only if the subquery returns zero rows. It is great for finding missing items, like customers who have never placed an order.
Example: Using NOT EXISTS
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);EXISTS vs IN
IN searches through a list of values returned by the subquery, while EXISTS checks for the mere presence of matching rows. EXISTS is usually faster with complex correlated datasets, since it can stop at the first match instead of building the whole list first.
Example: EXISTS vs IN
CREATE TABLE customers (id INT, region TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'North'), (2, 'South');
INSERT INTO orders VALUES (101, 1);
-- IN: builds the whole list first
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'North');
-- EXISTS: stops at the first match
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.region = 'North');EXISTS with Subquery Select Values
The actual columns in the inner SELECT list do not matter for EXISTS, since it only cares whether any row comes back. You can write SELECT 1, SELECT *, or SELECT NULL. The result remains the same either way.
Example: EXISTS with Subquery Select Values
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
SELECT * FROM customers c WHERE EXISTS (SELECT * FROM orders o WHERE o.customer_id = c.id);
SELECT * FROM customers c WHERE EXISTS (SELECT NULL FROM orders o WHERE o.customer_id = c.id);- Using
SELECT *thinking the columns matter inEXISTS, when only whether rows exist is tested. - Forgetting the correlation condition inside
EXISTS, so it is true for every row when any row exists. - Using
NOT INinstead ofNOT EXISTSwithNULLvalues, which returns no rows.
Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: