PostgreSQL IN condition

Preamble

PostgreSQL IN condition is used to reduce the need to use multiple OR conditions in SELECT, INSERT, UPDATE, or DELETE.

The syntax for IN condition in PostgreSQL

expression IN (value1_id, value2_id,... value_n_id);

OR:

IN (subquery_id);

Parameters and arguments of the condition

• expression – Value to be checked.
• value1_id, value2_id, or value_n_id – Values for checking expression for compliance.
• subquery_id – This is the SELECT operator, whose set of results will be checked for compliance. If any of these values correspond to an expression, the IN condition will have the value true.

Note:

• PostgreSQL condition IN will return records with value1, value2… or value_n.
• The PostgreSQL condition IN is also called the PostgreSQL IN operator.

Example IN condition with characters

Let’s consider an example of PostgreSQL IN conditions using character values.

Below is a PostgreSQL statement SELECT which uses the IN condition to compare character values:

SELECT *
FROM suppls
WHERE suppl_name IN ('Apple', 'Samsung', 'Asus');

In this PostgreSQL example, the IN condition will return all rows from the supplier’s table where supplier_name equals “Apple”, “Samsung” or “Asus”. Since SELECT uses *, all fields from the supplier table will be displayed in the resulting set.

The above IN example is equivalent to the following SELECT operator:

SELECT *
FROM suppls
WHERE suppl_name = 'Apple'
OR suppl_name = 'Samsung'
OR suppl_name = 'Asus';

As you can see, using PostgreSQL condition IN makes the operator easier to read and more efficient.

Example of a condition with numbers

Next, Let’s look at an example of PostgreSQL IN conditions using numeric values.
For example:

SELECT *
FROM empls
WHERE empl_id IN (300, 301, 500, 501);

This example of the PostgreSQL IN condition will return all records from the employee’s table for which employee_id is 300, 301, 500, or 501.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM empls
WHERE empl_id = 300
OR empl_id = 301
OR empl_id = 500
OR empl_id = 501;

Example of a condition to using NOT operator

Finally, let us consider an example of the IN condition using the NOT operator.
For example:

SELECT *
FROM suppls
WHERE suppl_name NOT IN ('Apple', 'Samsung', 'Asus');

This example of PostgreSQL condition IN would return all rows from the supplier’s table where supplier_name is not “Apple”, “Samsung” or “Asus”. Sometimes it is more efficient to list the values that you don’t want, as opposed to the values that you want.

PostgreSQL Tutorial for Beginners – PostgreSQL IN Condition

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.