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 evaluates to TRUE as soon as a single matching row is found and FALSE if the subquery is empty, and it does not care what the subquery selects. EXISTS is almost always written as a correlated subquery, and its partner NOT EXISTS is the safest way to find rows in one table that have no counterpart in another.

This chapter of the NeoTech Navigators SQL series covers the syntax, how EXISTS is evaluated, nine worked examples on two small sample tables (with the exact result of each query), EXISTS vs IN vs JOIN, common mistakes and fixes, and a short practice exercise. Everything here works in MySQL, SQL Server, PostgreSQL and SQLite unless noted.

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);

How EXISTS Is Evaluated

1. The database takes one row from the outer table (for example one customer).
2. It runs the subquery with that row’s key plugged into the correlation condition.
3. If the subquery finds a row, EXISTS is TRUE and the outer row is kept. The database can stop looking at that point.
4. If no row is found, EXISTS is FALSE and the outer row is dropped (or kept, with NOT EXISTS).

In practice the optimizer usually rewrites this as a semi-join (or an anti-join for NOT EXISTS) rather than literally looping, but the result is the same.

Sample Tables

Note that order 105 belongs to CustomerID 6, which does not exist in Customers, and customer 5 (Emma Brown) has no orders. Both are deliberate.

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 outer 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);

This is a classic data-quality check: it finds child rows whose parent is missing.

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 with a Date Range

Customers who placed an order in February 2026:

SELECT c.Name
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o
              WHERE o.CustomerID = c.CustomerID
                AND o.OrderDate >= '2026-02-01'
                AND o.OrderDate <  '2026-03-01');

Order 105 is also in February, but its CustomerID 6 has no customer row, so nobody else is returned.

Example 6: Combining EXISTS and NOT EXISTS

Customers who bought a Laptop but never a Monitor, a typical cross-sell question:

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

Anita Sharma bought both, so the NOT EXISTS condition removes her.

Example 7: EXISTS Inside CASE (a Yes/No Flag)

SELECT c.Name,
       CASE WHEN EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)
            THEN 'Yes' ELSE 'No' END AS HasOrders
FROM Customers c;

Example 8: EXISTS in UPDATE and DELETE

-- flag customers who have ordered (assumes a Segment column exists)
UPDATE Customers
SET Segment = 'Active'
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = Customers.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. Referring to the outer table by its full name (Customers.CustomerID) keeps the statement portable. Run the matching SELECT first to preview the rows, as covered in the SQL UPDATE and SQL DELETE chapters.

Example 9: NOT EXISTS vs LEFT JOIN … IS NULL

Both of these return Emma Brown:

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

SELECT c.Name
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;

NOT EXISTS states the intent more directly and cannot accidentally produce duplicates, which is why many teams prefer it for anti-joins. The SQL LEFT JOIN chapter explains the second pattern in detail.

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.

The NOT IN NULL trap

Suppose Orders also contained a row whose CustomerID is NULL. Then this query returns no rows at all, because x NOT IN (..., NULL) is never TRUE:

SELECT Name FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

The NOT EXISTS version in Example 2 still returns Emma Brown, because the correlation condition simply never matches the NULL row. See SQL NULL values for why comparisons with NULL behave this way.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• EXISTS and NOT EXISTS are part of the SQL standard and return the same results in all four.
• Most modern optimizers turn EXISTS into a semi-join, so it is usually at least as fast as IN with a subquery and often faster than DISTINCT over a JOIN.
• The correlated UPDATE and DELETE syntax varies slightly by database (aliases, UPDATE ... FROM in SQL Server and PostgreSQL); the EXISTS logic itself does not.

Common Mistakes and Fixes

Tips

• Index the column used in the correlation (here Orders.CustomerID); it makes EXISTS checks fast on large tables.
• Read WHERE EXISTS (...) aloud as “where there is at least one …” and NOT EXISTS as “where there is no …”. If the sentence sounds right, the query usually is.
• Prefer EXISTS over COUNT(*) > 0: it says the same thing and lets the database stop at the first match.

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 Joins Explained: when you need columns from both tables

If you also want the spreadsheet side of these checks, the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy walks through lookup and logic formulas with practice files.

Frequently Asked Questions

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 can stop at the first matching row. In modern databases the optimizer frequently produces the same plan for both, so the bigger practical difference is NULL handling in the negated form.

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.

What is the difference between EXISTS and a JOIN?

A JOIN combines rows and can return columns from both tables, producing one row per match. EXISTS only filters the outer table and returns each outer row at most once.

Can I use EXISTS without a correlated subquery?

Yes, but then it is either TRUE for every row or FALSE for every row. That is occasionally useful as an on/off switch, but usually it is a bug.

When should I use NOT EXISTS instead of NOT IN?

Whenever the subquery column can contain NULLs, or when you are not sure. NOT EXISTS always gives the intuitive answer; NOT IN returns nothing if a single NULL appears.

Wrapping Up

EXISTS answers one question, “is there at least one matching row?”, and NOT EXISTS answers its opposite. Correlate the subquery to the outer row, keep the inner SELECT as simple as SELECT 1, and reach for NOT EXISTS whenever NULLs might be involved. Next, try the practice exercise above, then continue with SQL subqueries to see where EXISTS fits among the other subquery patterns.

📅 Last updated: October 2026

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