Prev Next

PostgreSQL EXISTS

The EXISTS operator checks for the existence of rows returned by a subquery. It returns TRUE if the subquery returns at least one row and FALSE otherwise.

Key Topics

1. Basic Usage

SELECT c.customer_id, c.first_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This returns customers who have at least one order.

2. Using NOT EXISTS

Use NOT EXISTS to find rows with no matching subquery result:

SELECT c.customer_id, c.first_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This finds customers without any orders.

Best Practices

  • Use EXISTS for efficient subqueries; PostgreSQL can optimize for early exit once a match is found.
  • Be mindful of subquery complexity and indexing for best performance.

Key Takeaways

  • EXISTS checks if a subquery returns any row.
  • NOT EXISTS ensures no subquery rows exist for the condition.
Prev Next