August 3, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL
EXISTS operator to test for existence of rows in a subquery.
Introduction to PostgreSQL EXISTS operator
EXISTS operator is a boolean operator that tests for existence of rows in a subquery.
The following illustrates syntax of the
EXISTS accepts an argument which is a subquery.
If the subquery returns at least one row, the result of
EXISTS is true. In case the subquery returns no row, the result is of
EXISTS is false.
EXISTS operator is often used with the correlated subquery.
The result of
EXISTS operator depends on whether any row returned by the subquery, and not on the row contents. Therefore, columns that appear on the
SELECT clause of the subquery are not important.
For this reason, the common coding convention is to write
EXISTS in the following form:
SELECT column1 FROM table_1 WHERE EXISTS( SELECT 1 FROM table_2 WHERE column_2 = table_1.column_1);
Note that if the subquery returns NULL, the result of
EXISTS is true.
PostgreSQL EXISTS examples
We will use the following
payment tables in the sample database for the demonstration:
A) Find customers who have at least one payment whose amount is greater than 11.
The following statement returns customers who have paid at least one rental with an amount greater than 11:
SELECT first_name, last_name FROM customer c WHERE EXISTS (SELECT 1 FROM payment p WHERE p.customer_id = c.customer_id AND amount > 11 ) ORDER BY first_name, last_name;
The query returns the following output:
In this example, for each customer in the
customer table, the subquery checks the
payment table to find if that customer made at least one payment (
p.customer_id = c.customer_id) and the amount is greater than 11 (
amount > 11).
B) NOT EXISTS example
NOT operator negates the result of the
EXISTS operator. The
NOT EXISTS is opposite to
EXISTS. It means that if the subquery returns no row, the
NOT EXISTS returns true. If the subquery returns one or more rows, the
NOT EXISTS returns false.
The following example returns customers have not made any payment that greater than 11.
SELECT first_name, last_name FROM customer c WHERE NOT EXISTS (SELECT 1 FROM payment p WHERE p.customer_id = c.customer_id AND amount > 11 ) ORDER BY first_name, last_name;
Here is the output:
C) EXISTS and NULL
If the subquery returns
EXISTS returns true. See the following example:
SELECT first_name, last_name FROM customer WHERE EXISTS( SELECT NULL ) ORDER BY first_name, last_name;
In this example, the subquery returned
NULL, therefore, the query returned all rows from the
In this tutorial, you have learned how to use the PostgreSQL
EXISTS to test for the existence of rows in the subquery.