NOT EXISTS returns rows when the correlated subquery does not find a matching record. The subquery can contain JOINs to apply conditions across multiple related tables.
This pattern is powerful for finding exceptions and entities that do not satisfy a business condition.
Find customers who have never purchased an electronics product.
SELECT
c.CustomerID,
c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
INNER JOIN Products p
ON o.ProductID = p.ProductID
WHERE o.CustomerID = c.CustomerID
AND p.Category = 'Electronics'
);Used in customer segmentation, compliance monitoring, exception reporting, and data-quality analysis.
- NOT EXISTS finds missing qualifying relationships.
- The subquery can contain JOINs.
- It is useful for exception analysis.
- It avoids returning duplicate parent rows.
- It handles existence logic without needing related columns in the output.
What does NOT EXISTS return?
It returns rows for which the correlated subquery finds no qualifying matching record.
Why is NOT EXISTS useful with JOINs?
It can identify entities that do not have a related record satisfying conditions across multiple tables.