SQL Cookbook, 2nd Edition

Appendix B. Common Table Expressions

Many of the queries presented in this cookbook go beyond what is possible using tables as they are typically available in a database, especially in relation to aggregate functions and window functions. Therefore, for some queries, you need to make a derived table—either a subquery or a common table expression (CTE).

Subqueries

Arguably the simplest way to create a virtual table that allows you to run queries on window functions or aggregate functions is a subquery. All that’s required here is to write the query that you need within parentheses and then to write a second query that uses it. The following table illustrates the use of subqueries with a simple double aggregate—you want to find not just the counts of employees in each job, but then identify the highest number, but you can’t nest aggregate functions directly in a standard query.

One pitfall is that some vendors require you to give the subquery table and alias, but others do not. The following example was written in MySQL, which does require an alias. The alias here is HEAD_COUNT_TAB after the closing parenthesis.

Others that require an alias are PostgreSQL and SQL Server, while Oracle does not:

select max(HeadCount) as HighestJobHeadCount from
(select job,count(empno) as HeadCount
from emp
group by job) head_count_tab

Common Table Expressions

CTEs were intended to overcome some of the limits of subqueries, and may be most well known for allowing recursive queries to be used within ...