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 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
How to show confirmation alerts with OK and cancel buttons using Swift 5?SwiftKubernetes vs. Docker? It’s a misleading phraseMiscellaneousHow to apply style only to first child and/or only to children other than the first child?CSSCan we execute conditions in SQL?SQL/MySQLWhat are Class Constants in PHP?PHPWordPress: How to print ACF array field values?WordPress