PostgreSQL WHERE IN with examples

In this PostgreSQL tutorial, we will learn about PostgreSQL WHERE IN. IN is used to check a value from a list of values, i.e., it is used to retrieve data according to multiple values in a column.

PostgreSQL WHERE IN
PostgreSQL WHERE IN Array
PostgreSQL WHERE IN Subquery
PostgreSQL WHERE IN JSON Array
PostgreSQL WHERE IN String
PostgreSQL WHERE IN JSON
PostgreSQL WHERE IN String Array

Table of Contents

PostgreSQL WHERE IN

In this section, we will learn about PostgreSQL WHERE IN. IN is used to check values from a list of values, i.e., it is used to retrieve data according to multiple values in a column. IN is used with the WHERE clause in PostgreSQL.

We will look at an example for PostgreSQL WHERE IN to filter data according to multiple values.

First, connect to the database using \c the command and create a table in PostgreSQL.

\c sqlserverguides

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

PostgreSQL WHERE IN — PostgreSQL- Create Table

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

PostgreSQL WHERE IN example — PostgreSQL- Insert Data

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

How to use PostgreSQL WHERE IN — PostgreSQL- Data inside Table

Now we will use the IN operator in WHERE clause like this:

SELECT *
FROM Employee
WHERE Salary IN ('2500','2700','3000');

Here we have retrieved the data from the table according to the three values from a column.

In this section, we learned about PostgreSQL WHERE IN.

Read: PostgreSQL DATE Format

PostgreSQL WHERE IN Array

In this section, we will learn about PostgreSQL WHERE IN in Array. We have already learned that how to use the WHERE clause in arrays in PostgreSQL. This section will focus on the IN operator used with the WHERE clause in an Array.

We can use the IN operator with an array to check values from a list of values. Let us look at an example.

First, connect to the database using \c command along with the name of the database:

\c sqlserverguides

Now create the table using these lines of code, having one of the columns as an integer array datatype:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE,
Phone integer[]);

PostgreSQL WHERE IN Array — PostgreSQL- Create Table Array

Now add some data in the table using the INSERT statement:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25','{856741356,857412589}');

INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12','{745128963,745894172}');

INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21','{874125963,879654125}');

INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10','{985412477,985632147}');

INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16','{654781239,657412893}');

INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07','{745812396,745369812}');

INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13','{587412896,578941247}');

INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05','{874596321,879456142}');

INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24','{547812369,657412893}');

INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23','{978451236,941125367}');

PostgreSQL Data Inserted in Table Array — PostgreSQL- Data Inserted in Table Array

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now we will use the IN operator in the WHERE clause to check values from a list of values:

SELECT *
FROM Employee
WHERE Phone[1] IN (856741356,985412477,978451236);

In this example, we have searched for the phone number which is an integer type array using the IN operator to check from a list of values.

In this section, we learned about PostgreSQL WHERE IN in Array.

Read: PostgreSQL CASE with Examples

PostgreSQL WHERE IN Subquery

In this section, we will learn about PostgreSQL WHERE IN in Subquery. We can use the WHERE clause to filter data according to a particular condition, now this condition can be a subquery as well, i.e., a SELECT statement inside another SELECT statement.

Using the IN operator we can select from a list of subqueries as well. We will have a look at two examples, one explaining the Subqueries in PostgreSQL and the other explaining PostgreSQL WHERE IN in Subquery.

First, connect to the database using \c command and the name of the database:

\c sqlserverguides

Then create a table using these lines of code:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

PostgreSQL WHERE IN Subquery — PostgreSQL- Create Table

Now add some data using the INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;

PostgreSQL WHERE IN Subquery example — PostgreSQL- Data inside Table

Now, we will use these lines of code to see the implementation of SELECT Subquery in PostgreSQL:

SELECT * 
FROM Employee
WHERE Salary = (
SELECT Salary
FROM Employee
WHERE Date_Of_Joining = '2018-08-12');

This is an example of a subquery in PostgreSQL where we filter data according to a condition based on the result from a SELECT statement.

Now we will learn about the WHERE IN operator along with subquery, i.e., we will filter data from a list of values obtained from various subqueries. The code to implement PostgreSQL WHERE IN In Subquery is:

