PostgreSQL EXISTS

Key EXISTS Concepts

WHERE EXISTS (
  SELECT 1 FROM table
  WHERE condition
)

WHERE NOT EXISTS (
  SELECT 1 FROM table
  WHERE condition
)

-- Efficient checking
EXISTS (subquery)
-- vs
COUNT(*) > 0

WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.id = c.id
)

🔹 Basic EXISTS Usage

The EXISTS operator is used to test for the existence of rows in a subquery. It's commonly used to find records in one table that have related records in another table. EXISTS is more efficient than using IN with large datasets because it stops searching as soon as it finds the first matching row.

-- Find customers who have placed orders
SELECT customer_id, customer_name, email
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🔹 NOT EXISTS Usage

NOT EXISTS is the opposite of EXISTS - it returns TRUE when the subquery returns no rows. This is extremely useful for finding records that don't have related records in another table, such as customers who haven't placed orders or products that haven't been sold. It's a clean way to identify missing relationships.

-- Find customers who have NOT placed any orders
SELECT customer_id, customer_name, email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🔹 EXISTS with Multiple Conditions

You can add multiple conditions inside the EXISTS subquery to make your checks more specific. This allows you to find records that meet complex criteria in related tables. For example, you might want to find customers who have placed orders above a certain amount or within a specific date range.

-- Find customers with orders over $1000
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > 1000
);

🔹 EXISTS vs IN

While both EXISTS and IN can achieve similar results, EXISTS is generally more efficient for large datasets because it stops as soon as it finds a match. IN evaluates all values in the subquery before returning results. EXISTS is also better when dealing with NULL values and is the preferred choice for correlated subqueries where you're checking relationships.

-- Using EXISTS (More efficient)
SELECT product_name
FROM products p
WHERE EXISTS (
    SELECT 1 
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

-- Using IN (Less efficient for large datasets)
SELECT product_name
FROM products p
WHERE p.product_id IN (
    SELECT product_id 
    FROM order_items
);

🔹 EXISTS with Date Conditions

EXISTS is particularly useful when working with date-based conditions to find records within specific time periods. You can check if related records exist within a date range, making it perfect for time-based analysis like finding active customers in the last month or identifying recent activity patterns in your data.

-- Find customers who ordered in the last 30 days
SELECT customer_id, customer_name, last_login
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

🔹 Multiple EXISTS Conditions

You can combine multiple EXISTS clauses using AND or OR to create complex filtering logic. This is powerful for finding records that meet multiple relationship criteria simultaneously, such as customers who have both placed orders and left reviews, or employees who work on multiple projects meeting different conditions.

-- Find customers who have both orders AND reviews
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
)
AND EXISTS (
    SELECT 1 FROM reviews r
    WHERE r.customer_id = c.customer_id
);

🔹 EXISTS in UPDATE Statements

EXISTS can be used in UPDATE statements to conditionally modify records based on the existence of related data. This is useful for maintaining data integrity or updating records based on relationships with other tables. For example, you might want to update customer status only if they have recent orders or flag products that have pending shipments.

-- Update customer status if they have recent orders
UPDATE customers c
SET status = 'active'
WHERE EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
);