PostgreSQL IN Operator: Filtering Data Using Multiple Values

In this tutorial, we will learn how to use the IN operator in PostgreSQL to filter the data. It is used with the WHERE clause to get the targeted rows by checking if the column value matches any value in the specified list. We will be learning some exciting stuff here, so hang in with us!

Introduction to PostgreSQL IN Operator

When you want to check if the column value matches with the value in the list, you can use the IN operator. For example, if you want to see the employees having the departments of HR, Engineering, Finance or Design.

Note that, the IN operator is always used with the WHERE clause because you have to define an expression to target particular rows.

The IN operator comes into the picture when you want to match the column value to multiple values. In this case, instead of using multiple OR conditions, you can use the IN operator with the list of values.

You can use the IN condition with SELECT, UPDATE as well as DELETE statement to find the targeted rows and proceed with the selected operation.

Let’s now see the syntax of the IN operator or IN expression.

Syntax of PostgreSQL IN Operator

You can specify the list of values as a static list as well as a dynamic list.

Syntax:

If you have a list of values that are static, use the following syntax.

WHERE valueToMatch IN (value1,value2,value.....);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the ‘valueToMatch’ will be matched with all the values in the list. If it matches with at least one value in the list, it returns true and that row is included in the result set.

If the list values are dynamic, use the following syntax.

WHERE valueToMatch IN (SELECT col_name FROM table_name);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the subquery might return multiple values to which the specified ‘valueToMatch’ value will be matched. The subquery is handy when you have a bunch of values that come from another table.

Now that we have seen the syntax of the IN operator, let’s see some examples.

Examples of PostgreSQL IN Operator

Before moving forward, let’s create two tables that have a one-to-many relationship.

-- Drop the existing Categories table
DROP TABLE Categories;

-- Categories table 
CREATE TABLE Categories (
    category_id serial PRIMARY KEY,
    category_name VARCHAR(100) UNIQUE
);

-- Insert into categories
INSERT INTO Categories (category_name)
VALUES
    ('Electronics'),
    ('Clothing'),
    ('Appliances'),
    ('Books'),
    ('Toys');

-- Drop the existing Products table
DROP TABLE Products;

-- Products table with the category_id
CREATE TABLE Products (
    product_id serial PRIMARY KEY,
    product_name VARCHAR(100) UNIQUE,
    price DECIMAL(10, 2),
    description TEXT,
    category_id INT REFERENCES Categories(category_id) -- Foreign key reference
);

-- Insert products with category IDs
INSERT INTO Products (product_name, price, description, category_id)
VALUES
    ('Smartphone X', 799.99, 'High-end smartphone with advanced features', 1), -- Category ID for Electronics
    ('Laptop A', 1299.99, 'Powerful laptop for professional use', 1),         -- Category ID for Electronics
    ('T-Shirt Red', 19.99, 'Casual red t-shirt for everyday wear', 2),       -- Category ID for Clothing
    ('Washing Machine', 599.99, 'Front-load washing machine with various settings', 3), -- Category ID for Appliances
    ('Book: The Great Novel', 29.99, 'Best-selling novel by a renowned author', 4),   -- Category ID for Books
    ('Toy Car', 14.99, 'Remote-controlled toy car for kids', 5),               -- Category ID for Toys
    ('Laptop B', 899.99, 'Compact and lightweight laptop for travel', 1),     -- Category ID for Electronics
    ('Smartphone Y', 699.99, 'Affordable smartphone with great camera', 1),   -- Category ID for Electronics
    ('T-Shirt Blue', 21.99, 'Blue t-shirt made from premium cotton', 2),      -- Category ID for Clothing
    ('Refrigerator', 799.99, 'Energy-efficient refrigerator with ample storage', 3); -- Category ID for Appliances
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created two tables – categories and products. Let’s see if the data has been inserted into these tables successfully.

Output:

Perfect! Let’s now see some cool examples of the IN operator.

Example 1: Using a Static List

Let’s say you have to display all the categories from the table having names in the given list. Here, we will fetch the rows having category names as – Electronics, Clothing, Grocery, Furniture, Books.

If the row value doesn’t match with any value in the list, that row is skipped. Note that, list values can be or can not be present in the table.

SELECT * FROM categories
WHERE category_name IN
('Electronics', 'Clothing', 'Grocery', 'Furniture', 'Books');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

As you can see, all the categories in the output match with at least one value in the list.

Example 2: Using a Subquery as a List

Now let’s say you want to get all the products that have the categories of the above query. For example, there are multiple products present in the products table having different categories, but you want only those products that are displayed in the above query’s output.

SELECT * FROM products
WHERE category_id IN
(SELECT category_id FROM categories
WHERE category_name IN 
('Electronics', 'Clothing', 'Grocery', 'Furniture', 'Books')
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, both the outer query and the subquery consist of the IN operator. The subquery will return the category_id of the categories which are present in the given list and the outer query will match the product’s category_id with the subquery’s output.

To understand it better, let’s break down it in simple terms. The subquery will execute first and check if the row’s category_name matches with any value in the given list.

We are only retrieving the category_id column, therefore the output in our case will be 1,2,4. Now, these values will be used as the list to the outer query and the outer query will check if each row’s category_id matches with the list of category_id which we got earlier.

And that’s it. All the products having category_id 1,2 or 4 will be returned as the output as shown below.

Output:

Example 3: Using NOT IN Operator

The NOT IN operator does exactly the opposite of the IN operator. Simply, the NOT IN operator will return all the rows that the IN operator skips. Let’s take an example.

SELECT * FROM categories
WHERE category_name NOT IN
('Electronics', 'Clothing', 'Books');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The categories table contains these three values along with other category values. However, this query will return all other categories that are not listed in the given list.

Output:

As you can see, the query returns all the categories that are not present in the specified list.

Conclusion

In this tutorial, we have learned about the IN operator in PostgreSQL which is used to filter the rows. The IN operator allows you to optimize the SQL query and make it more readable which improves the performance of the query by reducing the multiple conditional statements. Do not forget to use it next time when you need multiple OR conditions.

Reference

PostgreSQL official documentatio n on IN operator