YogeshChauhan.com

Subquery in Postgres

July 21, 2020

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

A subquery can be used anywhere an expression is allowed. 

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Subquery can be used with many operators like EXISTS, IN, ANY, ALL etc.

Let’s take a look at the examples from each of them.

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

Postgre IN operator example 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

Postgre NOT IN example with Subquery:


SELECT product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM order_details 
						WHERE quantity > 99);

//Output
product_name
"Chai"
"Aniseed Syrup"
"Chef Anton's Cajun Seasoning"
...
...
57 rows

Postgres EXISTS Operator Example with Subquery:


SELECT contact_name
FROM suppliers
WHERE EXISTS (SELECT product_name 
			  FROM products 
			  WHERE products.supplier_id = suppliers.supplier_id 
			  AND unit_price < 20);

//Output
contact_name
"Charlotte Cooper"
"Shelley Burke"
"Yoshi Nagase"
...
...
24 rows

Postgres NOT EXISTS Example with Subquery:


SELECT contact_name
FROM suppliers
WHERE NOT EXISTS (SELECT product_name 
			  FROM products 
			  WHERE products.supplier_id = suppliers.supplier_id 
			  AND unit_price < 20);

//Output
contact_name
"Regina Murphy"
"Antonio del Valle Saavedra"
"Sven Petersen"
"Eliane Noz"
"Chantal Goulet"

Postgres ANY Operator Example with Subquery:


SELECT product_name
FROM products
WHERE product_id = ANY (SELECT product_id FROM order_details 
						WHERE quantity = 10);

//Output
product_name
"Chai"
"Chang"
"Chef Anton's Cajun Seasoning"
"Uncle Bob's Organic Dried Pears"
...
...
60 rows

Postgres ALL Operator Example with Subquery:


SELECT product_name
FROM products
WHERE product_id = ALL (SELECT product_id FROM order_details 
						WHERE quantity = 10);

//Output 
product_name

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

Recently Posted

Apr 7 Solution for “Yarn build: Failed because of a stylelint error” Apr 7 Make sure your links have descriptive text Apr 7 How to add Laravel to WordPress using Sage theme (and install Tailwind CSS)? Apr 2 How to create a sidebar using pure CSS? Apr 1 How to switch dark and light themes using pure CSS? Apr 1 How to calculate elapsed time in JavaScript?

You might also like these

Some SQL LIKE Operators We Need to Keep in MindSQL/MySQLHow to Remove PHP File Extensions From Your Website URLs?PHPHow to create HTML elements with ID and class using JavaScript?HTMLWordPress get_posts ExamplesWordPressDebugging in WordPress Part 2: WP_DEBUG_LOG and WP_DEBUG_DISPLAYWordPressCustom Post Type Template Files in WordPressWordPress