What is CTE?

What is CTE?
– CTE is a so-called “recursive query”
– The WOTH clause has become available
　→ Implemented on other RDMS(PostgreSQL, Oracle etc)
– Hierarchical queries can be written very easily
　→ In the past, it had to be nested using subueries.

WITH RECURSIVE Hatoyama AS
(
 select id, last_name, first_name from family
   where last_name= "鳩山" and first_name="和夫"
 union all
   select child.id, child.last_name, child.first_name
   from family as child, Hatoyama
    where Hatoyama.id = child.parent_id
)
select * from Hatoyama;

WITH Yoshida as
(
	select id, last_name, first_name from family
	  where last_name="吉田" and parent id is null
	union all
	  select child.id, child.last_name, child.first_name
	  from family as child, Yoshida
	  where yoshida.id= child.parent_id
)
select * from Yoshida;

select文が階層化しないようにできるわけね。