EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL IN Operator
 

PostgreSQL IN Operator

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 6, 2023

PostgreSQL IN Operator

 

 

Definition of PostgreSQL IN Operator

PostgreSQL IN operator is used in a WHERE clause. We can reduce multiple OR conditions written in where clause with the help of the IN Operator. The PostgreSQL IN operator checks whether a given value is exist or not in the list of values provided. We can use the PostgreSQL IN operator in SELECT, UPDATE, INSERT, or DELETE SQL statements.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

The syntax of the PostgreSQL IN operator is as follows:

value IN (value1, value2, value3. ...)

Explanation:

The expression returns either true or false; it returns true if the value exists in the list of values provided, that is, value1, value2, and value3, etc.

We can provide the list of values of type numbers or strings. We can also use the SELECT statement as follows to retrieve the list of values from the result set of the defined SELECT statement.

value IN (SELECT value FROM table_name);

Explanation:

The SQL statement defined in the parentheses is called as a subquery. The subquery is the one that is the query nested in another query.

How IN Operator Works in PostgreSQL?

  • The PostgreSQL IN operator returns true if it finds if any value-defined IN condition exists in the defined list of values.
  • The PostgreSQL IN operator returns false if it does not find any of the value exists.
  • If we are using subquery with IN operator, then it finds value in the result set of sub-query; if it finds then return true, otherwise false.

Examples

Let’s create a table named ‘furniture’ in order to understand the examples:

Example #1 – Inserting Data in the Table

CREATE table furniture
(
furniture_id SERIAL PRIMARY KEY,
furniture_name VARCHAR (256) NOT null,
furniture_type VARCHAR (256) NOT null,
furniture_price int NULL
);

Now, insert some data in the furniture table in order to execute SQL statements.

INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',2500),
('Chair','Plastic',2000),
('Table','Wood',5000),
('Table','Plastic',4000),
('Sofa','Wood',10000),
('Sofa','Plastic',8000),
('Bed','Wood',15000),
('Bed','Plastic',13000);1

Illustrate the result of the above statement with the help of the following snapshot and the SELECT statement.

SELECT * FROM furniture;

PostgreSQL IN Operator-1.1

Example #2 – The PostgreSQL IN with Character Values

Now we will demonstrate the use of the PostgreSQL IN operator with character or string values. We have defined a list of two furniture names as ‘Chair’ and ‘Table’. Here we find whether any of these furniture names exist in the furniture_name column of the furniture table and returns the matched records of the furniture table as shown below.

SELECT *
FROM furniture
WHERE furniture_name IN ('Chair', 'Table');

Illustrate the result of the above statement with the help of the following snapshot.

PostgreSQL IN Operator-1.2

Example #3 – The PostgreSQL IN with Numeric Values

Now we will demonstrate the use of the PostgreSQL IN operator with numeric values. We have defined a list of two furniture prices of 2500, 4000, 10000, etc. Here we find whether any of these furniture prices exist in the furniture_price column of the furniture table and returns the matched records of the furniture table as shown below.

SELECT *
FROM furniture
WHERE furniture_price IN (2500, 4000, 10000);

Illustrate the result of the above statement with the help of the following snapshot.

Output-1.3

Example #4 – PostgreSQL IN with the Subquery

Now we will demonstrate the use of the PostgreSQL IN operator with sub-query written. We have written a sub-query that returns the furniture prices whose furniture_name is ‘Sofa’.

SELECT *
FROM furniture
WHERE furniture_price
IN (
SELECT
furniture_price
FROM
furniture
WHERE
furniture_name = 'Sofa'
);

Illustrate the result of the above statement with the help of the following snapshot.

Output-1.4

Example # 5 – PostgreSQL IN with NOT

Now we will demonstrate the use of PostgreSQL NOT IN operator with character or string values. We have defined a list of two furniture names as ‘Chair’ and ‘Table’. Here we find whether any of these furniture names exist in the furniture_name column of the furniture table, and it skips that row only if it matches and returns the other records of the furniture table as shown below.

SELECT *
FROM furniture
WHERE furniture_name NOT IN ('Chair', 'Table');

Illustrate the result of the above statement with the help of the following snapshot.

Output-1.5

Conclusion

We hope from the above article you have understood how to use the PostgreSQL IN and how the PostgreSQL IN condition works to find the existence from the list of values. Also, we have added some examples of PostgreSQL IN operator to understand it in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL IN Operator” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Creating Database in PostgreSQL
  2. PostgreSQL DISTINCT
  3. PostgreSQL Views
  4. Cursors in PostgreSQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW