The readability of the code is better with CTE that with nested queries. So I try to avoid them — I used a nested query in the example above because It is more concise.
For readers not familiar with CTE:
A CTE (Common Table Expression) is a temporary result set that you can reference within another query
I write queries using temporary tables:
CREATE TEMPORARY TABLE table_name AS (SELECT foo FROM bar);
The main benefits of temporary tables are:
- you can investigate the data in the temp table with additional queries to make sure that the result is correct.
- if the query in the temp table 9 returns an error (division by zero) you don’t need to rerun the other 8 temp tables that were successfully executed.
- databases have query optimizers and sometimes they do strange things. With separate temp tables, it doesn’t optimize the query as whole (this is a positive and negative benefit).