YogeshChauhan.com
IN Operator in PostgreSQL
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

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS Mar 2 WordPress: How to access first and random row values from a repeater field in ACF? Mar 1 The basics of CSS Box model Feb 27 WordPress: How to display slider repeater fields in ACF? Feb 26 Simple Page Hit Counter in PHP

You might also like these

For Each Loop in Swift for BeginnersSwiftRBV Framework and closing of big brandsMiscFull and Partial ROLLUP in Postgresql with ExamplesPostgresHow can one check to see if a remote file exists using PHP?PHPHow to overwrite file contents with new content in PHP?PHPCheck if any column has NULL values in PostgresPostgres