{"id":599,"date":"2024-12-03T17:35:30","date_gmt":"2024-12-03T10:35:30","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=599"},"modified":"2025-01-22T09:04:11","modified_gmt":"2025-01-22T02:04:11","slug":"postgresql-exists","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-exists\/","title":{"rendered":"PostgreSQL EXISTS Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL EXISTS<\/code> operator to check for the existence of rows returned by a subquery.<\/p>\n\n\n\n

Getting Started with the PostgreSQL EXISTS Operator #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the EXISTS<\/code> operator allows you to check if a subquery<\/a> returns at least one row. The EXISTS<\/code> operator returns true<\/code> if the subquery returns any rows or false<\/code> otherwise.<\/p>\n\n\n\n

Here’s the syntax of the EXISTS<\/code> operator:<\/p>\n\n\n

EXISTS<\/span> (subquery)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To negate the result of the EXISTS<\/code> operator, you use the NOT<\/code> operator:<\/p>\n\n\n
NOT<\/span> EXISTS<\/span> (subquery)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using the PostgreSQL EXISTS operator in SELECT statements #<\/a><\/h2>\n\n\n\n
The following example uses the EXISTS<\/code> operator to find all products stored in at least one warehouse:<\/p>\n\n\n
SELECT<\/span>\n  product_name\nFROM<\/span>\n  products p\nWHERE<\/span>\n  EXISTS<\/span> (\n    SELECT<\/span>\n      1<\/span>\n    FROM<\/span>\n      inventories i\n    WHERE<\/span>\n      i.product_id = p.product_id\n  );<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
       product_name\n---------------------------\n Sony Xperia 1 VI\n Samsung Galaxy Z Fold 5\n Samsung Galaxy Tab S9\n Apple iPad Pro 12.9\n Samsung Galaxy Buds Pro 2\n Apple Watch Series 9\n LG OLED TV C3\n Sony Bravia XR A95K\n LG G3 OLED\n Sony HT-A7000 Soundbar\n Dell XPS 15\n HP Spectre x360\n Lenovo ThinkPad X1 Carbon\n Apple iMac 24\"<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the subquery checks if the inventories<\/code> table has any row whose product_id<\/code> equals product_id<\/code> in the products<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  1<\/span>\nFROM<\/span>\n  inventories i\nWHERE<\/span>\n  i.product_id = p.product_id<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the subquery returns any rows, the EXISTS<\/code> operator returns true, and the outer query includes the product in the result set.<\/p>\n\n\n\n
Using the PostgreSQL EXISTS operator in UPDATE statements #<\/a><\/h2>\n\n\n\n
The following statement increases the prices of all products in the warehouse id 1 by 5%:<\/p>\n\n\n
UPDATE<\/span> products p\nSET<\/span>\n  price = price * 1.05<\/span>\nWHERE<\/span>\n  EXISTS<\/span> (\n    SELECT<\/span>\n      1<\/span>\n    FROM<\/span>\n      inventories i\n    WHERE<\/span>\n      i.product_id = p.product_id\n      AND<\/span> i.warehouse_id = 1<\/span>\n  ) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 product_id |        product_name        |  price  | brand_id | category_id\n------------+----------------------------+---------+----------+-------------\n          1 | Samsung Galaxy S24         | 1049.99 |        1 |           1\n          4 | Xiaomi Mi 14               |  839.99 |        4 |           2\n          7 | Apple iPhone 15 Pro Max    | 1364.99 |        2 |           3\n         10 | Apple AirPods Pro 3        |  262.49 |        2 |           5\n         13 | Samsung Galaxy Watch 6     |  367.49 |        1 |           6\n         16 | Samsung QN900C Neo QLED    | 3149.99 |        1 |           8\n         19 | Bose SoundLink Max         |  419.99 |        7 |           9\n         22 | Microsoft Surface Laptop 5 | 1364.99 |        9 |          11\n         25 | Dell Inspiron 27           | 1049.99 |        7 |          12<\/code><\/span><\/pre>\n\n\n
In this example, the subquery checks if the inventories table has any rows with the same product _id<\/code> as the product_id<\/code> in the products<\/code> table and warehouse_id<\/code> 1.<\/p>\n\n\n\n
If the subquery produces at least one row, the EXISTS<\/code> operator returns true, and the UPDATE<\/code> statement updates the product price.<\/p>\n\n\n\n
You’ll find SELECT 1<\/code> in a subquery with the EXISTS<\/code> operator is a common practice.<\/p>\n\n\n\n
The number 1 is a placeholder and doesn’t impact the query logic. Additionally, SELECT 1<\/code> is efficient because it avoids the overhead of selecting data from the table.<\/p>\n\n\n\n
Using the PostgreSQL EXISTS operator in DELETE statements #<\/a><\/h2>\n\n\n\n
First, insert two new rows into the products<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (product_name, price, safety_stock, gross_weight, brand_id, category_id)\nVALUES<\/span>\n  ('Samsung Galaxy S25'<\/span>, 999.99<\/span>, 10<\/span>, 0.36<\/span>, 1<\/span>, 1<\/span>),\n  ('Apple iPhone 17'<\/span>, 1299.99<\/span>, 20<\/span>, 0.41<\/span>, 2<\/span>, 1<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, delete the products that are not stored in any warehouse:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> products p\nWHERE<\/span>\n  NOT<\/span> EXISTS<\/span> (\n    SELECT<\/span>\n      1<\/span>\n    FROM<\/span>\n      inventories i\n    WHERE<\/span>\n      i.product_id = p.product_id\n  ) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 product_id |    product_name    |  price  | brand_id | category_id\n------------+--------------------+---------+----------+-------------\n         26 | Samsung Galaxy S25 |  999.99 |        1 |           1\n         27 | Apple iPhone 17    | 1299.99 |        2 |           1<\/code><\/span><\/pre>\n\n\n
In this example, the subquery checks if the inventories<\/code> table has rows with the sample product_id<\/code> as in the products table.<\/p>\n\n\n\n
The NOT EXISTS<\/code> becomes true if the subquery returns no rows, causing the DELETE<\/code> statement to delete the product.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Typically, you’ll use the EXISTS<\/code> operator in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code> statements.<\/p>\n\n\n\n