Subqueries and CTEs in SQL

Subqueries

From what I understand, a subquery is a query within a query.Subquery (also known as an inner query or nested query) is simply a SQL query embedded inside another SQL statement. It is used to fetch an intermediate dataset that the main (outer) query can use to filter, calculate, or manipulate data.

Common locations of Subqueries

1. In the WHERE Clause (Dynamic Filtering)

• This filters rows based on a calculation or a list generated by the inner query.

-- Finds all employees who make more than the company average
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

2. In the FROM Clause (Derived Tables)

• The subquery acts like a temporary table that the outer query selects data from. These must be given a table alias

-- Treats the subquery results as a temporary table named "dept_salaries"
SELECT department_id, max_salary
FROM (SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id) AS dept_salaries
WHERE max_salary > 100000;

3. In the SELECT Clause

• Calculates a single value to display as a standalone column alongside individual row data

-- Shows each employee's salary right next to the overall company average
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS overall_average
FROM employees;

Important to know:

1. Isolating a single calculation or subset to filter main data.
2. Can only return columns from the outer table
3. Easier to read for multi-step, sequential logic.
4. Can be slower if highly nested or poorly optimized by the engine.

CTEs

In PostgreSQL, a Common Table Expression (CTE) is a temporary result set that you can reference within a larger SQL statement (SELECT, INSERT, UPDATE, or DELETE). They act like temporary tables that exist only for the duration of a single query execution, making complex queries much easier to write, read, and maintain.

1. Basic CTE Syntax

• A standard CTE begins with the WITH keyword, followed by the CTE name and the inner query.

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 10000;

2. Chaining Multiple CTEs

• You can define multiple CTEs sequentially by separating them with a comma. A later CTE can even reference a previously defined CTE within the same query.

WITH total_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT customer_id 
    FROM total_orders
    WHERE order_count > 5
)
SELECT name, email 
FROM customers 
WHERE id IN (SELECT customer_id FROM top_customers);

Key differences between Subqueries and CTEs