YogeshChauhan . com

IN Operator in PostgreSQL

in Postgres on October 24, 2020

IN operator is used to check if a particular value is in a list of values. It is used with the WHERE clause.

Syntax:


value IN (value1, value2, value3)

That is just basic syntax. We can use it in many different scenarios.

Simple IN Query:

I am using the database for all examples. It is available on my Github public repo


SELECT product_name, unit_price 
FROM products 
WHERE product_name IN ('Chai', 'Chang', 'Konbu');

//Output
product_name.   unit_price 
"Chai"	18
"Chang"	19
"Konbu"	6

We can just add NOT infront of IN and it will give the opposite result set.

IN Query with NOT:


SELECT product_name, unit_price 
FROM products 
WHERE product_name NOT IN ('Chai', 'Chang', 'Konbu');

//Output 
product_name. unit_price 
"Aniseed Syrup"	10
"Chef Anton's Cajun Seasoning"	22
"Chef Anton's Gumbo Mix"	21.35
"Grandma's Boysenberry Spread"	25
"Uncle Bob's Organic Dried Pears"	30
...
...

We saw in this post that we can get the same results without using BETWEEN: PostgreSQL BETWEEN

We can get same results as above without using IN operator as well.

Let’s see.

Simple IN Query alternative:


SELECT product_name, unit_price 
FROM products 
WHERE product_name = 'Chai' 
OR product_name = 'Chang' 
OR product_name = 'Konbu';

//Output
product_name.   unit_price 
"Chai"    18
"Chang"    19
"Konbu"    6

IN Query with NOT alternative:


SELECT product_name, unit_price 
FROM products 
WHERE product_name <> 'Chai' 
OR product_name <> 'Chang' 
OR product_name <> 'Konbu';

//Output 
product_name. unit_price 
"Aniseed Syrup"    10
"Chef Anton's Cajun Seasoning"    22
"Chef Anton's Gumbo Mix"    21.35
"Grandma's Boysenberry Spread"    25
"Uncle Bob's Organic Dried Pears"    30
...
...

As we can see, in both of the alternatives, results are the same.

IN operator with Subquery:


SELECT product_name, unit_price 
FROM products 
WHERE product_name 
IN (SELECT product_name FROM products WHERE unit_price > 50);

//Output
product_name   unit_price 
"Mishi Kobe Niku"	97
"Carnarvon Tigers"	62.5
"Sir Rodney's Marmalade"	81

As we can see, all we need to do is to add a subquery in the IN bracket. We need to make sure that we are checking for the same column as in the subquery results otherwise we will get an error.

dreamhost

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to uninstall Cocoapods from the Mac OS? #4 How to add Read More Read Less Button using JavaScript? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

May 7 How to disable right click and drag and drop of images using jQuery? May 7 How to render Lists in React? May 7 What’s the difference between variables in CSS and SCSS (Sass)? May 7 How to define variables in SCSS (Sass)? May 7 How to show and hide an element on click in React? May 5 Use inline if to make a shorter conditional syntax in React

You might also like these

What are Null Sessions?MiscHow to Commit and Rollback Changes in SQL?SQL/MySQLHow to add today’s date in HTML input date value using JavaScript?JavaScriptINTERSECT and EXCEPT in PostgresPostgresWhat is Git?MiscHow to get category name using post id in WordPress?WordPress