SQL EXISTS and NOT EXISTS: Check Related Rows Safely

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

The subquery inside EXISTS supplies evidence that a qualifying row exists. Its selected values are normally irrelevant: SELECT 1 is a common convention because the question concerns rows rather than the value of the constant. EXISTS does not count how many qualifying rows there are. One match and a thousand matches both make it true, so the outer customer row appears once if the outer input itself contains that customer once.

A correlated subquery refers to the current outer row. In WHERE EXISTS (SELECT 1 FROM sales AS s WHERE s.customer_id = c.id), c identifies the customer being tested. The inner WHERE looks only for that customer’s sales. Omitting the correlation changes the question to whether the sales table has any row at all; if it does, every customer passes. Carefully read each qualified column to confirm which query level provides it.

NOT EXISTS asks whether the qualifying set is empty. It is useful for customers without sales, products without stock movements, or records missing a required relationship. A NULL customer_id in an unrelated sale does not poison the entire test. Its equality comparison with a particular customer is unknown, so that inner row does not qualify, while other matching sales still qualify normally. This differs from NOT IN when the returned membership set contains NULL.

Place the business filters inside the subquery when they define the relationship you are testing. Customers without any paid sales are different from customers without any sales. Cara has a pending sale and a paid sale, so she passes a has-paid-sale test. A customer with pending sales only would pass has-sales and fail has-paid-sales. The optimiser may implement the query as a semi join or anti join; SQL’s meaning does not require physically running the inner statement from scratch for every outer row. Indexing the matching customer_id can help on larger data.

EXISTS and NOT EXISTS with correlated subqueries are supported by all four systems. SELECT 1 is a convention, not a requirement that the subquery produce exactly one row. Execution strategies differ; avoid claiming that an engine always checks rows in a specific physical order or always performs one separate scan per customer.

Check the engine-specific syntax and version requirements in PostgreSQL EXISTS reference.

Return customers who have at least one paid sale. Use EXISTS and keep one result row per customer.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT c.id,c.name FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM sales AS s
  WHERE s.customer_id = c.id AND s.status = 'paid'
) ORDER BY c.id;

id | name
1 | Ana
2 | Ben
3 | Cara

The status condition defines the evidence required by the inner query. Each of Ana, Ben and Cara has a paid sale, regardless of any additional pending sales. Dev has no matching evidence. Sale 106 is paid but has no customer link, so it supplies evidence for none of these customers.

Remove the customer correlation temporarily and predict that every customer will pass EXISTS because sales is nonempty. Restore the relationship, then change the inner status from paid to pending: Ana and Cara should remain while Ben and Dev disappear. The selected constant can change from 1 to 99 without changing the truth of the existence test.