First off, CTEs are temporary. A CTE is defined and used in the same query. A view, on the other hand, is more permanent and can be thought of as a somewhat permanent virtual table. This temporary nature of CTEs can be a good thing. Because the CTE and the query that uses it are all defined together, modifying it to keep up with updated business requirements is easy. Contrast that with a view, which needs to be updated separately from the queries that use it.

This temporary nature is part of the reason why derived tables are so popular; they let you quickly generate a useful temporary result set that you can perform operations on. CTEs build upon that usefulness with a more powerful set of features.

One big reason to use CTEs is because they are often more readable. Complex views or nested derived table queries can be hard for mere mortals to parse, often requiring reading the query inside-out, back-to-front, or some other unnatural order. Contrast that with CTEs, which generally can be read from top to bottom.

For example, here’s our simple CTE example rewritten as a derived table:

And so you don’t have to go back and find it, here is the CTE version again:

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.

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.

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.

For example, let’s expand upon our simple CTE example and drill further into our data by defining a second <cte_name> with its accompanying <cte_body> that selects just the DBAs in the Raleigh office:

Looking at this code, we have our original <cte_name>, emp_ raleigh , and its <cte_body>. We then define a second <cte_name>, emp_raleigh_dbas, and its <cte_body>. emp_raleigh_dbas builds upon emp_raleigh by only looking for records WHERE title='dba'. Lastly, in the <cte_query> section we SELECT everything from emp_raleigh_dbas. Syntactically, this is much more readable than the equivalent query written using a nested derived table.

Using our sample data, the result is as follows:

Building upon the ability to define multiple <cte_name>s in the same query with their corresponding <cte_body>s, we have the ability to refer to a given <cte_name> multiple times, either in a following <cte_body> section or in the <cte_query> section. As an example of referring to a single <cte_name> multiple times, here is an anti-self-join that looks for DBAs that are the only DBA at their particular office:

Here, our dbas <cte_name> simply selects all of the DBAs in the company, then in our ending <cte_query> we refer to dbas two times in order to filter out all DBAs except those we are interested in.

Using our sample data, the result is as follows:

I think management should make sure Toby and Susan visit other offices in the company a few times every year, just so they don’t feel isolated from the other DBAs in the company.