Subqueries, Common Table Expressions and Advanced Querying

Introduction

Relational databases are often introduced through single-query operations: selecting rows, filtering predicates, joining tables, and grouping results. Real applications, however, frequently require a query to use the result of another query. Examples include finding products priced above the average, identifying customers who have never placed an order, calculating each department’s share of total payroll, or traversing an organizational hierarchy. These tasks are addressed through subqueries, derived tables, common table expressions (CTEs), recursive queries, and views.

This chapter uses broadly portable SQL. Minor syntax differences exist among PostgreSQL, MySQL, SQL Server, Oracle, and other database systems; therefore, production developers should consult the documentation for their selected DBMS.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish scalar, multi-row, correlated, and existence subqueries;
• use IN, EXISTS, ANY, ALL, and comparison operators appropriately;
• construct derived tables and common table expressions;
• explain how recursive CTEs process hierarchical and graph-like data;
• create and use views as reusable query interfaces;
• recognize NULL-related pitfalls, performance issues, and security concerns;
• design maintainable advanced queries for full-stack applications.

1. The Role of Query Composition

A query can be viewed as a transformation from one relation to another. A simple query reads base tables and returns a result. A composed query uses one result as an input to another operation. SQL supports composition in several forms:

The central design question is not merely “Can this be written as a subquery?” but “Which form communicates the logic most clearly and executes reliably?”

2. Scalar Subqueries

A scalar subquery returns exactly one column and at most one row. It may appear wherever a single value is valid, such as in a comparison, calculation, or selected expression.

Assume the following tables:

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    salary DECIMAL(12,2)
);

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

To find employees earning more than the company-wide average:

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query returns one value: the average salary. The outer query compares each employee’s salary with that value. If the inner query returns multiple rows, most DBMSs report a scalar-subquery error. If it returns no rows, the scalar result is generally NULL, and comparisons with it are not true.

A scalar subquery can also be used in the SELECT list:

SELECT d.department_name,
       (
           SELECT COUNT(*)
           FROM employees e
           WHERE e.department_id = d.department_id
       ) AS employee_count
FROM departments d;

This example is correlated because the inner query refers to d.department_id from the outer query. The distinction between scalar and correlated concerns different properties: “scalar” describes the number of returned values, while “correlated” describes dependence on the outer row.

Conditional aggregation as an alternative

Many scalar subqueries can be replaced by a join and aggregation:

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
  ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;

This form may be more efficient when counts for many departments are required, and it makes the grouping operation explicit.

3. Multi-Row Subqueries

A multi-row subquery returns a set of values. It is commonly used with IN, ANY or ALL.

3.1 IN

To find employees belonging to departments located in London:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT d.department_id
    FROM departments d
    WHERE d.location = 'London'
);

IN tests whether a value equals at least one value in the returned set. It is conceptually similar to a series of equality comparisons, although the optimizer may implement it differently.

3.2 ANY and ALL

ANY (also called SOME in many systems) is true when the comparison is true for at least one returned value:

SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 3
);

This means the employee earns more than at least one employee in department 3. By contrast, ALL requires the comparison to be true for every returned value:

SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 3
);

This identifies employees earning more than every salary in department 3. If department 3 has no rows, DBMS implementations follow quantified-predicate rules in which > ALL (empty set) is true and > ANY (empty set) is false; developers should test such edge cases because business expectations may differ.

4. Existence Subqueries

EXISTS tests whether a subquery returns at least one row. The selected columns inside an EXISTS query are irrelevant; conventionally, developers write SELECT 1.

To find customers who have placed at least one order:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The database can stop searching after finding the first matching order. This makes EXISTS semantically natural when the question is “Does a related row exist?” rather than “What are the related values?”

NOT EXISTS and anti-joins

To find customers with no orders:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

A common alternative is a LEFT JOIN:

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Both can be correct, but NOT EXISTS is often safer when nullable columns are involved. The apparently equivalent form customer_id NOT IN (subquery) can produce surprising results if the subquery contains NULL. Under three-valued SQL logic, comparison with NULL is UNKNOWN, not true. For exclusion logic, NOT EXISTS is therefore usually the preferred expression.