SELECT * 
FROM Employee
WHERE Salary IN ((
SELECT Salary
FROM Employee
WHERE Date_Of_Joining = '2018-08-12'),
(SELECT Salary
FROM Employee
WHERE Date_Of_Joining = '2017-06-10'),
(SELECT Salary
FROM Employee
WHERE Date_Of_Joining = '2016-05-25'));

In this example, we used the PostgreSQL IN operator to check against a list of values provided by the different subqueries.

In this section, we learned about PostgreSQL WHERE IN in Subquery.

Read: How to Restart PostgreSQL (Linux, Windows, Mac)

PostgreSQL WHERE IN JSON Array

In this section, we will learn about PostgreSQL WHERE IN JSON Array. IN operator can be used to check against a list of values that can be of JSON Array Data Type.

An array consisting of JSON values, i.e., key-value pairs, is known as a JSON array. We will look at an example of PostgreSQL WHERE IN JSON Array.

First, connect to the database using \c command and the name of the database:

\c sqlserverguides

Now create a table using these lines of code consisting of one column as JSON array Data Type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE,
Address JSON[]);

PostgreSQL WHERE IN Create Table Json Array — PostgreSQL WHERE IN- Create Table JSON Array

Now we will add some data to the table using INSERT Statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25',array['{"Lane":"6649 N Blue Gum St","City":"New Orleans"}','{"Lane":"228 Runamuck Pl #2808","City":"Baltimore"}']::json[]);

INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12',array['{"Lane":"4 B Blue Ridge Blvd","City":"Brighton"}','{"Lane":"5 Boston Ave #88","City":"Sioux Falls"}']::json[]);

INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21',array['{"Lane":"8 W Cerritos Ave #54","City":"Bridgeport"}','{"Lane":"5 Boston Ave #88","City":"Sioux Falls"}']::json[]);

INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10',array['{"Lane":"639 Main St","City":"Anchorage"}','{"Lane":"7 W Jackson Blvd","City":"San Jose"}']::json[]);

INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16',array['{"Lane":"34 Center St","City":"Hamilton"}','{"Lane":"3 Mcauley Dr","City":"Ashland"}']::json[]);

INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07',array['{"Lane":"3 Mcauley Dr","City":"Ashland"}','{"Lane":"34 Center St","City":"Hamilton"}']::json[]);

INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13',array['{"Lane":"7 Eads St","City":"Chicago"}','{"Lane":"3 Mcauley Dr","City":"Ashland"}']::json[]);

INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05',array['{"Lane":"7 W Jackson Blvd","City":"San Jose"}','{"Lane":"639 Main St","City":"Anchorage"}']::json[]);

INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24',array['{"Lane":"5 Boston Ave #88","City":"Sioux Falls"}','{"Lane":"4 B Blue Ridge Blvd","City":"Brighton"}']::json[]);

INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23',array['{"Lane":"228 Runamuck Pl #2808","City":"Baltimore"}','{"Lane":"6649 N Blue Gum St","City":"New Orleans"}']::json[]);

PostgreSQL WHERE IN Insert Data Json Array — PostgreSQL WHERE IN- Insert Data JSON Array

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

PostgreSQL WHERE IN JSON Array — PostgreSQL WHERE IN- Data Inserted JSON Array

Now we will use the PostgreSQL WHERE IN operator to check from a list of JSON Array values:

SELECT * 
FROM Employee
WHERE Address[2] ->> 'City' IN ('Baltimore','Anchorage','New Orleans');

In this example, we have searched for those employees, whose Second city in the Address field matches any value provided using the IN operator.

In this section, we learned about PostgreSQL WHERE IN JSON Array.

Read: How to Uninstall PostgreSQL (Linux, Mac, and Windows)

PostgreSQL WHERE IN String

In this section, we will learn about WHERE IN in String Datatype. We can use the IN operator in PostgreSQL to filter data according to a list of values which can be of string data type as well. We will look at an example of where to use the IN operator in PostgreSQL with String data type.

First, connect to the database using \c command and the name of the database in PostgreSQL:

\c sqlserverguides

Now create a table in PostgreSQL:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

PostgreSQL WHERE IN String — PostgreSQL- Create Table

Now add some data in the table using INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT*
FROM Employee;

WHERE IN String in PostgreSQL — PostgreSQL- Data inside Table

