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.
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: