SQL Server Interview Question #74

What is the difference between EXISTS and IN?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

IN compares an expression against a list or subquery result, while EXISTS tests whether qualifying rows exist.

For many logically equivalent queries, SQL Server can generate similar plans. However, NULL semantics differ and can become especially important with NOT IN. EXISTS and NOT EXISTS are often preferred when expressing relationship existence or non-existence.

Performance should be determined from indexes, cardinality, statistics, and execution plans rather than assuming one syntax is always faster.

Code Example

SELECT c.Id, c.Name
FROM dbo.Customers AS c
WHERE c.Id IN
(
    SELECT o.CustomerId
    FROM dbo.Orders AS o
);

SELECT c.Id, c.Name
FROM dbo.Customers AS c
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Orders AS o
    WHERE o.CustomerId = c.Id
);