Correlated Subqueries for Anti-Joins

Snippet

Correlated Subqueries for Anti-Joins

A correlated subquery references columns from the outer query. Using NOT EXISTS with a correlated subquery is a highly performant way to implement anti-joins, such as finding customers who haven't placed an order in the last year.

snippet.sql
sql
SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.customer_id = c.id 
  AND o.order_date > CURRENT_DATE - INTERVAL '365' DAY
);

Breakdown

WHERE NOT EXISTS (

Filters for rows where the subquery returns no results.

SELECT 1 FROM orders o

The subquery selects a constant to minimize overhead since only existence matters.

WHERE o.customer_id = c.id

The correlation: links the subquery record to the current outer row.

Previous snippet Next snippet

From your library

Correlated Subqueries for Anti-Joins

Related