During my work for AMIS I came across a (to me at least) new feature of the SQL engine. Instead of building an inline view in the from clause of a statement you can build this ‘inline view’ before the SQL statement and use the results in the from clause. The syntax is pretty straightforward:

  WITH <your_alias> AS (<select_statement>) 
SELECT <fieldlist> 
  FROM <tables,><your_alias> 
 WHERE <where_clause>;

It was quite puzzling to me at first, but I can definitely see possibilities here. Not only is this approach probably more readable than using inline views, but according to Oracle this approach is also much faster.

 Improving Query Performance with the SQL WITH Clause tells me a couple of things. First of all, this feature was introduced in Oracle 9i (which makes is apparent that I didn’t know of this feature, because 1) I didn’t need it before and 2) my major customers at my previous employer were still running Oracle 8i). Second, this feature is supposed to speed up query execution. If for instance you have multiple references to the same query, you can of course copy and paste it’s contents in the query, but that is not really Single Point Of Definition. And the query has to be parsed and executed multiple times where the result should be the same at all times. Of course you can circumvent this issue by creating a real view in the database, but there are times when you don’t want that, because you eiter don’t want to expose the query logic, or you don’t have the rights to do this.

Consider the following query (borrowed from Ask The Oracle Expert):

select product_id
     , supplier
     , price
     , iteminfo
  from (
       select ... from table1
       union all
       select ... from table2
       union all
       select ... from table3
       ) as T
 where price =
       ( select min(price)
           from (
                select ... from table1
                union all
                select ... from table2
                union all
                select ... from table3
                ) as T1
          where product_id = T.product_id )

   
and consider the same query, but now using the WITH clause:

with simpletable as
(
select ... from table1                 
union all                              
select ... from table2                 
union all                              
select ... from table3                 
)
select product_id
     , supplier
     , price
     , iteminfo
  from simpletable as T
 where price =
       ( select min(price)
           from simpletable
          where product_id = T.product_id )

The second query is not only more readable, but it also implements SPOD. If I need to change the ‘inline view’, then I should make sure I make the same changes in both (or maybe even more) queries.

According to Don Burleson in his article Oracle SQL-99 WITH clause the following apply:

• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
• Formally, the “WITH clause” is called subquery factoring
• The SQL “WITH clause” is used when a subquery is executed multiple times
• Also useful for recursive queries (SQL-99, but not Oracle SQL)

I think it makes the queries more readable as well as it may speed up the execution. It is fun to lean a new feature that has been around for a couple of years 😉