Avoid duplicate code generated by a subquery

Assume you have a query where you have a subquery which you need multiple times. Once to check if something exists and once to output some data or build a total. Such a query could look like this:

SELECT person, money FROM my_table
UNION ALL
SELECT 'TOTAL', SUM(money) FROM my_table;

This would build a table where the last row shows you the total money you've got. In this situation, it's not much of a problem but imagine what happens if we have a more complicated query instead of "my_table":

SELECT person, money FROM my_table WHERE YEAR = 2012 AND MONTH = 1
UNION ALL
SELECT 'TOTAL', SUM(money) FROM my_table WHERE YEAR = 2012 AND MONTH = 1;

Here you can easily see that the WHERE conditions appear twice in our query. If it gets more complicated, it could even happen that you forget to modify one and produce an inconsistent result.

Recipe #1 - Using WITH to remove duplicate code

For this and much more, you can use the WITH clause. It's basically a tool you use to materialize a subquery and avoid that Oracle has to execute them multiple times. Let's look at a simple example:

WITH subquery AS (
  SELECT 'remo' person, 100 money FROM DUAL
  UNION ALL
  SELECT 'rest', 12 FROM DUAL
)
SELECT person, money FROM subquery;

This query isn't really useful, it basically builds a virtually materialized subquery based on two SELECT .. FROM DUAL commands and shows the output of it. But what if we want to display another row at the end showing the total sum of money we've got? This is where the WITH clause gets handy:

WITH subquery AS (
  SELECT 'remo' person, 100 money FROM DUAL
  UNION ALL
  SELECT 'rest', 12 FROM DUAL
)
SELECT person, money FROM subquery
UNION ALL
SELECT 'TOTAL', SUM(money) FROM subquery;