9: Assembling Queries With Subqueries

What's a Subquery

"List the name and price of all products that are more expensive than all products in the 'Toys' department."

SELECT name, price FROM products WHERE price > ( SELECT MAX(price) FROM products WHERE department = 'Toys' );

Thinking About the Structure of Data

Subqueries can be used as:

  • A source of a value. Eg. SELECT COUNT(*) FROM products.
  • A source of rows. Eg. SELECT * FROM order.
  • A source of a column. Eg. SELECT id FROM order.

Subqueries in a SELECT

SELECT name, ( SELECT COUNT(*) from example ) as count FROM users;

Subqueries in a FROM

SELECT name, price_weight_ratio FROM ( SELECT name, price / weight AS price_weight_ratio FROM products ) AS p WHERE price_weight_ratio > 5;

Note: a subquery must have an alias applied to it in a FROM clause.

FROM subqueries that return a value

SELECT * FROM ( SELECT MAX(price) FROM products) AS p; );

Example of a subquery in a FROM

"Find the average number of orders for all users."

We can use a subquery to find the average number of orders for all users (although it is still an INTEGER in our case so far).

SELECT AVG(order_count) FROM ( -- Using GROUP BY SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id ) AS p;

Note: There are far easier ways to do this than a subquery. Take notice of performance when writing these.

Subqueries in a JOIN clause

Here is a contrived example of a subquery in a JOIN clause.

SELECT first_name FROM users JOIN ( SELECT user_id FROM orders WHERE product_id = 3 ) AS o ON o.user_id = users.id;

Note: requires alias as well.

More useful - subqueries with WHERE

Here is another contrived example.

"Show the id of order that involve a product with a price/weight ration greater than 5."

SELECT id FROM orders WHERE product_id IN ( SELECT id FROM products WHERE price / weight > 50 );

Note: it was brought up here about performance and that rewriting this as a JOIN statement might actually end up the same with Performance thanks to how Postgres interprets the query.

Data structure with WHERE subqueries

  • For most operators, it will return a single value.
  • IN and NOT IN will return a single column, as well as operators with the ALL/SOME/ANY keyword.

A contrived example from the quiz:

SELECT name, price FROM phones WHERE price > ( SELECT price FROM phones WHERE name = 'S5620 Monte' AND manufacturer = 'Samsung' );

The NOT IN operator with a list

SELECT name FROM products WHERE department NOT IN ( SELECT department FROM products WHERE price < 100 );

A New Where Operator

"Show the name, department and price of products that are more expensive than all products in the 'Industrial' department".

The following is a contrived example to make use of the > ALL keyword.

SELECT name, department, price FROM products WHERE price > ALL ( SELECT price FROM products WHERE department = 'Industrial' );

Finally SOME

SOME does as you might expect where you want the clause like 50 < SOME to evaluate such that at least one value is less than 50.

Probably too much about Correlated Subqueries

"Show the name, department and price of the most expensive product in each department."

SELECT name, department, price FROM products AS p1 WHERE p1.price = ( SELECT MAX(price) FROM products AS p2 WHERE p2.department = p1.department );

Correlated subqueries makes use of aliases to help with reducing complexity.

We are referring to a row in our top query in our subquery.

More on correlated subqueries

"Without using a join or a group by, print the number of orders for each product."

We can make use of correlated subqueries within select too!

SELECT name, ( SELECT COUNT(*) FROM orders AS o1 WHERE o1.product_id = p1.id ) AS num_orders FROM products AS p1

A SELECT without a FROM?

SELECT ( SELECT MAX(price) FROM products ) AS max;

Why would we ever do this? Maybe when we want to calculate one value from multiple operations.

A contrived example:

SELECT ( SELECT MAX(price) FROM products ) / ( SELECT MIN(price) FROM products ) AS max_div_min;