How to Use the PostgreSQL IN Operator

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re specifying conditions in a PostgreSQL query, you may need to check if a value can be found in a list of values. The PostgreSQL IN operator makes it easy to perform this type of check within a query and get the results you need. In this article, we’ll take a closer look at the PostgreSQL IN operator and review some examples to learn how it’s used.

Prerequisites

Before moving forward with this tutorial, make sure the following prerequisites are in place:

  • You’ll need to have a PostgreSQL database cluster installed on your local machine. Make sure you’re able to access it successfully.

  • If you need to determine the version number of your PostgreSQL installation, connect to Postgres and enter your specified database. Then. use the following SELECT query to view the version number:

1
SELECT VERSION();

The output will look something like this:

1
2
3
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

The PostgreSQL in Operator

The PostgreSQL IN operator is used in the WHERE clause of a query. You can use this operator to verify whether a value matches the values in a specified list.

The expression evaluates to TRUE if the given value matches some value in the set. The set of values can be made up of a sequence of numbers or strings; it can also be the result set of a nested SELECT statement.

IN Operator in PostgreSQL With Example

With an Integer Value

In our first example, we’ll look at a query that uses the PostgreSQL IN operator with integer values.

Let’s assume we have a table named items that contains the following records:

1
2
3
4
5
6
7
 item_id | item_name | item_quantity | item_price
---------+-----------+---------------+------------
     635 | Laptop    |             2 |      62521
     123 | Desktop   |             1 |      14500
     234 | Keyboard  |            10 |        500
     536 | Mouse     |            10 |        250
     956 | Printer   |             3 |       3000

We want to construct a query that only returns records with certain values for item_id. We can use the IN operator in our WHERE clause to accomplish this:

1
2
SELECT item_name, item_quantity, item_price FROM items WHERE
item_id IN (123, 234) ORDER BY item_price ASC;

The above query will return records where the item_id matches one of the values listed in the IN clause. The results will include the three columns item_name, item_quantity and item_price, and they will be sorted by item_price in ascending order.

1
2
3
4
 item_name | item_quantity | item_price
-----------+---------------+------------
 Keyboard  |            10 |        500
 Desktop   |             1 |      14500

We can create an equivalent query using the PostgreSQL OR operator:

1
2
SELECT item_name, item_quantity, item_price FROM items WHERE
item_id = 123 OR item_id = 234 ORDER BY item_price ASC;

The output will be the same:

1
2
3
4
 item_name | item_quantity | item_price
-----------+---------------+------------
 Keyboard  |            10 |        500
 Desktop   |             1 |      14500

With a Character Value

Next, let’s look at a PostgreSQL IN operator example that uses character values:

1
SELECT * FROM items WHERE item_name IN ('Laptop', 'Keyboard', 'Mouse');

The query shown above will return the following results:

1
2
3
4
5
 item_id | item_name | item_quantity | item_price
---------+-----------+---------------+------------
     635 | Laptop    |             2 |      62521
     234 | Keyboard  |            10 |        500
     536 | Mouse     |            10 |        250

Using the NOT in Operator

Now that we understand how to check to see if a value is found in a list of values, let’s consider the converse: What if we wanted to verify that a value is not found in a list of values? We can accomplish this by using the NOT IN operator:

1
SELECT * FROM items WHERE item_name NOT IN ('Laptop', 'Keyboard', 'Mouse');

The result would look something like this:

1
2
3
4
 item_id | item_name | item_quantity | item_price
---------+-----------+---------------+------------
     123 | Desktop   |             1 |      14500
     956 | Printer   |             3 |       3000

Conclusion

If you want to write effective PostgreSQL queries, you’ll need to know how to define and specify a variety of conditions. The Postgres IN operator is a powerful tool that allows you to check if a value matches any element in a list of values. In this article, we reviewed multiple examples of the PostgreSQL IN operator, and we even looked at its converse, the NOT IN operator. With our examples and instructions, you’ll be able to implement this operator in your own PostgreSQL queries. Jump to top

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.