PostgreSQL IN Condition

In this section, we are going to understand the working of PostgreSQL IN condition, which is used with WHERE clause to fetch data from a table where defined condition satisfies the IN condition. Examples of IN operator, and IN operator with a subquery.

Introduction of PostgreSQL IN condition

The PostgreSQL IN condition is used within the WHERE clause to get those data that matches any data in a list. In other words, we can say that the IN condition is used to reduce multiple OR conditions.

PostgreSQL IN Condition Syntax

In PostgreSQL, the IN condition can be used with the SELECT, INSERT, UPDATE, and DELETE commands.

Expression IN (SELECT column_name FROM table_name);

OR

expression IN (value1, value2, .... valueN);  

We can write the above syntax below because inside the parentheses is known as a subquery that is a statement nested inside another statement.

Expression IN (subquery);

In the above syntax, we have used the following parameters:

Note:

• The PostgreSQL IN condition will return true if the value matches any value in the given list, which is value1, value2 ,....valueN,, and these lists of value can be a list of literal values. For example, string, numbers, or an output of a SELECT command.

Examples of PostgreSQL IN Condition

Let us see different examples to understand how the PostgreSQL IN condition works:

Example of PostgreSQL IN Condition: with Character values

For this, we are taking the employee table to get the employee information of emp_fname having John and Ava employees.

We are using the IN operator with WHERE clause as we can see the following command:

SELECT *
FROM employee
WHERE emp_fname IN ('John', 'Ava')
ORDER BY emp_id DESC;

Output

On executing the above command, we will get the following result:

In the above example, the PostgreSQL IN condition will return all rows from the employee table where the emp_fname is 'John' and 'Ava'.

In the above command, we use the (*) in the SELECT, which means that all fields from the employee table will be displayed in the output.

In the below command, we are using the OR and equal (=) operators in place of the IN Operator. And the statement is equivalent to the above command:

SELECT *
FROM employee
WHERE emp_fname ='John' OR emp_fname ='Ava'
ORDER BY emp_id DESC;

Output

After successful executing of the above command, we will get the following result:

As we can see in the above images, both the outputs are providing similar results, but using the PostgreSQL IN condition makes the command more comfortable to read and more efficient as compared to OR and Equal (=) Operators.

Example of PostgreSQL IN condition: with Numeric values

To see PostgreSQL IN operator examples, we will take the department table from the TheDeveloperBlog database.

Suppose, we want to know the department information of emp_id 1,2, and 3. So, for this, we are using the IN operator in the WHERE clause as we can see the following command:

SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id IN (1, 2,3)
ORDER BY dept_name DESC;

Output

After executing the above command, we will get the following output:

In the above command, we can also use the OR and equal (=) operators in place of the IN Operator. And the statement is equivalent to the above command:

SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id=1 OR emp_id=2 OR emp_id=3
ORDER BY dept_name DESC;

Output

After implementing the above statement, we will get the following result:

After implementing both the commands, we clearly see that the command where we use the IN operator is more readable, and short as compared to the command where we use the OR and Equal (=) Operators.

In other words, we can say that PostgreSQL implements the command with the IN operator faster than using the lists of OR operators.

Example of PostgreSQL IN with a subquery

In the following command, we will use the CAST() function, which changes a value of any type into the specified datatype.

In the below example, the CAST() is used to convert the Joining_date AS Date datatype returns a list of emp_id from the department table where the Joining_date is 2020-06-22:

SELECT emp_id
FROM department
WHERE CAST (Joining_date AS Date) = '2020-06-22'
ORDER BY emp_id;

Output

After executing the above command, we will get the following result:

As we can in the above screenshot that the command returns a list of values, we can use it as the input of the IN Operator as shown in the following command:

SELECT emp_id, emp_fname, emp_lname
FROM employee
WHERE emp_id IN (
		SELECT emp_id
		FROM department
		WHERE CAST (Joining_date AS DATE) = '2020-06-22'
)
ORDER BY emp_id;

Output

After executing the above command, we will get the following result:

Overview

In the PostgreSQL IN Condition section, we have learned the following topics:

• The use Of PostgreSQL IN operator to find if a value matches any value in a list of values.
• We used the IN Condition to fetch the Character from the particular table.
• We used the IN Condition to fetch the Numeric from the particular table.
• We used the subquery concept with the PostgreSQL IN Operator to get the values from two tables.