Versions Compared

Old Version 3

changes.mady.by.user Harish Butani

Saved on

compared with

New Version 4

changes.mady.by.user Lars Francke

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

CTE in Select Statements

Code Block
languagesql
with q1 as ( select key from src where key = '5')
select *
from q1;

-- from style
with q1 as (select * from src where key= '5')
from q1
select *
;
 
-- chaining CTEs
with q1 as ( select key from q2 where key = '5'),
q2 as ( select key from src where key = '5')
select * from (select key from q1) a;
 
-- union example
with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2
;

CTE in Views, CTAS, and Insert Statements

Code Block
languagesql
-- insert example
create table s1 like src;
with q1 as ( select key, value from src where key = '5')
from q1
insert overwrite table s1
select *
;

-- ctas example
create table s2 as
with q1 as ( select key from src where key = '4')
select * from q1
;

-- view example
create view v1 as
with q1 as ( select key from src where key = '5')
select * from q1
;
select * from v1;
 
-- view example, name collision
create view v1 as
with q1 as ( select key from src where key = '5')
select * from q1
;
with q1 as ( select key from src where key = '4')
select * from v1
;

In the second View example, a query's CTE is different from the CTE used when creating the view. The result will contain rows with key = '5' because in the view's query statement the CTE defined in the view definition takes effect.

...