What’s going on everybody.

Welcome back to another article on my PostgreSQL Notebook.

Hope you’ll find something helpful here.

Enjoy!

What’s a Subquery?

• The action of taking one or more queries and merging them all together into one in some way.

Sub-queries can be used as

• A source of value

• A source of rows

• A source of a column

Understanding the shape of a query result is the key!

SELECT * FROM products;

→ Return many rows, many columns

SELECT id FROM products;

→ Return rows, ONE column

SELECT COUNT(*) FROM products;

→ Return ONE row, ONE column (single value)

Subqueries in a SELECT

SELECT name, price, (
  SELECT MAX(price) FROM products
)
FROM products 
WHERE price > 860;

Here we’re trying to select to columns name and price

To have a subqueries in a SELECT statement

We need to make sure to put in any subquery that results in a SINGLE value. For instance, this query will be resulted in a single value.

SELECT MAX(price) FROM products

As a result, we will have

Subqueries in a FROM

Remember that, a FROM clause in general is specifying a SET OF ROWS that we want to use inside of our query.

In other words, it’s our source of data.

One caveat:

• Subquery in FROM MUST have an ALIAS applied to it. Otherwise, it will throw an error

With the FROM clause, we can insert any subquery, as long as the outer selects/wheres/etc are COMPATIBLE

Example

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

Subqueries in a JOIN

• Any subquery that returns data compatible with the ON clause

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

Subqueries with WHERE

Here we want to show the id of orders that involve a product with a price / weight ration > 50

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

• The structure of data allowed to be returned by subquery changes depending on the comparison operator!

Data Structure with WHERE subqueries

A SELECT without a FROM

• We can use SELECT without FROM for any subquery that results in a SINGLE VALUE

SELECT (
      SELECT MAX(price) FROM products
  );

Or we can even do this

select
    (select max(price) from phones)  as max_price,
    (select min(price) from phones)  as min_price,
     (select avg(price) from phones) as avg_price;

Select Distinct Values

• DISTINCT will give you a list of all different unique values inside a column.

For example, we want to select all the unique department in the products table

SELECT DISTINCT department FROM products;

SELECT COUNT(DISTINCT department) FROM products;

SELECT DISTINCT deparment, name FROM products; 
// -> Find unique combination of (department, name)

• You may notice that DISTINCT is somehow similar to GROUP BY they all take a look at the columns, and remove the duplicate records, but GROUP BY is much more powerful.

• ONLY GROUP BY can make use of Aggregate functions to take a look at values inside of each of those different groups.

• If we do a DISTINCT over TWO or MORE different columns, we can NO LONGER do a COUNT on it. That’s a restriction there.