PostgreSQL Tutorial: EXISTS

Summary: in this tutorial, you will learn how to use the PostgreSQL EXISTS operator to test for existence of rows in a subquery.

Table of Contents

Introduction to PostgreSQL EXISTS operator

The EXISTS operator is a boolean operator that tests for existence of rows in a subquery.

The following illustrates syntax of the EXISTS operator:

EXISTS (subquery)

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.

The 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 customerand 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

The 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 NULL, 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 customer table.

In this tutorial, you have learned how to use the PostgreSQL EXISTS to test for the existence of rows in the subquery.