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 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #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

#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 #Jan 14 How to display a student’s individual transcript in Colleague? #Jan 11 How to install PuTTY on a MacOS?
You might also like these
bin2hex() and chr() String Functions in PHPPHPHow to add Laravel to WordPress using Sage theme (and install Tailwind CSS)?PHPReverse a String in JavaScriptJavaScriptHow to use HTML picture Tag?HTMLSimple Page Hit Counter in PHPPHPrandom function in SCSS (Sass)SCSS