What is the difference between EXISTS and IN? | Interview Question

Q: What is the difference between EXISTS and IN?

**Target Audience:** Beginners who know a basic WHERE clause and have seen both forms in code reviews; anyone who has heard "EXISTS is faster" and wants to know when that is true. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A bouncer at a party has to decide who gets in. There are two ways to do the job. **Way one:** before the doors open, the host hands over a printed guest list. For every person in the queue, the bouncer checks: *is this name on the list?* That is `IN`. The list is buil

The problem

Question presented to candidate: "Say I want every customer who has placed at least one order. I could write WHERE id IN (SELECT customer_id FROM orders) or WHERE EXISTS (SELECT 1 FROM orders WHERE ...). What is the difference between the two, do they return the same rows, and which one would you pick?"

What a strong answer should cover:

IN tests whether a value is a member of a list or of one column returned by a subquery; EXISTS tests whether a (usually correlated) subquery returns at least one row
Both are semi joins: each outer row comes back at most once, however many matches there are
EXISTS ignores the select list (SELECT 1, SELECT *, even SELECT 1/0 all behave the same) and can carry any multi-column or non-equality condition
For positive checks they return the same rows even with NULLs; the real difference shows up with NOT IN versus NOT EXISTS
Performance: modern optimizers (PostgreSQL, MySQL 8.0.16+) usually plan both the same way; older advice "EXISTS is always faster" is engine- and version-specific, so read the plan

Code / implementation expected: Yes — the same "customers with an order" query written with IN and with EXISTS, plus one check that needs more than one column.

Schema used by the examples:

exists-in

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know a basic WHERE clause and have seen both forms in code reviews; anyone who has heard "EXISTS is faster" and wants to know when that is true. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A bouncer at a party has to decide who gets in. There are two ways to do the job.

Way one: before the doors open, the host hands over a printed guest list. For every

The solution is hidden so you can try first

12 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLIN with a subquery: customers who have placed at least one order

SQLEXISTS with a correlated, multi-condition check: customers with a March order over 100

SQLRow-value IN: the largest order of each customer, matched on two columns at once

PostgreSQL: the EXISTS query gets the same Hash Join plan as the IN query

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

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.