PostgreSQL - EXISTS Operator

PostgreSQL EXISTS Operator

The EXISTS operator in PostgreSQL is a boolean operator used to test for the existence of rows in a subquery. It returns TRUE if the subquery returns one or more rows, and FALSE otherwise. EXISTS is highly efficient because it stops processing the subquery as soon as it finds the first matching row.

Syntax

SELECT column_list
FROM table_name
WHERE EXISTS (subquery);

How EXISTS Works

• The subquery is executed for each row of the outer query.
• If the subquery returns at least one row, EXISTS evaluates to TRUE, and the current row from the outer query is included in the result set.
• If the subquery returns no rows, EXISTS evaluates to FALSE, and the current row is discarded.
• It is commonly used with correlated subqueries, where the subquery depends on values from the outer query.

Example: Finding Customers Who Have Placed Orders

Let’s consider two tables: customers and orders. We want to find all customers who have placed at least one order.

Step 1: Create and Populate Tables

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
('Acme Corp'),
('Globex Inc'),
('Stark Industries');

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-26'),
(3, '2023-10-25');

Step 2: Use EXISTS to Find Customers with Orders

This query selects customers for whom an order exists.

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id -- This correlation is key
);

Best Practice: Using SELECT 1 inside the EXISTS subquery is a convention. Since EXISTS only cares about the presence of rows, not their content, there is no need to select actual data columns. SELECT 1 is the most efficient way to express this.

The query returns:

  customer_name
------------------
 Acme Corp
 Stark Industries
(2 rows)

Step 3: Using NOT EXISTS

The NOT EXISTS operator is the inverse. It’s used to find rows in the outer query that do not have a match in the subquery. Let’s find customers who have never placed an order.

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

This query returns:

 customer_name
---------------
 Globex Inc
(1 row)

EXISTS vs. IN vs. JOIN

These three constructs can sometimes achieve similar results, but their performance and intent differ.

• EXISTS: Best for checking existence when the subquery result set could be large. It stops as soon as a match is found. It does not return data from the subquery.
• IN: Best when the subquery returns a small, fixed set of values. The entire subquery is typically executed first, and its results are stored in memory for comparison. Can be less efficient than EXISTS for large subquery results.
• JOIN (specifically INNER JOIN): Use this when you need to select columns from both tables. If you only need to filter, EXISTS is often more performant and clearer in its intent, as JOIN may produce duplicate rows from the ‘one’ side if there are multiple matches on the ‘many’ side, requiring an additional DISTINCT.

For finding customers with orders, EXISTS is generally the most idiomatic and performant choice.