PostgreSQL EXISTS Operator

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
  );Code language: SQL (Structured Query Language) (sql)

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
  );Code language: SQL (Structured Query Language) (sql)

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
  );Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 that is 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;Code language: SQL (Structured Query Language) (sql)

Here is the output:

first_name  |  last_name
-------------+--------------
 Aaron       | Selby
 Adam        | Gooch
 Adrian      | Clary
 Agnes       | Bishop
 Alan        | Kahn
...Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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.
Was this tutorial helpful ?