CHAPTER 1 ■ BASICS OF COMMON TABLE EXPRESSIONS
7
Unsurprisingly, the output of both queries, using our sample data, is the same.
However, when you compare them, the CTE can be understood by simply reading
it from beginning to end. To understand the derived table, on the other hand, you
need to first read the inner SELECT statement and then jump up to the outer SELECT
statement, and then back down to the end. On a simple example like this, the extra
difficulty compared to the CTE is minimal, but as a derived table query becomes
more complex, the difficulty in reading it goes up exponentially. For a CTE, however,
the difficulty goes up in a more linear fashion because you can always just read from
beginning to end, naturally.
Using in One or Many Places
Following on from the previous section, another reason to use CTEs is if you need
something complex for just one query as opposed to something that will be used many
times in many different queries. For example, if your underlying sales table stores
invoice dates using a Unix timestamp, but several of your applications expect YYYY-
MM-DD whenever they query the table, a view would be an excellent solution; just
define the view and have your applications call that. On the other hand, a complex view
only used once in a single application might be more maintainable if rewritten as an
easier-to-read CTE.
Permissions
When working on your own personal databases on your own workstation or server, your
database user generally has the ALL PRIVILEGES WITH GRANT OPTION permissions, which
means you can do anything you need or want to your tables and databases, including
CREATE, UPDATE, INSERT, DELETE, and so on. Or, you might regularly just log in as the root
database user, which automatically has all permissions. Databases used in production,
however, generally have more granular access defined. Some users are only able to
SELECT from tables in certain databases, while others can make inserts in some tables
but not in others, and yet other users are given more or fewer grants depending on their
various job functions.
You may find yourself in need of something like a view on a table that you do not
have the CREATE VIEW permission on. CTEs only require the SELECT permission, so in this
case using a CTE is a great way to get what you need without having to pester one of the
DBAs to either create the view you need for you or asking them to grant you the CREATE
VIEW permission on the table you need it on, which they may be unable to do because of
company policies.
Nesting
CTEs bring several new tricks to our DBA toolbox, one of which is that in each individual
<cte_body> we can refer to other CTEs. This solves a big problem with nested derived
tables where every level of nesting greatly increases the complexity.