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.
OR
We can write the above syntax below because inside the parentheses is known as a subquery that is a statement nested inside another statement.
In the above syntax, we have used the following parameters:
| Parameter | Description |
|---|---|
| Expression/ value | It is used to define a column or field. |
| value1, value2, …. valueN | If any of these values match the expression, then the IN condition will return true. It is a quick method to assess if any one of the values matches the expression. |
| Subquery | It is a SELECT command where the output will be checked in contradiction of expression. |
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:
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:
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 tutoraspire 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:
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:
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:
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:
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.