SQL
Find Records Without Matching Entries Using NOT EXISTS
Learn to identify rows in one table that do not have corresponding entries in another table, effectively finding missing links or orphaned records using the efficient NOT EXISTS clause.
SELECT
c.customer_id,
c.customer_name
FROM
customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= '2023-01-01'
);
How it works: This query identifies customers who have not placed any orders since a specific date (`'2023-01-01'`). It uses `NOT EXISTS` with a correlated subquery, which is an efficient way to check for the absence of related records. For each customer, the subquery attempts to find a matching order. If no order is found for a given customer within the specified criteria, that customer is included in the final result, making it perfect for finding 'missing' relationships.