Lesson 10

Subqueries and common table expressions

SQL provides powerful features for writing complex analytic queries across database tables. Mastering techniques like subqueries and common table expressions allows answering deeper questions and better organizing logic.


A subquery is a SQL query nested inside another query. It runs first and outputs intermediate results, which the outer query uses in its logic.

Basic syntax:

SELECT column
FROM table
WHERE column_name OPERATOR (
   SELECT column
   FROM table
   WHERE condition

This runs the subquery separately first and brings its scalar result for the outer query to compare against.

Common uses for subqueries:

Existence Check: See if rows meeting a condition exist:

FROM products
   FROM reviews
   WHERE product_id = products.id

Row Value Comparison: Compare a value against subquery output:

FROM products
WHERE price < (
   SELECT AVG(price)
   FROM products

Finds products cheaper than the average price.

Row Generation: Output the subquery as a derived table:

   SELECT id, name
   FROM brands
   WHERE country = 'US'
) AS us_brands;

Further filter and process the subquery output.

Common Table Expressions (CTEs)

A common table expression (CTE) defines a temporary result set that can be referenced in later query clauses. Unlike subqueries, CTEs can be self-referential while executing only once.


WITH cte_name AS (
   SELECT column
   FROM table
FROM cte_name;

This queries the CTE cte_name like a physical table, after defining it above.

CTEs help simplify complex logic and share derived tables across clauses. Recursive CTEs can model hierarchical data using self-references too.

For example, to recursively sum salaries up a department hierarchy:

WITH emp_tree AS (
   SELECT *, 1 AS level
        FROM employees
   SELECT e.*, level + 1
     FROM employees e
        JOIN emp_tree et ON e.manager_id = et.id
SELECT SUM(salary)
FROM emp_tree;

Mastering advanced subquery and CTE techniques allows construction of eloquent SQL for analytic and reporting needs. With practice, building complex data transformations becomes intuitive.

List all books written by authors whose last name is 'Cormen'.
Show each book's title and a flag indicating if its price is above the average price of all books.
Identify customers who made their latest purchase after January 1, 2023.
Which books have the top 5 highest average ratings?
Which genres have a higher than average number of books?
No Data

©2024 SQLZap - Learn SQL Interactively