Problem

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". Not complicated but slightly more code:

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 get more complicated, it could even happen, that you forget to modify one and produce an inconsitent result.

Solution

Recipe #1 - Using with to remove duplicate code

For this and much more, you can use with 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

This will print a table like this: