PostgreSQL EXISTS Operator
EXISTS
TheEXISTS operator is used to test for the existence of any record in a sub query.
TheEXISTS operator returns TRUE if the sub query returns one or more records.
Example
Return all customers that is represented in theorders table:
SELECT customers.customer_name
FROM customers
WHERE EXISTS (
SELECT order_id
FROM orders
WHERE customer_id = customers.customer_id
);
Run Example »FROM customers
WHERE EXISTS (
SELECT order_id
FROM orders
WHERE customer_id = customers.customer_id
);
The result in example above showed that 89 customers had at least one order in theorders table.
NOT EXISTS
To check which customers that do not have any orders, we can use theNOT operator together with theEXISTS operator :
Example
Return all customers that is NOT represented in theorders table:
SELECT customers.customer_name
FROM customers
WHERE NOT EXISTS (
SELECT order_id
FROM orders
WHERE customer_id = customers.customer_id
);
Run Example »FROM customers
WHERE NOT EXISTS (
SELECT order_id
FROM orders
WHERE customer_id = customers.customer_id
);
