Subqueries with EXISTS in PostgreSQL

Introduction

Use EXISTS to check if a related set of data exists without returning the data itself. It helps answer yes/no questions efficiently.

Check if a customer has made any orders before showing their details.

Find products that have at least one review.

List employees who have completed any training sessions.

Show departments that have employees assigned.

Syntax

PostgreSQL

SELECT column_list
FROM table_name
WHERE EXISTS (
    SELECT 1
    FROM related_table
    WHERE condition
);

EXISTS returns true if the subquery returns any rows, false if none.

The subquery usually uses a condition to link to the outer query.

Examples

Find customers who have placed at least one order.

PostgreSQL

SELECT name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.id
);

List products that have reviews.

PostgreSQL

SELECT product_name
FROM products
WHERE EXISTS (
    SELECT 1
    FROM reviews
    WHERE reviews.product_id = products.id
);

Show departments with employees.

PostgreSQL

SELECT department_name
FROM departments
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE employees.department_id = departments.id
);

Sample Program

This query lists customers who have made at least one order. Alice (id=1) and Charlie (id=3) have orders, Bob does not.

PostgreSQL

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT);

INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO orders (customer_id) VALUES (1), (1), (3);

SELECT name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.id
);

Important Notes

EXISTS stops checking as soon as it finds the first matching row, making it efficient.

Use SELECT 1 or SELECT * inside EXISTS; the selected columns do not matter.

EXISTS is often faster than IN when checking for existence.

Summary

EXISTS checks if a subquery returns any rows and returns true or false.

It is useful for filtering rows based on related data presence.

EXISTS queries are efficient because they stop searching after finding the first match.