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 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 add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Is there a CSS parent selector?CSSIN Operator in PostgreSQLPostgresfor @each loop in SCSSSCSSHow to make a curtain slider using jQuery and CSS?CSSThe :last-of-type selectorCSSHow to create an empty array in Swift?Swift