Introduction to PostgreSQL EXISTS operator

TheEXISTS operator is a boolean operator that checks the existence of rows in asubquery.

Here’s the basic syntax of theEXISTS operator:

EXISTS (subquery)

Typically, you use theEXISTS operator in theWHERE clause of aSELECT statement:

SELECT  select_listFROM  table1WHERE  EXISTS(    SELECT      select_list    FROM      table2    WHERE      condition  );

If the subquery returns at least one row, theEXISTS operator returnstrue. If the subquery returns no row, theEXISTS returnsfalse.

Note that if the subquery returnsNULL, theEXISTS operator returnstrue.

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

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

SELECT  select_listFROM  table1WHERE  EXISTS(    SELECT      1    FROM      table2    WHERE      condition  );

To negate theEXISTS operator, you use theNOT EXISTS operator:

NOT EXISTS (subquery)

TheNOT EXISTS operator returnstrue if the subquery returns no row orfalse if the subquery returns at least one row.

In practice, you often use theEXISTS operator in conjunction with thecorrelated subqueries.

PostgreSQL EXISTS examples

We will use the followingcustomer andpayment tables in thesample database for the demonstration:

1) Basic EXISTS operator example

The following example uses theEXISTS operator to check if the payment value is zero exists in thepayment 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 theEXISTS operator to find customers who have paid at least one rental with an amount greater than 11:

SELECT  first_name,  last_nameFROM  customer cWHERE  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 thecustomer table, the subquery checks thepayment 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 theNOT EXISTS operator to find customers who have not made any payment more than 11.

SELECT  first_name,  last_nameFROM  customer cWHERE  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 thecustomers table because the subquery in theEXISTS operator returnsNULL:

SELECT  first_name,  last_nameFROM  customerWHERE  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 PostgreSQLEXISTS to check the existence of rows in a subquery.

Last updated onJuly 1, 2024

On this page

• Introduction to PostgreSQL EXISTS operator
• PostgreSQL EXISTS examples
• Summary