SQL - 'With'

[cbrunny]

I've been learning SQL for about a year now - queries only, no updating or inserting. We are shifting to a Cognos 10 implementation which uses some different SQL than I'm used to seeing.

What is 'With'? How is it different from just adding a sub-query (mis-named?)?

E.g. This is what I call a sub-query, which is almost definitely mis-named.

select

SQ.a,
SQ.b,
SQ.c,
table2.otherthing,

from

(select

a,
b,
c

from
tablename) SQ,
table2

where

..blah blah




Is this similar to a With statement in terms of function?

Thanks!

 

[KB]

With behaves very similar to a subquery. Performance wise its nearly identical. The benefit of a With Clause over a subquery is that you can reuse the query of a WITH clause without having to duplicate the query definition. For more information see:


http://oracle-base.com/articles/misc/with-clause.php

 

[cbrunny]

ah that's interesting. Makes sense why Cognos would prefer 'With' over a standard subquery then. Thanks for the help, that's all I needed to know!

 

[KentState]

WITH is also self referencing for recursion.