In PostgreSQL, the IN operator is used with the collaboration of the WHERE clause to check the existence of a particular value in a list of values. If a match is found between a particular value and a list of given values, then the IN operator returns a Boolean value “true”.

This post will consider some examples to explain the working of the IN operator in PostgreSQL. So, let’s start!

How to Use IN Operator in PostgreSQL?

In PostgreSQL, if the targeted_val matches with the values of the given list, then the IN operator will return a true value. The OR operator, along with the equal to “=” operator, produces the same results as IN operator.

The basic syntax of the IN operator will look like this:

targeted_val IN (val_1,val_2,.., val_N)

Here in the above syntax, targeted_val is a value to be searched. While val_1, val_2, …, val_N represents the list of values to which the targeted_val will be compared.

The list values can be anything such as numbers, literals, result-set of the SELECT statement, or strings.

Let’s jump into the practical implementation of the Postgres IN operator:

Example 1: How to Use IN Operator in PostgreSQL?

Suppose we have a table named bike_details whose details are as follows:

SELECT * FROM bike_details;

Suppose we want to know the price of bikes whose ids are 5, 6, and 7. Then we will execute the following query:

SELECT * FROM bike_details
WHERE bike_id IN (5, 6, 7)
ORDER BY bike_id ASC;

The output verifies that the IN operator finds the perfect match and hence returns the record of selected bikes.

Example 2: How to Use NOT IN Operator in PostgreSQL?

The NOT IN operator is used to get contradictory results as compared to the IN operator. The NOT IN operator will produce the same result as the combination of not equal “<>” and “AND” operators does. The NOT IN operator will return all the records available in the list except the searched records:

SELECT * FROM bike_details
WHERE bike_id NOT IN (5, 6, 7)
ORDER BY bike_id ASC;

The output proved that the NOT IN operator fetched all the records except the targeted records (i.e. bike_id = 5, 6, and 7).

What is a Subquery in Postgres?

If there is a query within the parenthesis of IN operator, then it will be referred to as a subquery. The basic syntax of the IN operator with respect to the subquery will be as follows:

targeted_val IN (SELECT col_name FROM tab_name);

The snippet shows that a query is nested within another query, which is known as a subquery.

Example: How to Use an IN Operator With a Subquery in Postgres?

In this example, we will use the IN operator with a subquery to fetch all those bikes whose bike_color starts with “B”:

SELECT * FROM bike_details
WHERE bike_color IN (
SELECT bike_color FROM bike_details
WHERE (bike_color LIKE 'B%'));

The above query will execute in the following sequence:

- Firstly, the subquery will execute. It will fetch all those bikes whose bike_color starts with B.

- The result set will be passed to the outer query.

- Finally, the outer query will get executed.

- Consequently, the IN operator will produce the following output:

The output authenticates the working of IN operator with a subquery.

Conclusion

In PostgreSQL, the IN operator is used with the collaboration of the WHERE clause to check the existence of a particular value in a given list. The list values can be anything such as numbers, literals, result-set of the SELECT statement, or strings. The IN operator returns true if the targeted value is found in the given list of values. Specifying a NOT operator prior to the IN operator will produce contradictory results as compared to the IN operator. This write-up explained the working of IN operator with the help of some suitable examples.