EXISTS and NOT EXISTS in Postgres

As the name suggests, the EXISTS operator is used to test for the existence of any record in a subquery.

It returns true if the subquery returns one or more records.

Syntax:


EXISTS (subquery)

Detailed Syntax:


SELECT column(s)
FROM table
WHERE EXISTS
(SELECT column FROM table WHERE condition);

The argument of EXISTS is an arbitrary SELECT statement, or subquery.

The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is “true”; if the subquery returns no rows, the result of EXISTS is “false”.

The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.

The subquery will generally only be executed far enough to determine whether at least one row is returned, not all the way to completion. It is unwise to write a subquery that has any side effects (such as calling sequence functions); whether the side effects occur or not may be difficult to predict.

Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally uninteresting or doesn’t matter.

A common coding convention is to write all EXISTS tests in the form EXISTS(SELECT 1 WHERE …). There are exceptions to this rule however, such as subqueries that use INTERSECT.

Examples

Postgres EXISTS Operator Example

I am using the database for all examples. It is available on my Github public repo


SELECT contact_name
FROM suppliers
WHERE EXISTS (SELECT product_name 
			  FROM products 
			  WHERE products.supplier_id = suppliers.supplier_id 
			  AND unit_price < 20);

//Output
contact_name
"Charlotte Cooper"
"Shelley Burke"
"Yoshi Nagase"
...
...
24 rows

Let’s divide the whole query into 2 parts.

Part 1: Postgres will run subquery for each and every row and check if it returns TRUE. If so then it will return the name of the supplier otherwise it will skip it.

Part 2: The subquery: We are getting the names of the products of our suppliers (from suppliers table) which has price of 20 or less. We are using correlated subquery so we can’t just use subquery to get the products just in this case.

Postgres NOT EXISTS Operator Example


SELECT contact_name
FROM suppliers
WHERE NOT EXISTS (SELECT product_name 
			  FROM products 
			  WHERE products.supplier_id = suppliers.supplier_id 
			  AND unit_price < 20);

//Output
contact_name
"Regina Murphy"
"Antonio del Valle Saavedra"
"Sven Petersen"
"Eliane Noz"
"Chantal Goulet"

We are getting the opposite result set than previous query.

What if we add NULL to subquery?

Postgres EXISTS Operator with NULL input Example


SELECT contact_name
FROM suppliers
WHERE  EXISTS (SELECT NULL);

or

SELECT contact_name
FROM suppliers;

//Same output
contact_name
"Charlotte Cooper"
"Shelley Burke"
"Regina Murphy"
"Yoshi Nagase"
...
...
29 rows

Credit: Postgres functions subquery

« Previous Next »