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.

Subqueries

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:

SELECT *
FROM products
WHERE EXISTS (
   SELECT *
   FROM reviews
   WHERE product_id = products.id
);

Row Value Comparison: Compare a value against subquery output:

SELECT *
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 *
FROM (
   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.

Syntax:

WITH cte_name AS (
   SELECT column
   FROM table
)
SELECT *
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
   UNION ALL
   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.