Nested Query

Introduction

• Subquery: A SELECT statement located inside another SQL statement (can be inside SELECT, FROM, WHERE, HAVING). It supplies data for the main query.
• Nested Query: A term used to describe the structure of the query. When a Subquery resides inside a parent statement, this action is called nesting.
• Thus, Subquery can be viewed as the component (the child), while Nested query is the structural relationship (the parent containing the child). A statement that contains a subquery has its entire structure referred to as a nested query.

Classification

• Non-correlated Subquery: This type of subquery runs completely independently of the parent statement. Postgres executes this subquery exactly once, using its result to apply to the parent statement.
  • Example: SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
  • Here, SELECT AVG(salary) FROM employees is an independent Subquery that only needs to run once to provide the value for the outer query.
• Correlated Subquery: This type of subquery depends on the data from the parent statement. For each row processed by the parent statement, the subquery is forced to execute again.
  • Example: SELECT e.name, e.salary, e.department_id FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees d WHERE d.department_id = e.department_id)
  • Here, SELECT AVG(salary) FROM employees d WHERE d.department_id = e.department_id is a Subquery that uses e.department_id as a reference to the outer employee table, so it reruns as it iterates through each row of the employee table.

Use cases

• IN and EXISTS: This is a form of Nested Query because both operators function by taking results from a child SELECT statement to serve as filtering conditions for the parent SELECT statement.
• JOIN
  • Using JOIN with tables normally is not a Nested Query because it is a horizontal link of datasets (flat). The tables involved in the JOIN stand at the same level within the same FROM clause and no SELECT statement is embedded inside another.
  • However, you can still use a Nested Query by joining a table with a Subquery (commonly called an Inline View or Derived Table).
• CREATE TABLE AS: A complex SELECT statement can be nested to create a structure and clone data into a new table instantly.
• INSERT: Instead of inserting each row of data manually using VALUES, you can nest a SELECT statement (Subquery) inside the INSERT to retrieve data from other tables and insert it into the target table.
• UPDATE: A Nested Query can be used to update values based on data retrieved from another table, or used in the WHERE clause to filter rows that need updates.
• DELETE: When deleting data, a Nested Query usually resides in the WHERE clause to pinpoint the exact records to remove based on another related table.
• MERGE: Supported from PostgreSQL 15, this can be used to combine data modification statements (INSERT, UPDATE, DELETE) into a single statement depending on data matching conditions. You can nest a subquery as the data source (USING) to match against the target table.

Alternatives

• In PostgreSQL, instead of writing highly complex Nested queries that cause poor readability and high maintenance overhead, developers prefer using the WITH syntax, known as CTE (Common Table Expressions).
• A CTE is essentially a way of writing a Nested Query but breaking it out separately at the beginning of the statement to increase clarity and it can also be combined with other statements like SELECT, INSERT, UPDATE, DELETE.

Detail

Create the tables as follows

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    membership VARCHAR(20) DEFAULT 'Standard'
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20),
    total_amount DECIMAL(10,2)
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT,
    price_at_purchase DECIMAL(10,2)
);

Non-correlated/Correlated Subquery

-- Query 1
SELECT product_id, name, price 
FROM products 
WHERE price > (
    SELECT AVG(price) 
    FROM products 
    WHERE category = 'Electronics'
);

-- Query 2
SELECT p1.product_id, p1.name, p1.category, p1.price
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price) 
    FROM products p2 
    WHERE p2.category = p1.category
);

• Query 1: This is a Non-correlated Subquery.
  • The Subquery runs only once to calculate the average price of the Electronics category.
  • After that, scanning each row in the products table only requires comparing it with that price value.
  • When running ANALYZE, you can see the Filter: (price > (InitPlan 1).col1) section which indicates that the average price must be calculated before the parent query runs.
• Query 2: This is a Correlated Subquery.
• Each row of products p1 is used to compare with the category in products p2, meaning the Subquery will run multiple times.
• You can see the result 102 Loops meaning that initially products p1 has 102 rows, performing comparisons 102 times with products p2 and the final result only yields 6 rows meeting the condition.
• The value 21.51 is the averaged value, because each time the Subquery runs it yields a different number of rows and calculating the total number of those rows after running through the 102 Loop and dividing by 102 results in exactly 21.51.

