PostgreSQL WHERE IN Subquery

Summary: in this tutorial, you’ll explore using the PostgreSQL WHERE IN subquery to filter rows based on a list of values returned by the subquery.

Getting Started with the PostgreSQL WHERE IN subquery #

In PostgreSQL, the WHERE IN subquery allows you to check if a value is in a set of values returned by subquery.

Here’s the syntax for using the WHERE IN subquery in the SELECT statement:

SELECT select_list
FROM table_name
WHERE column_name IN (subquery);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the column name in the WHERE clause with the IN operator to filter rows.
Second, place a subquery inside parentheses () to supply values to the IN operator.

The query returns a result set with the values in the column_name matching any value returned by the subquery.

If the subquery returns no rows, the outer query will also return an empty result set.

PostgreSQL WHERE IN subquery example #

The following example uses the WHERE IN subquery to find all products stored in a warehouse with id 1:

SELECT
  product_name
FROM
  products
WHERE
  product_id IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      warehouse_id = 1
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name
----------------------------
 Xiaomi Mi 14
 Apple iPhone 15 Pro Max
 Apple AirPods Pro 3
 Samsung Galaxy Watch 6
 Samsung QN900C Neo QLED
 Bose SoundLink Max
 Microsoft Surface Laptop 5
 Dell Inspiron 27Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

First, the subquery retrieves a list of product_id stored in the warehouse id 1.
The outer query selects the products where product_id matches any product_id returned by the subquery.

PostgreSQL WHERE NOT IN subquery example #

You can use the NOT IN operator to filter rows that are not in a list returned by a query.

The following statment uses the WHERE NOT IN subquery to find all products that are not stored in a warehouse id 1:

SELECT
  product_name
FROM
  products
WHERE
  product_id NOT IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      warehouse_id = 1
  );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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using PostgreSQL WHERE NOT IN with the UPDATE statement #

The following statement shows how to use the WHERE IN subquery in the UPDATE statement to filter rows for updating:

UPDATE table_name
SET column2 = value2,
    column3 = value3,
    ...
WHERE column1 IN (subquery);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The UPDATE statement will update rows whose values in column1 match any value returned by the subquery.

If there is no match or the subquery returns now row, the UPDATE statement will not update any rows.

The following example decreases the prices of products stored in the warehouse id 1 by 10%:

UPDATE products
SET
  price = price * 0.9
WHERE
  product_id IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      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
------------+----------------------------+---------+----------+-------------
          4 | Xiaomi Mi 14               |  719.99 |        4 |           2
          7 | Apple iPhone 15 Pro Max    | 1169.99 |        2 |           3
         10 | Apple AirPods Pro 3        |  224.99 |        2 |           5
         13 | Samsung Galaxy Watch 6     |  314.99 |        1 |           6
         16 | Samsung QN900C Neo QLED    | 2699.99 |        1 |           8
         19 | Bose SoundLink Max         |  359.99 |        7 |           9
         22 | Microsoft Surface Laptop 5 | 1169.99 |        9 |          11
         25 | Dell Inspiron 27           |  899.99 |        7 |          12Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

The subquery retrieves a list of product_id stored in the warehouse id 1.
The UPDATE statement then decreases the price of the selected products by 10%.

Using WHERE IN subquery with the DELETE statement #

The following shows how to use the WHERE IN subquery in the DELETE statement to select rows for deletion:

DELETE FROM table_name
WHERE column1 IN (subquery);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This DELETE statement will delete rows with values in column1 matching any value returned by the subquery.

If the subquery returns no row or there are no matches, the DELETE statement will not delete any rows.

The following example shows how to delete the products stored in the warehouse id 1:

DELETE FROM products
WHERE
  product_id IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      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
------------+----------------------------+---------+----------+-------------
          4 | Xiaomi Mi 14               |  719.99 |        4 |           2
          7 | Apple iPhone 15 Pro Max    | 1169.99 |        2 |           3
         10 | Apple AirPods Pro 3        |  224.99 |        2 |           5
         13 | Samsung Galaxy Watch 6     |  314.99 |        1 |           6
         16 | Samsung QN900C Neo QLED    | 2699.99 |        1 |           8
         19 | Bose SoundLink Max         |  359.99 |        7 |           9
         22 | Microsoft Surface Laptop 5 | 1169.99 |        9 |          11
         25 | Dell Inspiron 27           |  899.99 |        7 |          12Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

The subquery retrieves all product_id stored in warehouse 1.
The DELETE statement removes all products with those product_id from the products table.

Summary #

Use the WHERE IN subquery to filter rows based on dynamic sets of values.

Quiz #

Was this tutorial helpful ?