PostgreSQL EXISTS Operator
EXISTS
The EXISTS
operator is used to test for the existence of any record in a
sub query.
The EXISTS
operator returns TRUE if the sub
query returns one or more records.
Example
Return all customers that is represented in the orders
table:
SELECT customers.customer_name
FROM customers
WHERE EXISTS (
SELECT order_id
FROM orders
WHERE customer_id = customers.customer_id
);
Run Example »
The result in example above showed that 89 customers had at least one order in the
orders
table.
NOT EXISTS
To check which customers that do not have any orders, we can use the
NOT
operator together with the EXISTS
operator :
Example
Return all customers that is NOT represented in the orders
table:
SELECT customers.customer_name
FROM customers
WHERE NOT EXISTS (
SELECT order_id
FROM orders
WHERE customer_id = customers.customer_id
);
Run Example »
Exercise?What is this?
Test your skills by answering a few questions about the topics of this page
Drag and drop the correct syntax to create a query that lists suppliers with products priced under 20.
SELECT supplier_name
suppliers
(SELECT product_name FROM products WHERE products.supplier_id = suppliers.supplier_id AND price < 20);
WHERE
EXISTS
SELECT
FROM
GROUP BY