CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
Caffeinated
Caffeinated

Reputation: 12484

How is WITH used in Oracle SQL (example code shown )?

I found this example code online when I searched for "how to do an Exclusive Between oracle sql"

Someone was proving that, in Oracle, BETWEEN is by default inclusive.

So they used such code :

with x as (
    select 1 col1 from dual
    union
    select 2 col1 from dual
    union
    select 3 col1 from dual
    UNION
    select 4 col1 from dual

 )

 select *
   from x
  where col1 between 2 and 3

I've never seen such an example, what is going on with the WITH ?

Upvotes: 1

Views: 102

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20862

In short, WITH clause is an inline view, or subquery. It is useful when you will refer to something multiple times, or when you want to abstract parts of a complex query to make it easier to read.

If you are from SQL Server world, you can also think of it like a temporary table.

So:

WITH foo as (select * from tab);

select * from foo;

is like

select * from (select * from tab);

Though it may be more efficient since x is resolved to a single dataset, even if queried multiple times.

It also reduces repetition. If you use a subquery more than once in a statement, you can consider factoring it out using WITH.

It has nothing to do with the BETWEEN example, it is just the author's choice of approach for demonstrating a concept.

Upvotes: 3

Related Questions