Other SQL Features
--------------------

--- OR stuff you really wanted to do all this time ............


CASE statement

create table abc (
     id int,
     id2 int
) ;
insert into abc values(1,2);
insert into abc values(3,null);
insert into abc values(null,4);


select
      CASE WHEN id IS NULL THEN 0 ELSE id END
      +CASE WHEN id2 IS NULL THEN 0 ELSE id2 END
from abc ;

select id+id2 from abc where id is not null and id2 is not null
union all
select id from abc where id is not null and id2 is null
union all
select id2 from abc where id is  null and id2 is not null;


---------------------

GROUP BY

select day, time, avg(price) from events group by day, time ;

select day, avg(price) from events group by day ;

select time, avg(price) from events group by time ;


select day, time, avg(price) from events
group by grouping sets((day,time), (day), (time)) ;


select day, time, avg(price) from events
group by rollup (day,time) ;


select day, time, avg(price) from events
group by cube(day,time) ;


--> (day,time), (day), (time), ()


-------------------

Recursion


WITH anc AS
(
   SELECT parent, child FROM parents
   UNION
   SELECT p1.parent, p2.child FROM parents p1, parents p2
   WHERE  p1.child = p2.parent
)
SELECT * FROM anc ;


WITH RECURSIVE anc AS
(
   SELECT parent, child FROM parents
   UNION
   SELECT p1.parent, a.child FROM parents p1, anc a
   WHERE  p1.child = a.parent
)
SELECT * FROM anc ;