Difference Between EXISTS, IN, and JOIN

All three can relate data between tables, but their purposes differ:

• EXISTS checks whether at least one matching row exists.

• IN checks whether a value belongs to a list or result set.

• JOIN combines columns and rows from related tables.

Assume:

Customers

Orders

1. EXISTS

What is it?

EXISTS checks whether a subquery returns at least one row.

It returns a Boolean-style result:

• At least one row exists → TRUE

• No rows exist → FALSE

Example

Find customers who have placed at least one order:

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

Result

Why SELECT 1?

EXISTS only checks whether a row exists. It does not use the selected value.

These are logically equivalent:

SELECT 1

SELECT *

SELECT 1 clearly communicates that the returned columns are irrelevant.

Usage

Use EXISTS when:

• You only need to check for related records.

• The inner table may contain multiple matches.

• You need a correlated subquery.

• You want to use NOT EXISTS.

Key points

• It stops logically after finding the first matching row.

• It does not duplicate outer-query rows.

• It works safely with NOT EXISTS.

• Indexing the matching columns improves performance.

2. IN

What is it?

IN checks whether a value matches any value in a list or subquery result.

Example with a fixed list

SELECT
    CustomerId,
    CustomerName
FROM Customers
WHERE CustomerId IN (1, 2);

Example with a subquery

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE C.CustomerId IN
(
    SELECT O.CustomerId
    FROM Orders O
);

Result

Even though customer 1 has two orders, Arun appears only once because IN performs a membership check.

Usage

Use IN when:

• Comparing against a small fixed list

• Comparing one value with a single-column result

• The query represents a simple membership test

Key points

• The subquery must return one column.

• Duplicate values inside the subquery do not duplicate outer rows.

• It is easy to read for small lists.

• Be careful when using NOT IN if the subquery can return NULL.

3. JOIN

What is it?

A JOIN combines matching rows from multiple tables and can return columns from all joined tables.

Example

SELECT
    C.CustomerName,
    O.OrderId,
    O.Amount
FROM Customers C
INNER JOIN Orders O
    ON C.CustomerId = O.CustomerId;

Result

Arun appears twice because he has two orders.

Usage

Use a JOIN when:

• You need columns from multiple tables.

• You need details about each related record.

• You want to combine matching data.

• Row multiplication is required or expected.

Key points

• A one-to-many relationship can create multiple result rows.

• It can return columns from both tables.

• Different join types preserve different rows.

• Join columns should normally be indexed.

Major differences

Same requirement using all three

Find customers who have at least one order.

Using EXISTS

SELECT C.CustomerId, C.CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

Using IN

SELECT C.CustomerId, C.CustomerName
FROM Customers C
WHERE C.CustomerId IN
(
    SELECT O.CustomerId
    FROM Orders O
);

Using JOIN

SELECT DISTINCT
    C.CustomerId,
    C.CustomerName
FROM Customers C
INNER JOIN Orders O
    ON C.CustomerId = O.CustomerId;

DISTINCT is required in the JOIN version because one customer can have multiple orders.

NOT EXISTS vs NOT IN

Find customers without orders.

Recommended: NOT EXISTS

SELECT C.CustomerId, C.CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

Potential problem: NOT IN

SELECT C.CustomerId, C.CustomerName
FROM Customers C
WHERE C.CustomerId NOT IN
(
    SELECT O.CustomerId
    FROM Orders O
);

If Orders.CustomerId contains even one NULL, the NOT IN query may return no rows because comparisons involving NULL become UNKNOWN.

A safer version is:

SELECT C.CustomerId, C.CustomerName
FROM Customers C
WHERE C.CustomerId NOT IN
(
    SELECT O.CustomerId
    FROM Orders O
    WHERE O.CustomerId IS NOT NULL
);

However, NOT EXISTS is usually clearer and safer.

Which one should you use?

Interview answer

“EXISTS checks whether at least one matching row exists, IN checks whether a value belongs to a list or single-column result, and JOIN combines rows and returns columns from related tables. For existence checks, I prefer EXISTS; for fixed lists, IN; and when I need data from both tables, I use a JOIN. I normally prefer NOT EXISTS over NOT IN because NOT IN can produce unexpected results when the subquery contains NULL.”