EXISTS Operator in PostgreSQL

The PostgreSQL EXISTS operator is a boolean operator that tests whether a subquery returns any rows. It returns true if the subquery produces at least one row and false if it produces none. EXISTS is evaluated purely on row presence — column values in the subquery result are irrelevant — and it short-circuits after finding the first qualifying row.

Syntax

SELECT select_list
FROM table_name
WHERE EXISTS (
  SELECT 1
  FROM other_table
  WHERE condition
);

The convention is to write SELECT 1 inside EXISTS because column values are never used. The subquery is typically a correlated subquery that references the outer query’s current row to check for a related-row relationship.

NOT EXISTS inverts the result:

WHERE NOT EXISTS (
  SELECT 1
  FROM other_table
  WHERE condition
)

Practical Example

Create products and orders tables to track which products have been purchased:

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  category     VARCHAR(50),
  unit_price   NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  product_id   INT REFERENCES products (product_id),
  quantity     INT NOT NULL,
  order_total  NUMERIC(10, 2) NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO products (name, category, unit_price) VALUES
  ('Widget A',   'Hardware',  9.99),
  ('Widget B',   'Hardware', 14.99),
  ('Gadget Pro', 'Software', 49.99),
  ('Gadget Lite','Software', 19.99),
  ('Component X','Hardware',  4.50);

INSERT INTO orders (product_id, quantity, order_total, placed_at) VALUES
  (1, 5,  49.95, '2026-03-01 10:00:00+00'),
  (3, 2,  99.98, '2026-03-02 11:30:00+00'),
  (1, 3,  29.97, '2026-03-03 09:15:00+00'),
  (2, 1,  14.99, '2026-03-04 14:00:00+00');

Simple existence check

Check whether any high-value orders (total > 90) exist in the system:

SELECT EXISTS (
  SELECT 1
  FROM orders
  WHERE order_total > 90
);

 exists
--------
 t
(1 row)

Using EXISTS to filter rows

Find all products that have at least one order:

SELECT product_id, name, unit_price
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.product_id = p.product_id
)
ORDER BY name;

 product_id |    name     | unit_price
------------+-------------+------------
          2 | Widget B    |      14.99
          1 | Widget A    |       9.99
          3 | Gadget Pro  |      49.99
(3 rows)

For each row in products p, the correlated subquery checks whether a matching order exists. EXISTS stops as soon as it finds the first order for each product.

Using NOT EXISTS to find absent relationships

Find all products that have never been ordered — the classic anti-join pattern:

SELECT product_id, name, unit_price
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.product_id = p.product_id
)
ORDER BY name;

 product_id |     name     | unit_price
------------+--------------+------------
          5 | Component X  |       4.50
          4 | Gadget Lite  |      19.99
(2 rows)

NOT EXISTS returns the complement of EXISTS — every product for which the subquery produces zero rows.

EXISTS and NULL Behavior

If the subquery returns a row whose value is NULL, EXISTS still returns true because a row was produced:

-- Returns all products — subquery always produces one row (NULL)
SELECT name FROM products
WHERE EXISTS (SELECT NULL)
ORDER BY name;

This confirms that EXISTS evaluates row presence only, not the truthiness of column values. It also explains why NOT EXISTS is safer than NOT IN when the subquery column may contain NULLs: NOT IN with a NULL in the set returns NULL (unknown) for every outer row, silently dropping all results.

EXISTS vs. IN vs. NOT IN

Testing with Vela

EXISTS and NOT EXISTS are commonly used in data-quality checks and migration validation scripts — for example, verifying that every order row has a matching product before a foreign key migration. Vela’s database branching lets you run these validation queries against a clone of production before altering the live schema, so you catch data integrity issues before they block a migration.

Production Tips

• Index the correlated join column in the inner table (e.g., orders.product_id) so each inner lookup is an index scan rather than a sequential scan.
• Use SELECT 1 inside EXISTS rather than SELECT * or SELECT column — it makes intent explicit and avoids fetching column data that is never used.
• Prefer NOT EXISTS over NOT IN whenever the subquery column might contain NULLs. The NULL behavior of NOT IN is a common source of hard-to-debug query logic errors.
• EXISTS short-circuits, so it is often faster than COUNT(*) > 0 for presence checks on large tables. Avoid SELECT COUNT(*) FROM ... WHERE ... > 0 as a substitute.
• Use EXPLAIN ANALYZE to inspect the “loops” and “actual rows” for the EXISTS subplan — confirm it is exiting early on hits, not scanning the entire inner table.