Yogesh Chauhan's Blog

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.


Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
2 Ways we can create an Array in JavaScriptJavaScriptJavaScript String Properties and MethodsJavaScriptWhat’s a Web Storage API in JavaScript?JavaScriptHow to progressively load images and add a blurry placeholder?CSSSimple Page Hit Counter in PHPPHPAND, OR and NOT boolean operators in Envision BasicEnvision Basic