YogeshChauhan . com

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
amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

How to replace HTML lists using CSS Counters?CSSHow to load variables, functions, and mixins from another module in SCSS?SCSSWhat is an API?MiscEverything you need to know about ANY, SOME and ALL Operators in PostgresPostgresUnderstanding the confusing and handy “This” keyword in JavaScriptJavaScriptHow to center an image horizontally and vertically?CSS