PostgreSQL EXISTS condition

Preamble

PostgreSQL EXISTS condition is used in combination with a subquery and is considered “satisfied” if the subquery returns at least one line. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.

The syntax for PostgreSQL EXISTS condition

WHERE EXISTS ( subquery );

Parameters and arguments of the condition

• subquery – A SELECT operator which usually starts with SELECT *, not with a list of expressions or column names. To improve performance, you can replace SELECT * with SELECT 1 because the result of the subquery column does not matter (only the returned rows are important).

Note:

• SQL statements that use the EXISTS condition in PostgreSQL are very inefficient because the subquery is restarted for EVERY line in the external query table. There are more efficient ways to write most queries that do not use the EXISTS condition.

Example EXISTS Condition with SELECT Operator

Let us consider a simple example. Below is the SELECT operator which uses PostgreSQL condition EXISTS:

SELECT *
FROM products
WHERE EXISTS (SELECT 1
FROM inventory
WHERE products.product_id = inventory.product_id);

In this PostgreSQL EXISTS condition example, it will return all entries from the products table where the inventory table has at least one entry with the matching product_id. We used SELECT 1 in the subquery to improve performance because the resulting set of columns has nothing to do with the EXISTS condition (only the returned row counts).

Example of a SELECT operator using NOT EXISTS

PostgreSQL condition EXISTS can also be combined with NOT operator.
For example,

SELECT *
FROM products
WHERE DOES NOT EXIST (SELECT 1
FROM inventory
WHERE products.product_id = inventory.product_id);

In this PostgreSQL example EXISTS will return all records from the Products table, where the inventory table has no records for this product_id).

Example EXISTS condition with INSERT operator

Below is an example of the INSERT operator which uses PostgreSQL condition EXISTS:

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT 1
FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);

Example of condition with UPDATE operator

Below is an example of UPDATE operator, which uses PostgreSQL condition EXISTS:

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT 1
FROM customers
WHERE customers.customer_id = supplier_id);

Example of PostgreSQL EXISTS condition with DELETE operator

Below is an example of a DELETE operator that uses PostgreSQL EXISTS condition:

DELETE FROM contacts
WHERE EXISTS (SELECT 1
FROM employees
WHERE contacts.contact_id = employees.employee_id);

PostgreSQL EXISTS condition

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.