Now we will use the WHERE IN operator to check against a list of values for string data as follows:

SELECT *
FROM Employee
WHERE Name IN ('Amber','Chanel','Simona');

PostgreSQL WHERE IN String example — PostgreSQL- WHERE IN String

In this example, we have a column Name of String Data Type, i.e., TEXT, and we have used IN operator to look for names from a list of three names.

In this section, we learned about PostgreSQL WHERE IN String.

Read: How to connect to PostgreSQL database

PostgreSQL WHERE IN JSON

In this section, we will learn about PostgreSQL WHERE IN in JSON, i.e., we can check against a list of values that can be of JSON data type. We have already learned about the PostgreSQL WHERE clause in JSON in our previous blog. We will see an example of the PostgreSQL IN operator for JSON data type.

First, connect to the database using \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having one of the columns as JSON Data Type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE,
Address JSON);

PostgreSQL WHERE IN JSON — PostgreSQL Create Table JSON

Now we will add some data in the table using INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25','{"Lane":"6649 N Blue Gum St","City":"New Orleans"}');

INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12','{"Lane":"4 B Blue Ridge Blvd","City":"Brighton"}');

INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21','{"Lane":"8 W Cerritos Ave #54","City":"Bridgeport"}');

INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10','{"Lane":"639 Main St","City":"Anchorage"}');

INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16','{"Lane":"34 Center St","City":"Hamilton"}');

INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07','{"Lane":"3 Mcauley Dr","City":"Ashland"}');
 
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13','{"Lane":"7 Eads St","City":"Chicago"}');

INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05','{"Lane":"7 W Jackson Blvd","City":"San Jose"}');

INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24','{"Lane":"5 Boston Ave #88","City":"Sioux Falls"}');

INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23','{"Lane":"228 Runamuck Pl #2808","City":"Baltimore"}');

We have taken Address as JSON data type and provided two key-value pairs: Lane and City.

WHERE IN JSON in PostgreSQL — PostgreSQL- Data Inserted in Table

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now we will use the IN operator in PostgreSQL to filter data according to a list of values from a JSON column:

SELECT * 
FROM Employee
WHERE Address ->> 'City' IN ('Ashland','San Jose','Brighton');

In this example, we have used the IN operator to select the city in the address column from a list of cities.

In this section, we learned about PostgreSQL WHERE IN in JSON.

Read: How to create a table in PostgreSQL [Terminal + pgAdmin]

PostgreSQL WHERE IN String Array

In this section, we will learn about PostgreSQL WHERE IN String Array. IN operator in PostgreSQL is used to check a value from a list of values, so we can use the IN operator to check from a list of string array values.

We will look at an example where we will have a column with string array data type and we will use the IN operator in WHERE clause to check a certain value from a list of values.

First, connect to the database using \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having one of the columns as String Array Data Type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text[] NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

PostgreSQL WHERE IN Create Table String Array — PostgreSQL WHERE IN- Create Table String Array

Now add some data into the table using INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'{James,Butt}','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'{Simona,Tollner}','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'{Kiley,Glick}','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'{Fletcher,Corrio}','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'{Alisha,Gillian}','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'{Chanel,Nicka}','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'{Erick,Maclead}','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'{Amber,Rim}','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'{Stephen,Caudy}','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'{Jose,Stockham}','HR',3700,'2016-01-23');

PostgreSQL WHERE IN String Array — PostgreSQL WHERE IN- Insert Data String Array

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;

PostgreSQL WHERE IN String Array example — PostgreSQL WHERE IN- Data Inserted Successfully

Now we will use the IN operator in PostgreSQL to check from a list of values:

SELECT * 
FROM Employee
WHERE Name[2] IN ('Nicka','Rim','Tollner');

Here in this example, we have used the IN operator in PostgreSQL to filter data according to the list of Last Names of the employee.

Watch my YouTube video for a better understanding of the topic:

You may like the following PostgreSQL tutorials:

In this tutorial, we learned about PostgreSQL WHERE IN. Also, we have covered these topics:

PostgreSQL WHERE IN
PostgreSQL WHERE IN Array
PostgreSQL WHERE IN Subquery
PostgreSQL WHERE IN JSON Array
PostgreSQL WHERE IN String
PostgreSQL WHERE IN JSON
PostgreSQL WHERE IN String Array

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.