PostgreSQL EXISTS Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL EXISTS operator to check for the existence of rows returned by a subquery.

Getting Started with the PostgreSQL EXISTS Operator #

In PostgreSQL, the EXISTS operator allows you to check if a subquery returns at least one row. The EXISTS operator returns true if the subquery returns any rows or false otherwise.

Here’s the syntax of the EXISTS operator:

EXISTS (subquery)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

NOT EXISTS (subquery)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Typically, you’ll use the EXISTS operator in the WHERE clause of the SELECT, UPDATE, and DELETE statements.

Using the PostgreSQL EXISTS operator in SELECT statements #

The following example uses the EXISTS operator to find all products stored in at least one warehouse:

SELECT
  product_name
FROM
  products p
WHERE
  EXISTS (
    SELECT
      1
    FROM
      inventories i
    WHERE
      i.product_id = p.product_id
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name
---------------------------
 Sony Xperia 1 VI
 Samsung Galaxy Z Fold 5
 Samsung Galaxy Tab S9
 Apple iPad Pro 12.9
 Samsung Galaxy Buds Pro 2
 Apple Watch Series 9
 LG OLED TV C3
 Sony Bravia XR A95K
 LG G3 OLED
 Sony HT-A7000 Soundbar
 Dell XPS 15
 HP Spectre x360
 Lenovo ThinkPad X1 Carbon
 Apple iMac 24"Code language: plaintext (plaintext)

In this example, the subquery checks if the inventories table has any row whose product_id equals product_id in the products table:

SELECT
  1
FROM
  inventories i
WHERE
  i.product_id = p.product_idCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the subquery returns any rows, the EXISTS operator returns true, and the outer query includes the product in the result set.

Using the PostgreSQL EXISTS operator in UPDATE statements #

The following statement increases the prices of all products in the warehouse id 1 by 5%:

UPDATE products p
SET
  price = price * 1.05
WHERE
  EXISTS (
    SELECT
      1
    FROM
      inventories i
    WHERE
      i.product_id = p.product_id
      AND i.warehouse_id = 1
  ) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |        product_name        |  price  | brand_id | category_id
------------+----------------------------+---------+----------+-------------
          1 | Samsung Galaxy S24         | 1049.99 |        1 |           1
          4 | Xiaomi Mi 14               |  839.99 |        4 |           2
          7 | Apple iPhone 15 Pro Max    | 1364.99 |        2 |           3
         10 | Apple AirPods Pro 3        |  262.49 |        2 |           5
         13 | Samsung Galaxy Watch 6     |  367.49 |        1 |           6
         16 | Samsung QN900C Neo QLED    | 3149.99 |        1 |           8
         19 | Bose SoundLink Max         |  419.99 |        7 |           9
         22 | Microsoft Surface Laptop 5 | 1364.99 |        9 |          11
         25 | Dell Inspiron 27           | 1049.99 |        7 |          12

In this example, the subquery checks if the inventories table has any rows with the same product _id as the product_id in the products table and warehouse_id 1.

If the subquery produces at least one row, the EXISTS operator returns true, and the UPDATE statement updates the product price.

You’ll find SELECT 1 in a subquery with the EXISTS operator is a common practice.

The number 1 is a placeholder and doesn’t impact the query logic. Additionally, SELECT 1 is efficient because it avoids the overhead of selecting data from the table.

Using the PostgreSQL EXISTS operator in DELETE statements #

First, insert two new rows into the products table:

INSERT INTO
  products (product_name, price, brand_id, category_id)
VALUES
  ('Samsung Galaxy S25', 999.99, 1, 1),
  ('Apple iPhone 17', 1299.99, 2, 1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, delete the products that are not stored in any warehouse:

DELETE FROM products p
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      inventories i
    WHERE
      i.product_id = p.product_id
  ) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |    product_name    |  price  | brand_id | category_id
------------+--------------------+---------+----------+-------------
         26 | Samsung Galaxy S25 |  999.99 |        1 |           1
         27 | Apple iPhone 17    | 1299.99 |        2 |           1

In this example, the subquery checks if the inventories table has rows with the sample product_id as in the products table.

The NOT EXISTS becomes true if the subquery returns no rows, causing the DELETE statement to delete the product.

Summary #

• The PostgreSQL EXISTS operator returns true if the subquery produces any rows or false otherwise.
• Use the EXISTS operator to check based on data in the related table.

Quiz #