5. Correlated Subqueries

A correlated subquery references one or more columns from the current row of its outer query. Logically, the inner condition is evaluated in the context of each candidate outer row.

The following query finds products whose price is greater than the average price in their own category:

SELECT p.product_id, p.product_name, p.category_id, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category_id = p.category_id
);

The inner query is not one global average. It computes a category-specific average for each outer product. A join to a grouped derived table may express the same operation:

SELECT p.product_id, p.product_name, p.price
FROM products p
JOIN (
    SELECT category_id, AVG(price) AS category_average
    FROM products
    GROUP BY category_id
) a ON a.category_id = p.category_id
WHERE p.price > a.category_average;

The second form makes the intermediate relation visible and may avoid repeated logical evaluation. Modern optimizers can transform correlated subqueries, but performance should be verified with an execution plan rather than assumed.

A correlated subquery is also useful for “top-per-group” questions:

SELECT e.employee_name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

This returns ties, unlike a solution based on an arbitrary LIMIT 1.

6. Derived Tables

A derived table is a subquery in the FROM clause. It must normally have an alias. It behaves like a temporary relation available only to the containing statement.

SELECT a.department_id, a.average_salary
FROM (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) a
WHERE a.average_salary > 70000;

Derived tables are useful when a query has stages: first aggregate, then filter; first calculate, then join; or first reduce a large relation, then apply additional logic. They also prevent accidental mixing of detail-level and aggregate-level expressions.

A derived table does not automatically create a stored object. Its result exists only during statement execution. This differs from a view, which has a persistent database definition, and from a temporary table, which is a materialized database object with session-specific lifetime in systems that support it.

7. Common Table Expressions

A common table expression is introduced with WITH and assigns a name to a query expression.

