EXISTS vs IN vs JOIN - What Actually Happens?

Suppose we have two tables:

Customers
----------------
CustomerId
Name

Orders
----------------
OrderId
CustomerId
Amount

Our requirement is simple:

Find customers who have at least one order.

There are several ways to write it.

1. EXISTS - "Does a match exist?"

SELECT c.*
FROM Customers c
WHERE EXISTS
(
    SELECT 1
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
);

Think of EXISTS as asking:

"Does at least one matching order exist for this customer?"

It doesn't matter whether the customer has 1 or 100 orders. The customer is returned once.

Customer A → 3 orders → ✓
Customer B → 1 order  → ✓
Customer C → 0 orders  → ✗

Use EXISTS when you only care whether a related record exists.

2. IN - "Is this value in the list?"

SELECT *
FROM Customers
WHERE CustomerId IN
(
    SELECT CustomerId
    FROM Orders
);

Think:

"Is this customer's ID present in the list of customer IDs from Orders?"

For a simple membership check, IN is very readable.

Orders
CustomerId
----------
1
2
2
4

        ↓

Customers with ID 1, 2, 4

Use IN when you're checking whether a value belongs to a set of values.

3. JOIN - "Give me the matching rows"

SELECT c.*
FROM Customers c
JOIN Orders o
    ON o.CustomerId = c.CustomerId;

Here we're actually joining the rows.

If a customer has three orders:

Customer A
   ↓
Order 1
Order 2
Order 3

the JOIN produces three rows for that customer.

If we only want customers, we might need:

SELECT DISTINCT c.*
FROM Customers c
JOIN Orders o
    ON o.CustomerId = c.CustomerId;

But if we actually need order information:

SELECT
    c.Name,
    o.OrderId,
    o.Amount
FROM Customers c
JOIN Orders o
    ON o.CustomerId = c.CustomerId;

then JOIN is the right tool.

The Key Difference

Imagine:

Customer A → 3 orders
Customer B → 1 order
Customer C → 0 orders

EXISTS

A ✓
B ✓
C ✗

One customer = one result.

IN

A ✓
B ✓
C ✗

Also one customer = one result.

JOIN

A → Order 1
A → Order 2
A → Order 3
B → Order 4

Rows can multiply.

Which One Should You Use?

A simple rule:

Do I only need to know if a related row exists?
                ↓
             EXISTS

Am I checking whether a value belongs to a list?
                ↓
               IN

Do I need columns from the related table?
                ↓
              JOIN

What About Performance?

Don't fall into this common trap:

"EXISTS is always faster than IN."

That's not necessarily true.

SQL Server's optimizer can produce similar execution strategies for EXISTS, IN, and JOIN, depending on:

• Indexes
• Data size
• Data distribution
• Constraints
• Query structure

When performance matters, check the actual execution plan and logical reads instead of choosing syntax based on a performance myth.

Final Takeaway

Remember it this way:

EXISTS asks "Does it exist?"
IN asks "Is it in the list?"
JOIN asks "Give me the matching rows."

The best SQL isn't about using the "fastest-looking" syntax.

It's about choosing the syntax that most clearly expresses what you're trying to do.