Sql Excel : Subqueries and Common Table Expressions Are Our Friends

Subqueries are exactly what their name implies, queries within queries. They
make it possible to do complex data manipulation within a single SQL statement,
exactly the types of manipulation needed for data analysis and data mining.
In one sense, subqueries are not needed. All the manipulations could be
accomplished by creating intermediate tables and combining them. The resulting
SQL would be a series of CREATE TABLE statements and INSERT statements (or
possibly CREATE VIEW or SELECT INTO), with simpler queries. Although such an
approach is sometimes useful, especially when the intermediate tables are used
multiple times, it suffers from several problems.
First, instead of thinking about solving a particular problem, you end up thinking
about the data processing, the naming of intermediate tables, determining the
types of columns, remembering to remove tables when they are no longer needed,
deciding whether to build indexes, and so on. All the additional bookkeeping
activity distracts from focusing on the data and the business problems.
Second, SQL optimizers can often find better approaches to running a complicated
query than people can. So, writing multiple SQL statements can interfere with the
Third, maintaining a complicated chain of queries connected by tables can be
quite cumbersome. For instance, adding a new column might require adding new
columns in all sorts of places. Or, you may run part of the script and not realize
that one of the intermediate tables has values from a previous run.
Fourth, the read-only SQL queries that predominate in this book can be run with a
minimum of permissions for the user—simply the permissions to run queries.
Running complicated scripts requires create and modify permissions on at least
part of the database. These permissions are dangerous, because an analyst might
inadvertently damage the database. Without these permissions, it is impossible to
cause such damage.
Subqueries can appear in many different parts of the query, in the SELECT clause,
in the FROM clause, and in the WHERE and HAVING clauses. However, this section
approaches subqueries by why they are used rather than where they appear
Common table expressions (often referred to as CTEs) are another way of writing
queries that appear in the FROM clause. They are more powerful than subqueries for
two reasons. First, they can be used multiple times throughout the query. And,
they can refer to themselves—something called recursive CTEs. The following
sections have examples of both CTEs and subqueries.