IN & EXISTS

-- Query 1
SELECT user_id, name 
FROM users 
WHERE user_id IN (
    SELECT DISTINCT o.user_id 
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.category = 'Apparel'
);

-- Query 2
SELECT u.user_id, u.name 
FROM users u
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = u.user_id AND o.status = 'Completed'
);

-- Query 3
SELECT u.user_id, u.name 
FROM users u
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = u.user_id
);

• Query 1: Get the list of customers who have purchased products belonging to the Apparel department.
• Query 2: Find customers who have at least one Completed order.
• Query 3: Find customers who have never purchased any orders.

JOIN

Used to find customer information along with the total amount they paid, but only counting orders with a Completed status, this Nested Query will create an Inline View before performing the JOIN.

SELECT u.user_id, u.name, temp.total_paid
FROM users u
INNER JOIN (
    SELECT user_id, SUM(total_amount) AS total_paid 
    FROM orders 
    WHERE status = 'Completed'
    GROUP BY user_id
) temp ON u.user_id = temp.user_id;

INSERT & UPDATE & DELETE

-- Statement 1
INSERT INTO products (name, category, price, stock)
SELECT 
    (ARRAY['Pro', 'Elite', 'Eco', 'Smart', 'Classic', 'Ultra'])[floor(random() * 6) + 1] || ' ' || 
    (ARRAY['Gadget', 'Shoes', 'Watch', 'Backpack', 'Bottle', 'Headphones', 'Jacket', 'Keyboard'])[floor(random() * 8) + 1] AS name,
    (ARRAY['Electronics', 'Apparel', 'Home & Kitchen', 'Sports', 'Books'])[floor(random() * 5) + 1] AS category,
    ROUND((random() * 490 + 10)::numeric, 2) AS price,
    floor(random() * 146 + 5)::int AS stock
FROM generate_series(1, 100);

-- Statement 2
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * price_at_purchase) 
    FROM order_items oi 
    WHERE oi.order_id = o.order_id
)
WHERE o.status = 'Pending';

-- Statement 3
UPDATE products
SET price = price * 0.9
WHERE product_id NOT IN (
    SELECT DISTINCT product_id 
    FROM order_items
);

-- Statement 4
DELETE FROM orders o
WHERE NOT EXISTS (
    SELECT 1 
    FROM order_items oi 
    WHERE o.order_id = oi.order_id
);

• Statement 1: Often used to seed data, you can combine functions to generate data for each column field.
  • generate_series: creates a loop
  • random: generates a random number from 0 to 1
  • floor: rounds down
  • ARRAY: creates an array
• Statement 2: Used in the SET clause to dynamically update, synchronize or recalculate the total_amount column of the orders table based on the actual total money from the order_items table.
• Statement 3: Used in the WHERE clause to filter data for modification, applying a 10% discount to all products that have never been sold (not present in order_items).
• Statement 4: Delete corrupted orders that do not have corresponding order_items.

MERGE

MERGE INTO products AS target
USING (
    SELECT 'iPhone 15 Pro' AS name, 200.00 AS price, 0 AS new_stock, 1 as category
    UNION ALL
    SELECT 'Sony Headphone' AS name, 150.00 AS price, 50 AS new_stock, 1 as category
) AS source
ON (target.name = source.name)

WHEN MATCHED AND source.new_stock = 0 THEN
    UPDATE SET stock = 0

WHEN MATCHED AND source.new_stock > 0 THEN
    UPDATE SET stock = target.stock + source.new_stock, 
               price = source.price

WHEN NOT MATCHED THEN
    INSERT (name, category, price, stock) 
    VALUES (source.name, source.category, source.price, source.new_stock);

Using MERGE to handle product information updates from the supplier covers the following scenarios

• If the product already exists and the supplier reports out of stock, update stock = 0.
• If the product already exists and is in stock, update the price and add to the stock.
• If the product does not exist, perform an INSERT.

Happy coding!

See more articles here.