Subqueries and CTEs

A subquery is a SQL query nested inside another query. The outer (or main) query uses the results of the inner query (or subquery) to determine its own results.

Basic Subquery Syntax

1SELECT column1, column2, ...
2FROM table_name
3WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

Types of Subqueries

Subqueries can be classified based on where they appear in the main query and what kind of results they return:

1. Based on Location

• WHERE Clause Subqueries: Used for filtering rows
• FROM Clause Subqueries: Create derived tables
• SELECT Clause Subqueries: Calculate values for output columns
• HAVING Clause Subqueries: Filter groups

2. Based on Return Type

• Scalar Subquery: Returns a single value (one row, one column)
• Row Subquery: Returns a single row with multiple columns
• Table Subquery: Returns multiple rows (and possibly multiple columns)
• Correlated Subquery: References columns from the outer query

Let's look at examples of each of these types.

calculates a single value - the average price - which is then subtracted from each product's price.

In the WHERE Clause

1-- Find orders with an amount greater than the average order amount
2SELECT order_id, customer_id, amount
3FROM orders
4WHERE amount > (SELECT AVG(amount) FROM orders);

In this example, the subquery first calculates the average order amount, then the main query finds all orders with an amount greater than this average.

With Aggregate Functions

1-- Find the department with the highest average salary
2SELECT department
3FROM employees
4GROUP BY department
5HAVING AVG(salary) = (
6    SELECT MAX(dept_avg)
7    FROM (
8        SELECT AVG(salary) AS dept_avg
9        FROM employees
10        GROUP BY department
11    ) AS dept_averages
12);

This nested example first calculates the average salary for each department, then finds the maximum of those averages, and finally selects the department with that maximum average salary.

Error Handling

If a subquery is expected to return a scalar value but returns multiple rows, most databases will throw an error. For example:

1-- This will fail if there are multiple departments
2SELECT employee_name
3FROM employees
4WHERE department_id = (SELECT department_id FROM departments);

To avoid such errors, ensure your subquery returns exactly one value, or use operators designed for multi-value comparisons like IN, ANY, or ALL.

creates a derived table with average order values for each customer. The main query then joins this with the customers table to include customer names.

Using the IN Operator

The IN operator is often used with table subqueries to check if a value exists in a result set:

1-- Find products ordered in the last 30 days
2SELECT product_id, product_name
3FROM products
4WHERE product_id IN (
5    SELECT DISTINCT product_id
6    FROM order_items
7    JOIN orders ON order_items.order_id = orders.order_id
8    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
9);

EXISTS Operator

The EXISTS operator checks whether a subquery returns any rows:

1-- Find customers who have placed orders
2SELECT customer_id, name
3FROM customers c
4WHERE EXISTS (
5    SELECT 1
6    FROM orders
7    WHERE customer_id = c.customer_id
8);

The subquery here doesn't actually return data to the outer query. Instead, it returns a boolean value: true if at least one matching row exists, false otherwise. The

SELECT

is a convention - the actual value selected doesn't matter since we're only checking for existence.