EXISTS and NOT EXISTS

EXISTS (subquery) is true if the subquery returns at least one row, and false if it returns none. It does not care what the rows contain. Which customers have placed an order?

1SELECT c.CustomerId, c.FirstName2FROM dbo.Customers AS c3WHERE EXISTS (4    SELECT 15    FROM dbo.Orders AS o6    WHERE o.CustomerId = c.CustomerId7)8ORDER BY c.CustomerId;

CustomerId  FirstName
----------  ---------
         1  Asha
         2  Ben
         3  Chloe
         4  Dev
         6  Farid
         8  Zoë

• The subquery is correlated: o.CustomerId = c.CustomerId ties it to the customer being tested.
• SELECT 1 is a convention. The select list of an EXISTS subquery is never used, so SELECT * or SELECT 1 behave the same.
• SQL Server can stop looking as soon as it finds the first matching row. Asha has two orders, but one is enough.

SELECT c.Name
FROM dbo.Categories AS c
WHERE EXISTS (
    SELECT 1 FROM dbo.Products AS p
    WHERE p.CategoryId = c.CategoryId
);

NOT EXISTS: rows with no match

NOT EXISTS keeps the rows for which the subquery finds nothing. Customers who have never ordered:

1SELECT c.CustomerId, c.FirstName, c.JoinedOn2FROM dbo.Customers AS c3WHERE NOT EXISTS (4    SELECT 1 FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId5);

CustomerId  FirstName  JoinedOn
----------  ---------  ----------
         5  Ella       2023-09-14
         7  Grace      2024-01-08

This is called an anti-join. It gives the same answer as the LEFT JOIN … WHERE key IS NULL pattern from the outer joins lesson, and says what you mean more directly.

Conditions inside the subquery

The subquery can be as detailed as you need. Customers who have a shipped order containing Timber:

1SELECT c.FirstName, c.City2FROM dbo.Customers AS c3WHERE EXISTS (4    SELECT 15    FROM dbo.Orders AS o6    JOIN dbo.OrderItems AS oi ON oi.OrderId  = o.OrderId7    JOIN dbo.Products   AS p  ON p.ProductId = oi.ProductId8    WHERE o.CustomerId = c.CustomerId9      AND o.Status     = 'Shipped'10      AND p.CategoryId = 311);

FirstName  City
---------  --------
Dev        Bradford
Farid      York

And EXISTS and NOT EXISTS combine with AND and OR like any other test. Customers who have ordered, but have nothing pending:

1SELECT c.FirstName2FROM dbo.Customers AS c3WHERE EXISTS     (SELECT 1 FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId)4  AND NOT EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId AND o.Status = 'Pending')5ORDER BY c.FirstName;

FirstName
---------
Asha
Chloe
Dev
Farid

EXISTS or a join?

A join returns one row for every match, so a customer with two matching orders appears twice. EXISTS (a semi-join) returns each outer row at most once:

1SELECT c.FirstName2FROM dbo.Customers AS c3JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId4WHERE o.Status = 'Shipped'5ORDER BY c.FirstName;6 7SELECT c.FirstName8FROM dbo.Customers AS c9WHERE EXISTS (SELECT 1 FROM dbo.Orders AS o10              WHERE o.CustomerId = c.CustomerId AND o.Status = 'Shipped')11ORDER BY c.FirstName;

FirstName
---------
Asha
Asha
Ben
Dev
Dev
Farid
Farid

FirstName
---------
Asha
Ben
Dev
Farid

Adding DISTINCT to the join would hide the duplicates, but it does extra work to remove rows that EXISTS never makes. Use a join when you need columns from the other table; use EXISTS when you only need to know whether a match exists.

The NOT IN trap

NOT IN (subquery) looks like it should do the same as NOT EXISTS, and with BuildMart’s data it does. Which categories have no products?

1SELECT Name2FROM dbo.Categories3WHERE CategoryId NOT IN (SELECT CategoryId FROM dbo.Products);

Name
--------
Plumbing

Now add a product with no category (a gift voucher, CategoryId NULL) and run the same query again:

1INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)2VALUES (N'Gift voucher', NULL, 25.00, 100, N'each', '2024-07-01');3 4SELECT Name5FROM dbo.Categories6WHERE CategoryId NOT IN (SELECT CategoryId FROM dbo.Products);

Name
----

Nothing at all. 7 NOT IN (1, 2, 3, 4, 5, 6, NULL) means 7 <> 1 AND 7 <> 2 AND … AND 7 <> NULL. The last test is unknown, and an AND with an unknown part is never true. One NULL in the list makes NOT IN fail for every row.

1INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)2VALUES (N'Gift voucher', NULL, 25.00, 100, N'each', '2024-07-01');3 4SELECT c.Name5FROM dbo.Categories AS c6WHERE NOT EXISTS (SELECT 1 FROM dbo.Products AS p WHERE p.CategoryId = c.CategoryId);

Name
--------
Plumbing

NOT EXISTS only asks whether a matching row exists. The voucher’s NULL matches no category, so it simply does not count.

1SELECT p.Name, p.Stock2FROM dbo.Products AS p3WHERE NOT EXISTS (4    SELECT 15    FROM dbo.OrderItems AS oi6    JOIN dbo.Orders AS o ON o.OrderId = oi.OrderId7    WHERE oi.ProductId = p.ProductId8      AND o.Status = 'Shipped'9)10ORDER BY p.Name;

Name              Stock
----------------  -----
Safety goggles        0
Wall plugs (100)      0