SQL Academy · Lesson

EXISTS and NOT EXISTS

Check for related rows efficiently.

EXISTS and NOT EXISTS is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is EXISTS?

The EXISTS operator tests whether a subquery returns at least one row. It evaluates to TRUE if the subquery produces any result, and FALSE if the subquery is empty.

Unlike other subquery operators that compare values, EXISTS only cares about presence — it does not look at the actual data returned by the subquery.

Setting Up Sample Tables

Before writing EXISTS queries, let's create two tables: customers and orders. We'll use these throughout the lesson to explore how EXISTS and NOT EXISTS work in practice.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100),
  country     VARCHAR(50)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  amount      DECIMAL(10,2),
  order_date  DATE
);

INSERT INTO customers VALUES
  (1, 'Alice',   'US'),
  (2, 'Bob',     'UK'),
  (3, 'Charlie', 'US'),
  (4, 'Diana',   'DE');

INSERT INTO orders VALUES
  (101, 1, 250.00, '2024-01-10'),
  (102, 1, 180.00, '2024-02-15'),
  (103, 2,  95.00, '2024-03-01'),
  (104, 3, 430.00, '2024-03-22');

Basic EXISTS Syntax

The basic syntax for EXISTS places it inside a WHERE clause. The subquery inside EXISTS usually references a column from the outer query — this is called a correlated subquery.

The query below finds every customer who has placed at least one order.

SELECT customer_id, name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

SELECT 1 Inside EXISTS

You may have noticed that the subquery uses SELECT 1 rather than selecting any real column. This is intentional — EXISTS only checks whether rows exist, not what they contain.

Using SELECT 1 (or even SELECT *) makes no difference to the result, but SELECT 1 signals clearly to both the database engine and the reader that the actual values do not matter.

-- Both of these return the same result
SELECT name FROM customers c
WHERE EXISTS (SELECT 1   FROM orders o WHERE o.customer_id = c.customer_id);

SELECT name FROM customers c
WHERE EXISTS (SELECT o.* FROM orders o WHERE o.customer_id = c.customer_id);

How the Database Evaluates EXISTS

For every row in the outer query, the database runs the correlated subquery. As soon as one matching row is found, the engine stops scanning and marks EXISTS as TRUE — this short-circuit evaluation makes EXISTS very efficient even on large tables.

In contrast, a JOIN would build the full set of matching rows before filtering, which can be slower when you only need to know whether a match exists.

-- EXISTS short-circuits after first match
-- Efficient even when orders table has millions of rows
SELECT name, country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.amount > 200
);

NOT EXISTS: Finding Absent Rows

NOT EXISTS is the inverse: it returns TRUE when the subquery finds no matching rows. This is the standard SQL way to answer questions like "which customers have never placed an order?"

Trying to solve this with a regular JOIN or NOT IN can produce incorrect results when NULLs are involved — NOT EXISTS avoids that trap entirely.

-- Customers who have NOT placed any order
SELECT customer_id, name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

NOT EXISTS vs NOT IN with NULLs

One critical advantage of NOT EXISTS over NOT IN is NULL safety. If the subquery used by NOT IN returns even one NULL, the entire NOT IN expression becomes NULL — which means no rows are returned from the outer query.

NOT EXISTS is immune to this problem because it evaluates row existence, not value equality.

-- Dangerous: if any customer_id in orders is NULL,
-- NOT IN returns zero rows!
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- Safe: NOT EXISTS handles NULLs correctly
SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

EXISTS With Multiple Conditions

The subquery inside EXISTS can contain any valid SQL, including multiple WHERE conditions. This lets you check for highly specific related rows — for example, customers who placed an order larger than a threshold in a particular month.

-- Customers who placed an order over 200 in March 2024
SELECT c.name, c.country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.amount > 200
    AND o.order_date >= '2024-03-01'
    AND o.order_date <  '2024-04-01'
);

EXISTS in DELETE and UPDATE

EXISTS is not limited to SELECT statements. You can use it in UPDATE and DELETE to modify or remove rows based on the existence of related data in another table.

The example below deletes orders belonging to customers from a specific country.

-- Delete orders placed by US customers
DELETE FROM orders o
WHERE EXISTS (
  SELECT 1
  FROM customers c
  WHERE c.customer_id = o.customer_id
    AND c.country = 'US'
);

Comparing EXISTS to a JOIN

EXISTS and JOIN can often express the same question, but they behave differently. A JOIN multiplies rows when there are multiple matches; EXISTS always returns each outer row at most once.

When you only need to know whether a relationship exists (not retrieve data from the related table), EXISTS is cleaner and typically faster.

-- JOIN may return duplicate customer rows if a customer has multiple orders
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

-- EXISTS always returns each customer once
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

NOT EXISTS for Data Quality Checks

NOT EXISTS is a powerful tool for data quality audits. You can use it to find orphaned records, missing references, or rows that should have related data but do not.

The query below detects any order rows whose customer_id does not match any row in the customers table — a sign of broken referential integrity.

-- Find orders with no matching customer (orphaned records)
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM customers c
  WHERE c.customer_id = o.customer_id
);

Quick Check

Test your understanding of EXISTS and NOT EXISTS.

Lesson Recap

In this lesson you learned how EXISTS and NOT EXISTS let you check for the presence or absence of related rows without comparing values directly.

Key takeaways:

EXISTS returns TRUE as soon as the subquery finds one matching row (short-circuit evaluation).
NOT EXISTS returns TRUE when the subquery finds no matching rows.
Use SELECT 1 inside EXISTS — the returned values are irrelevant.
NOT EXISTS is NULL-safe; NOT IN is not — prefer NOT EXISTS when NULLs may appear.
EXISTS works in SELECT, UPDATE, and DELETE statements.
When you only need existence, EXISTS is often cleaner and faster than a JOIN.

Frequently asked questions

Is the “EXISTS and NOT EXISTS” lesson free?

Yes — the full text of “EXISTS and NOT EXISTS” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “EXISTS and NOT EXISTS”?

Check for related rows efficiently. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “EXISTS and NOT EXISTS” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy