PostgreSQL EXISTS

In PostgreSQL, the EXISTS operator is used to determine whether a subquery returns rows. If the subquery returns at least one row, EXISTS returns true, otherwise returns false.

PostgreSQL EXISTS syntax

The PostgreSQL EXISTS operator is used to construct conditions in WHERE clauses, and the syntax is:

WHERE EXISTS(subquery);

Explanation:

• The EXISTS is usually used in WHERE clauses .
• The EXISTS is a unary operator that takes a subquery subquery as an argument.
• If the subquery subquery returns at least one row (regardless of whether the value in the row is NULL), EXISTS returns TRUE, otherwise returns FALSE.
• When evaluating EXISTS, the EXISTS operation returns once the subquery finds a matching row. This is very helpful for improving query performance.
• The EXISTS doesn’t care about the number or names of columns in the subquery, it only cares if the subquery returns rows. So in the subquery of EXISTS, whether you use SELECT 1 or SELECT *, or SELECT column_list, does not affect the result of the EXISTS operation.
• The NOT EXISTS is the negation of EXISTS.

PostgreSQL EXISTS example

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

Example 1

In the Sakila sample database, all films are stored in the film table, and the inventory information for the films is stored in the inventory table. There is a one-to-many relationship between the film table and the inventory table, that is, a film may have multiple inventory information.

To find the number of films that have inventory records from the film table, use the following statement:

SELECT
    film_id,
    title
FROM film f
WHERE
    EXISTS (
        SELECT 1
        FROM inventory i
        WHERE i.film_id = f.film_id
    );

 film_id |            title
---------+-----------------------------
       1 | ACADEMY DINOSAUR
       2 | ACE GOLDFINGER
       3 | ADAPTATION HOLES
       4 | AFFAIR PREJUDICE
       5 | AFRICAN EGG
       6 | AGENT TRUMAN
       7 | AIRPLANE SIERRA
...
     999 | ZOOLANDER FICTION
    1000 | ZORRO ARK
(958 rows)

Here, for each film (that is, each row) in the film table, the subquery checks inventory to see if there is an inventory record for that film (i.film_id = f.film_id).

To find films without inventory records from the film table, use the following statement:

SELECT
    film_id,
    title
FROM film f
WHERE
    NOT EXISTS (
        SELECT 1
        FROM inventory i
        WHERE i.film_id = f.film_id
    );

 film_id |         title
---------+------------------------
      14 | ALICE FANTASIA
      33 | APOLLO TEEN
      36 | ARGONAUTS TOWN
      38 | ARK RIDGEMONT
      41 | ARSENIC INDEPENDENCE
      87 | BOONDOCK BALLROOM
...
     954 | WAKE JAWS
     955 | WALLS ARTIST
(42 rows)

Example 2

In the Sakila sample database, the customer’s information is stored in the customer table, and the customer’s payment records are stored in the payment table. There is a one-to-many relationship between the customer table and the payment table, that is, there may be multiple payment records for a customer.

To find customers who paid at least once with an amount greater than 11 from the customer table, use the following statement:

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;

 first_name | last_name
------------+-----------
 ALMA       | AUSTIN
 KAREN      | JACKSON
 KENT       | ARSENAULT
 NICHOLAS   | BARFIELD
 RICHARD    | MCCRARY
 ROSEMARY   | SCHMIDT
 TANYA      | GILBERT
 TERRANCE   | ROUSH
 VANESSA    | SIMS
 VICTORIA   | GIBSON

Conclusion

In PostgreSQL, the EXISTS operator is used to determine whether a subquery returns rows.