WITH department_totals AS (
    SELECT department_id,
           COUNT(*) AS employee_count,
           AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name,
       t.employee_count,
       t.average_salary
FROM departments d
JOIN department_totals t
  ON t.department_id = d.department_id;

A CTE improves readability by giving a meaningful name to an intermediate result. Multiple CTEs can be chained, and later CTEs can refer to earlier ones:

WITH department_totals AS (
    SELECT department_id, SUM(salary) AS payroll
    FROM employees
    GROUP BY department_id
), company_total AS (
    SELECT SUM(payroll) AS total_payroll
    FROM department_totals
)
SELECT d.department_name,
       t.payroll,
       100.0 * t.payroll / NULLIF(c.total_payroll, 0) AS payroll_percentage
FROM departments d
JOIN department_totals t ON t.department_id = d.department_id
CROSS JOIN company_total c;

NULLIF(c.total_payroll, 0) prevents division by zero. The percentage is calculated as:

Here, part and whole are monetary amounts in the same currency, so their ratio is dimensionless; multiplying by 100 expresses it as a percentage. The formula is valid when whole ≠ 0.

CTEs and optimization

A CTE is primarily a logical structuring device, not a guarantee of materialization. Some systems inline a CTE into the surrounding query; others may materialize it, and some provide explicit hints. Materialization can reduce repeated computation when an intermediate result is reused, but it can also create temporary storage and inhibit optimization. Always inspect the execution plan for performance-critical queries.

8. Recursive CTEs

A recursive CTE refers to itself and is composed of:

1. an anchor member, which provides initial rows;
2. a recursive member, which expands those rows;
3. a termination condition, usually implemented through a join that eventually finds no new rows.

Consider an employee hierarchy:

CREATE TABLE employee_hierarchy (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INTEGER NULL
);

The following query returns all employees under a selected manager and records their depth:

WITH RECURSIVE org AS (
    SELECT employee_id,
           employee_name,
           manager_id,
           0 AS depth
    FROM employee_hierarchy
    WHERE employee_id = 10

    UNION ALL

    SELECT e.employee_id,
           e.employee_name,
           e.manager_id,
           o.depth + 1
    FROM employee_hierarchy e
    JOIN org o
      ON e.manager_id = o.employee_id
)
SELECT employee_id, employee_name, manager_id, depth
FROM org
ORDER BY depth, employee_id;

The anchor selects employee 10. Each recursive iteration selects direct reports of rows already found. The process ends when an iteration finds no additional reports.

UNION ALL is normally used because it preserves rows and avoids the deduplication cost of UNION. However, cycles in the data can cause infinite recursion or repeated results. Production queries should enforce hierarchy constraints where possible and use DBMS-specific cycle detection, recursion limits, or a visited-path technique when traversing arbitrary graphs.

A path can be accumulated in systems supporting arrays or strings, for example to display Chief Executive > Director > Analyst. Such syntax is DBMS-specific and should be documented carefully.

Recursive CTEs are useful for:

• organizational charts;
• product component structures;
• folder and category trees;
• bill-of-materials calculations;
• dependency graphs;
• route and reachability analysis.

They are not automatically the best choice for every graph problem. Very large or highly connected graphs may require specialized graph databases, closure tables, materialized paths, or precomputed transitive relationships.

9. Views as Reusable Interfaces

A view is a named database object defined by a query:

CREATE VIEW active_customer_summary AS
SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE c.status = 'ACTIVE'
GROUP BY c.customer_id, c.customer_name;

Applications can query it like a table:

SELECT *
FROM active_customer_summary
WHERE order_count >= 5;

Views provide:

• abstraction from complex joins;
• a stable interface for application developers;
• centralized business logic;
• column-level exposure control;
• easier reporting and analytics.

A normal view generally stores the query definition rather than its result. A materialized view stores query results and must be refreshed. Materialized views can improve read performance for expensive aggregations, but introduce freshness, storage, and refresh-management issues.

Views are not a substitute for authorization design. Security-sensitive systems should combine views with carefully managed privileges, row-level security where supported, parameterized application queries, and auditing. A view can hide columns, but the underlying permissions and ownership model determine whether it truly restricts access.

10. Detailed Applied Case: Retail Customer and Product Analysis

Background

An online retailer stores customers, orders, order_items, and products. Management wants a monthly report identifying active customers whose spending exceeds the average spending of customers in their region. The report must also show whether each customer has purchased at least one product currently marked as premium.

Problem

The report combines several levels of data:

• customer-level attributes such as region;
• order-level dates and status;
• item-level quantities and prices;
• product-level premium classification;
• regional aggregate spending.

A single unstructured query would be difficult to test and maintain.

Approach

First, create a CTE that calculates spending per customer during the reporting period. The expression quantity × unit_price gives line-item revenue under the assumption that prices are stored in the transaction currency and that discounts, refunds, taxes, and shipping are either excluded or handled separately.

WITH customer_spend AS (
    SELECT c.customer_id,
           c.customer_name,
           c.region,
           SUM(oi.quantity * oi.unit_price) AS total_spend
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= DATE '2025-01-01'
      AND o.order_date <  DATE '2025-02-01'
      AND o.status = 'COMPLETED'
    GROUP BY c.customer_id, c.customer_name, c.region
),
regional_average AS (
    SELECT region, AVG(total_spend) AS average_spend
    FROM customer_spend
    GROUP BY region
)
SELECT cs.customer_id,
       cs.customer_name,
       cs.region,
       cs.total_spend,
       ra.average_spend,
       CASE WHEN EXISTS (
           SELECT 1
           FROM orders o
           JOIN order_items oi ON oi.order_id = o.order_id
           JOIN products p ON p.product_id = oi.product_id
           WHERE o.customer_id = cs.customer_id
             AND o.status = 'COMPLETED'
             AND p.is_premium = TRUE
       ) THEN 'Yes' ELSE 'No' END AS bought_premium_product
FROM customer_spend cs
JOIN regional_average ra ON ra.region = cs.region
WHERE cs.total_spend > ra.average_spend
ORDER BY cs.region, cs.total_spend DESC;

Analysis

The first CTE establishes one row per customer, preventing order-item rows from being compared directly with customer-level averages. The second CTE computes one average per region. The final query compares each customer’s total with the appropriate regional average and uses EXISTS because the requirement is a yes/no test, not a list of premium products.

The half-open date range—greater than or equal to the first date and less than the first date of the next month—works safely when order_date includes a time component. It also avoids dependence on a database-specific “end of day” value.

Outcome and learning points

The query is modular, testable, and extensible. Developers can independently validate customer_spend, then regional_average, and finally the report. Indexes on foreign keys, order status/date, and product classification may improve performance, but index design must be evaluated using actual data distributions and execution plans.

11. Performance and Maintainability

Advanced query syntax does not guarantee efficient execution. Important practices include:

• inspect execution plans using the DBMS plan tool;
• index columns used in joins and selective predicates, while considering write overhead;
• avoid unnecessary SELECT * in views and application queries;
• filter early when doing so reduces the amount of data processed;
• ensure join predicates are complete to avoid accidental Cartesian products;
• use EXISTS for existence tests and NOT EXISTS for reliable anti-joins;
• test duplicate and NULL behavior explicitly;
• avoid deeply nested queries when a named CTE improves comprehension;
• parameterize user-supplied values to prevent SQL injection;
• measure with realistic data rather than small development datasets.

Readability is a performance concern indirectly: understandable SQL is easier to profile, review, and correct. However, replacing a query with many CTEs merely for appearance can obscure execution behavior. Names should describe the relation’s meaning, such as monthly_customer_spend, rather than implementation details.

12. Governance, Security, and Contemporary Practice

Complex queries often process personal, financial, or behavioral data. Developers should apply least privilege, expose only necessary columns, and avoid embedding secrets or sensitive identifiers in reports. Views can support data minimization, but they should be reviewed alongside database roles and application authorization.

SQL injection remains a major risk when application code concatenates input into query strings. Subqueries and CTEs do not remove this risk. Use prepared statements or a trusted query builder, and validate values according to their intended type and range.

Modern database platforms increasingly support recursive queries, window functions, JSON data, temporal features, row-level security, and materialized views. AI-assisted coding can help suggest query structures, but generated SQL must be reviewed for correctness, authorization, NULL semantics, portability, and data leakage. A plausible query is not necessarily a safe or semantically correct query.

Best-Practice Checklist

1. State whether an inner query must return one row, many rows, or merely exist.
2. Use scalar subqueries only where scalar cardinality is guaranteed.
3. Prefer EXISTS and NOT EXISTS for existence and non-existence tests.
4. Treat NULL as a third logical state and test exclusion predicates carefully.
5. Use CTEs to expose meaningful stages of a complex transformation.
6. Protect recursive CTEs against cycles and uncontrolled expansion.
7. Use views to provide stable, governed interfaces—not as a replacement for privileges.
8. Verify performance with execution plans and production-like data.
9. Keep business definitions, such as revenue and active customer status, explicit.
10. Parameterize all external inputs.

Chapter Summary

Subqueries allow one SQL operation to depend on another. Scalar subqueries return a single value; multi-row subqueries work with IN, ANY, and ALL; and correlated subqueries depend on values from the current outer row. EXISTS and NOT EXISTS express efficient and semantically clear existence tests, with NOT EXISTS generally safer than NOT IN when NULLs may occur.

Derived tables and CTEs organize multi-stage queries without creating permanent tables. Recursive CTEs combine an anchor and recursive member to process hierarchies and graph-like structures, but require termination and cycle safeguards. Views provide reusable query interfaces and can support abstraction and controlled exposure, while materialized views trade freshness for read performance. Effective advanced SQL combines logical clarity, careful NULL and duplicate handling, secure application integration, and execution-plan-based performance analysis.

Key Terms

Subquery: A query nested inside another SQL statement.

Scalar subquery: A subquery intended to return one column and at most one row.

Correlated subquery: A subquery that references columns from an outer query.

Multi-row subquery: A subquery that returns a set of rows or values.

IN: Predicate testing membership in a set of values.

ANY: Quantified predicate true when a comparison succeeds for at least one value.

ALL: Quantified predicate true when a comparison succeeds for every value.

EXISTS: Predicate testing whether a subquery returns at least one row.

Anti-join: A technique for finding rows without a matching row, often using NOT EXISTS.

Derived table: A subquery in the FROM clause treated as a temporary relation.

Common table expression: A named query expression introduced with WITH.

Recursive CTE: A CTE whose recursive member refers to the CTE itself.

Anchor member: The initial, non-recursive part of a recursive CTE.

Termination condition: The condition that prevents further recursive expansion.

View: A named stored query definition that can be queried like a table.

Materialized view: A stored result of a query that requires refresh management.

Cardinality: The number of rows in a relation or result.

Three-valued logic: SQL logic in which predicates can be true, false, or unknown.

Execution plan: The DBMS-selected strategy for retrieving and processing data.

Parameterized query: A query whose external values are supplied separately from its SQL text.

Review Questions

Short Answer Questions

1. What makes a subquery scalar, and what happens if it returns multiple rows where one value is expected?
2. Explain the difference between IN and EXISTS.
3. Why can NOT IN behave unexpectedly when the subquery returns NULL?
4. What are the anchor and recursive members of a recursive CTE?
5. Distinguish a normal view from a materialized view.

Long Answer Questions

1. Compare scalar, correlated, and existence subqueries, providing an appropriate application for each.
2. Explain how derived tables and CTEs improve the design of multi-stage SQL queries.
3. Describe recursive CTE execution for an organizational hierarchy and discuss cycle risks.
4. Discuss the performance and maintainability trade-offs between correlated subqueries, joins, and aggregating CTEs.
5. Explain how views, privileges, parameterized queries, and least privilege contribute to secure database applications.

Analytical / Application Questions

1. Design a query that identifies products whose price is above the average price in their category. Provide both a correlated-subquery solution and a solution using a grouped derived table.
2. A report using customer_id NOT IN (SELECT customer_id FROM orders) omits some expected customers. Diagnose the likely cause and rewrite the logic safely.
3. Design a recursive query for a category tree that returns each category’s depth and discuss two safeguards against cycles or excessive expansion.

Multiple Choice Questions with Answers

Question 1

Which condition is required for a scalar subquery used in a comparison?

A. It must return exactly one column and no more than one row

B. It must return at least two columns

C. It must contain a recursive reference

D. It must be placed in the FROM clause

Correct Answer: A. It must return exactly one column and no more than one row

Question 2

Which predicate is most directly appropriate when the requirement is to determine whether at least one related order exists?

A. ALL

B. EXISTS

C. BETWEEN

D. LIKE

Correct Answer: B. EXISTS

Question 3

Why is NOT EXISTS often preferred over NOT IN for exclusion logic?

A. NOT EXISTS always sorts the result

B. NOT EXISTS automatically creates an index

C. NOT IN can produce UNKNOWN when the subquery contains NULL

D. NOT EXISTS can only be used with numeric columns

Correct Answer: C. NOT IN can produce UNKNOWN when the subquery contains NULL

Question 4

What is a derived table?

A. A permanent table created by a view

B. A subquery in the FROM clause

C. A recursive table constraint

D. A database backup table

Correct Answer: B. A subquery in the FROM clause

Question 5

What keyword introduces a common table expression?

A. WITH

B. CASE

C. HAVING

D. FETCH

Correct Answer: A. WITH

Question 6

Which two components are required in the usual design of a recursive CTE?

A. A primary key and a foreign key

B. An anchor member and a recursive member

C. A view and a trigger

D. A scalar subquery and a window function

Correct Answer: B. An anchor member and a recursive member

Question 7

What does a normal view generally store?

A. Only a query definition

B. A mandatory physical copy of every result row

C. An execution plan that never changes

D. Only the database indexes

Correct Answer: A. Only a query definition

Question 8

Which query structure is most suitable for calculating totals first and then filtering those totals?

A. A CTE or derived table

B. A database password

C. A foreign-key cascade

D. A transaction rollback

Correct Answer: A. A CTE or derived table

Question 9

In a recursive organizational query, what does the depth value normally represent?

A. The number of columns in the table

B. The number of indexes on the table

C. The number of hierarchy levels from the anchor row

D. The number of rows deleted

Correct Answer: C. The number of hierarchy levels from the anchor row

Question 10

Which practice best protects an application from SQL injection?

A. Concatenating user input into SQL strings

B. Using SELECT * in every query

C. Using prepared statements or parameterized queries

D. Removing all WHERE clauses

Correct Answer: C. Using prepared statements or parameterized queries