Oracle - Recursion (2 ways)
With Oracle, you can use the built-in hierachical queries. start with indicates the base condition where you will start your recursion. connect by will be the l
With Oracle, you can use the built-in hierachical queries. `start with` indicates the base condition where you will start your recursion. `connect by` will be the link condition for each row. Do note that `prior` is associated with `t.id`, as you can also put `prior` on the right side of the condition. The `CONNECT_BY_ISLEAF` is a flag in Oracle that will tell you if this is a leaf in the hierarchy based on your graph. And lastly, `level` indicates where you are in the hierarchy. Thusly, by saying `level` is 1 means we are at our base condition, or "Root" in this case. `CONNECT_BY_ISLEAF` will indicate this particular node has no children, which means all other nodes are of type "Inner."\n```\nselect\n    t.id\n    , case when level = 1 then \'Root\'\n            when CONNECT_BY_ISLEAF = 1 then \'Leaf\'\n            else \'Inner\' end "Type"\nfrom\n    tree t\nstart with t.p_id is null\nconnect by prior t.id = t.p_id\norder by 1\n```\n\nAnother methodolgy is using a CTE and a `union all` on itself to form the recursive model. The caveat with this is we can identify the root easily as it\'s our base condition. Unfortuantely, we have to join the resulting CTE back to tree in order to see if that node is a parent to any other and handle if it\'s an Inner or Leaf node. \n```\nwith graph (id, ntype) as (\n    select\n        t.id, \'Root\' ntype\n    from\n        tree t\n    where\n        t.p_id is null\n    union all \n    select\n        t.id, \'Inner\' ntype\n    from\n        graph g\n        inner join tree t on g.id = t.p_id\n)\nselect\n    distinct \n    g.id "id"\n    , case when t.p_id is null and g.ntype = \'Inner\' then \'Leaf\' \n        else g.ntype end "Type"\nfrom    \n    graph g\n    left outer join tree t on g.id = t.p_id\norder by\n    g.id\n```\nAlthough recursion is powerful, a simple self-join would be more concise than the CTE example above. This self-join, below, starts to look like our first recursion script, however, sans the more advanced hierarchy feature provided in Oracle. \n```\nselect\n    distinct \n    t.id\n    , case when t.p_id is null then \'Root\'\n        when ti.id is null then \'Leaf\'\n        else \'Inner\' end "Type"\nfrom\n    tree t\n    left outer join tree ti on t.id = ti.p_id \norder by\n    t.id\n```
['Recursion', 'Oracle']