0Pricing
SQL Academy · Lesson

EXISTS vs JOIN Performance

Choose the faster pattern.

EXISTS vs JOIN Performance is a free SQL Academy lesson on CoddyKit — lesson 4 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.

Why Performance Matters Here

When you need to check whether related rows exist in another table, SQL gives you several tools: EXISTS, IN, and JOIN. Each produces correct results, but they can perform very differently depending on your data size, indexes, and database engine.

In this lesson you will learn how each approach works under the hood and when to reach for which one.

Sample Tables

We will use two tables throughout this lesson: customers and orders. A customer may have zero or many orders. This is a classic one-to-many relationship perfect for testing EXISTS vs JOIN patterns.

CREATE TABLE customers (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  total       NUMERIC(10,2)
);

INSERT INTO customers (name) VALUES
  ('Alice'), ('Bob'), ('Carol'), ('Dave');

INSERT INTO orders (customer_id, total) VALUES
  (1, 120.00), (1, 85.50), (3, 200.00);

The JOIN Approach

A common pattern is to use INNER JOIN to find customers who have at least one order. This works, but notice the problem: if a customer has five orders, they appear five times in the result set before DISTINCT collapses them.

That duplication is extra work the database must do — it builds the full join result, then deduplicates.

SELECT DISTINCT c.id, c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

The EXISTS Approach

EXISTS answers a yes/no question: does at least one matching row exist? The moment the engine finds the first match it stops scanning — this is called short-circuit evaluation.

No duplicates are produced and no DISTINCT is needed, because EXISTS never actually returns the inner rows.

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

Short-Circuit Is the Key

Short-circuit evaluation means the subquery stops as soon as one qualifying row is found. Whether a customer has 1 order or 10,000 orders, EXISTS only reads until the first hit.

A JOIN must read all matching rows to build the result set, even when you only care about existence. On wide tables with many child rows per parent, this difference compounds quickly.

-- EXISTS stops after finding row #1
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1          -- 'SELECT 1' is conventional; the value does not matter
  FROM orders o
  WHERE o.customer_id = c.id
);

-- JOIN scans ALL matching order rows
SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

NOT EXISTS vs LEFT JOIN ... IS NULL

For the opposite check — finding customers with no orders — you can use NOT EXISTS or a LEFT JOIN ... WHERE IS NULL pattern. Both are common, but NOT EXISTS is usually more readable and the optimizer often prefers it.

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

-- LEFT JOIN ... IS NULL (equivalent result)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The Role of Indexes

Both EXISTS and JOIN benefit enormously from an index on the foreign key column. Without an index on orders.customer_id, every outer row triggers a full table scan of orders.

Adding that index is often the single biggest performance win — more impactful than choosing between EXISTS and JOIN.

-- Create an index on the foreign key
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Now both patterns use an index lookup instead of a full scan
EXPLAIN
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

Reading EXPLAIN Output

Use EXPLAIN (or EXPLAIN ANALYZE to also run the query) to see how the database executes a query. Look for these clues:

  • Index Scan — good; the index is being used.
  • Seq Scan on a large table — potentially a red flag; an index might help.
  • Hash Join / Nested Loop — the join algorithm chosen; Nested Loop pairs well with index scans.
EXPLAIN ANALYZE
SELECT c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 0;

When JOIN Wins

EXISTS shines for pure existence checks. But if you also need data from the related table — like the order total or the order date — you must use a JOIN. There is no way to return columns from inside an EXISTS subquery.

Choose the tool that fits the question: EXISTS for 'does it exist?', JOIN for 'give me data from both tables'.

-- Need order data? JOIN is the only option.
SELECT c.name, o.total, o.id AS order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

IN vs EXISTS on Large Sets

IN (subquery) evaluates the entire subquery first, builds an in-memory list of values, then checks each outer row against that list. With millions of rows this list can exhaust memory.

EXISTS is evaluated row-by-row and short-circuits, so it never materializes the full inner result set. On large correlated checks, EXISTS is almost always faster than IN.

-- IN builds the full list first
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders
);

-- EXISTS evaluates per-row and short-circuits
SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

Decision Cheat-Sheet

Here is a quick reference for choosing the right pattern:

  • EXISTS — you only need to know if a match exists; large child tables; NOT EXISTS for anti-join.
  • JOIN — you need columns from the related table; aggregations across both tables.
  • IN — short, static value lists (WHERE status IN ('active', 'pending')); avoid for large subqueries.
  • Always index the foreign key column — this matters more than the syntax choice.

Quick Check

Which statement best explains why EXISTS can be faster than INNER JOIN + DISTINCT when checking for the presence of related rows?

Lesson Recap

In this lesson you learned how to choose between EXISTS and JOIN for performance-conscious SQL:

  • EXISTS short-circuits — it stops scanning as soon as the first match is found, avoiding duplicates without needing DISTINCT.
  • JOIN returns all matching rows — use it when you need data from the related table, but add DISTINCT or GROUP BY if you only care about the parent row.
  • NOT EXISTS is a clean anti-join pattern; LEFT JOIN ... IS NULL is equivalent but more verbose.
  • Avoid IN with large subqueries — it materializes the entire inner result; EXISTS is more memory-efficient.
  • Index your foreign keys — this single step often delivers the biggest performance gain regardless of which syntax you choose.
  • Use EXPLAIN / EXPLAIN ANALYZE to verify the execution plan and confirm indexes are being used.

Frequently asked questions

Is the “EXISTS vs JOIN Performance” lesson free?

Yes — the full text of “EXISTS vs JOIN Performance” 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 vs JOIN Performance”?

Choose the faster pattern. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “EXISTS vs JOIN Performance” 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

  1. Correlated Subqueries
  2. EXISTS and NOT EXISTS
  3. IN vs ANY vs ALL
  4. EXISTS vs JOIN Performance
← Back to SQL Academy