SQL EXISTS Operator: Test Whether a Subquery Returns Rows

SQL Tutorial · Chapter 29 of 32

The SQL EXISTS operator tests whether a subquery returns at least one row. It gives TRUE as soon as a single match is found and FALSE if the subquery is empty, without caring what the subquery selects. EXISTS is almost always used as a correlated subquery, and NOT EXISTS is the safest way to find rows in one table that have no counterpart in another.

SQL EXISTS Syntax

SELECT columns FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.key = t1.key);

SELECT columns FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.key = t1.key);

The SELECT 1 is conventional: EXISTS only checks for rows, so the selected expression is irrelevant. SELECT * works equally well.

Sample Tables

Example 1: Customers Who Have Ordered

SELECT c.Name, c.Country
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

Each customer appears once, even Anita Sharma who has two orders. EXISTS never duplicates rows, unlike an INNER JOIN.

Example 2: NOT EXISTS for Customers with No Orders

SELECT c.Name, c.City
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

Example 3: Orphan Orders with NOT EXISTS

SELECT o.OrderID, o.CustomerID, o.Product
FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerID = o.CustomerID);

Example 4: EXISTS with Extra Conditions

Customers who have bought a Laptop. The subquery can contain any WHERE logic.

SELECT c.Name
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o
              WHERE o.CustomerID = c.CustomerID AND o.Product = 'Laptop');

Example 5: EXISTS in UPDATE and DELETE

-- flag customers who have ordered
UPDATE Customers c
SET Segment = 'Active'
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- remove orders with no customer
DELETE FROM Orders
WHERE NOT EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerID = Orders.CustomerID);

The UPDATE flags customers 1 to 4; the DELETE removes order 105. SQL Server writes the UPDATE as UPDATE Customers SET ... FROM Customers c WHERE EXISTS (...).

EXISTS vs IN vs JOIN

Use EXISTS to test presence, IN for short literal lists, and JOIN when you need the other table’s columns.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• EXISTS and NOT EXISTS behave identically in all four; they are part of the SQL standard.
• Modern optimizers in all four turn EXISTS into a semi-join, so it is at least as fast as IN with a subquery and usually faster than DISTINCT over a JOIN.
• The correlated UPDATE syntax differs by database; the EXISTS logic itself does not.

Common Mistakes

• Forgetting the correlation (o.CustomerID = c.CustomerID), which makes EXISTS true for every outer row as long as Orders is not empty.
• Using NOT IN instead of NOT EXISTS when the subquery column can be NULL, and getting an empty result.
• Expecting EXISTS to return the matched order’s columns; it only filters.
• Adding DISTINCT or LIMIT inside the EXISTS subquery; it changes nothing and wastes effort.

Practice Exercise

1. List customers who have placed an order of more than 200.
2. List products that have never been ordered by a customer from India.

-- 1
SELECT c.Name FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.Amount > 200);
-- Anita Sharma, Wei Chen

-- 2
SELECT DISTINCT o.Product FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Orders o2 JOIN Customers c ON c.CustomerID = o2.CustomerID
                  WHERE c.Country = 'India' AND o2.Product = o.Product);
-- Mouse, Keyboard, Headset

Related Chapters

• SQL Subqueries – the foundation EXISTS builds on
• SQL IN – the alternative and its NULL trap
• SQL LEFT JOIN – another way to find missing matches
• SQL from Scratch: full course overview

FAQ

What does EXISTS return in SQL?

TRUE if the subquery returns one or more rows, FALSE if it returns none. It never returns NULL, which makes NOT EXISTS reliable.

Is EXISTS faster than IN?

Often, because EXISTS stops at the first matching row and does not build a list. In modern databases the optimizer frequently produces the same plan for both, so the bigger difference is NULL handling.

Does it matter what I SELECT inside EXISTS?

No. SELECT 1, SELECT * and SELECT any_column all behave the same, because only the presence of rows is tested.

Chapter 29 of 32 · SQL from Scratch: all 32 chapters