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

Introduction to PostgreSQL EXISTS operator

The EXISTS operator is a boolean operator that checks the existence of rows in a subquery.

Here’s the basic syntax of the EXISTS operator:

EXISTS (subquery)

Typically, you use the EXISTS operator in the WHERE clause of a SELECT statement:

SELECT  select_list FROM  table1 WHERE  EXISTS(  SELECT  select_list  FROM  table2  WHERE  condition  );

If the subquery returns at least one row, the EXISTS operator returns true. If the subquery returns no row, the EXISTS returns false.

Note that if the subquery returns NULL, the EXISTS operator returns true.

The result of EXISTS operator depends on whether any row is returned by the subquery, and not on the row contents. Therefore, columns that appear in the select_list of the subquery are not important.

For this reason, the common coding convention is to write EXISTS in the following form:

SELECT  select_list FROM  table1 WHERE  EXISTS(  SELECT  1  FROM  table2  WHERE  condition  );

To negate the EXISTS operator, you use the NOT EXISTS operator:

NOT EXISTS (subquery)

The NOT EXISTS operator returns true if the subquery returns no row or false if the subquery returns at least one row.

In practice, you often use the EXISTS operator in conjunction with the correlated subqueries.

PostgreSQL EXISTS examples

We will use the following customer and payment tables in the sample database for the demonstration:

customer and payment tables

1) Basic EXISTS operator example

The following example uses the EXISTS operator to check if the payment value is zero exists in the payment table:

SELECT  EXISTS(  SELECT  1  FROM  payment  WHERE  amount = 0  );

Output:

exists --------  t (1 row)

2) Using the EXISTS operator to check the existence of a row

The following example uses the EXISTS operator to find 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:

first_name | last_name ------------+-----------  Karen | Jackson  Kent | Arsenault  Nicholas | Barfield  Rosemary | Schmidt  Tanya | Gilbert  Terrance | Roush  Vanessa | Sims  Victoria | Gibson (8 rows)

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)

2) NOT EXISTS example

The following example uses the NOT EXISTS operator to find customers who have not made any payment more 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:

first_name | last_name -------------+--------------  Aaron | Selby  Adam | Gooch  Adrian | Clary  Agnes | Bishop  Alan | Kahn ...

3) EXISTS and NULL example

The following example returns all rows from the customers table because the subquery in the EXISTS operator returns NULL:

SELECT  first_name,  last_name FROM  customer WHERE  EXISTS(  SELECT NULL  ) ORDER BY  first_name,  last_name;

Output:

first_name | last_name -------------+--------------  Aaron | Selby  Adam | Gooch  Adrian | Clary  Agnes | Bishop ...

Summary

  • Use the PostgreSQL EXISTS to check the existence of rows in a subquery.