EXISTS and NOT EXISTS

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Test whether related rows exist without duplicating the outer result.

Run the example

SELECT p.ProductID, p.ProductName
FROM dbo.Products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.OrderItems AS i
    WHERE i.ProductID = p.ProductID AND i.OrderID BETWEEN 1 AND 5
)
ORDER BY p.ProductID;

Read it step by step

1. For each product, the inner query looks for a line in Orders 1–5.
2. NOT EXISTS keeps products with no such line; SELECT 1 is only a placeholder because existence is what matters.
3. The product ID is unique, so each retained product appears once.

Check the result

This says nothing about orders after ID 5.

Practice

Return products that do appear in OrderItems for Orders 1–5.

SELECT p.ProductID, p.ProductName
FROM dbo.Products AS p
WHERE EXISTS (
    SELECT 1 FROM dbo.OrderItems AS i
    WHERE i.ProductID = p.ProductID AND i.OrderID BETWEEN 1 AND 5
)
ORDER BY p.ProductID;