Yogesh Chauhan's Blog

Subquery in Postgres

in Postgres on 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

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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #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
The difference between isFinite() method and isFinite() function in JavaScriptJavaScriptHow to Make a Simple Module with a Form and Menu Link in Drupal 7.x?DrupalArguments in @mixin rules in SCSS (Sass)SCSSHow to use @supports rule in CSS?CSSHow to center an image horizontally and vertically?CSSHow to make a Custom Login Page in WordPress?WordPress