Subquery in Postgres

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

Share this post