CODEWITHSUNDEEP
sqlpostgresql
Satya
Satya

Reputation: 153

union and select

I have a table in the following structure. The element with p_id as null is to be displayed as root and all elements mentioned in p_id column as inner and rest of the elements in the id column as leaf. is this the correct way to write the query or is there a more optimized way to write query in postgres sql. 

select id, 'Leaf' as node
from tree
where id NOT IN ( select distinct t1.id as node
                 from tree t1 join tree t2 on t1.id = t2.p_id )
union 
select distinct p_id, 'Inner' as node 
from tree
where p_id NOT IN( select id from tree where p_id IS NULL)
union 
select id, 'Root'
from tree
where p_id is NULL
order by id

The table for the same looks like

I have tried above query and it gives expected result, however, I am not sure if this perfect.

id    p_id
-----------
1   2
3   2
6   8
9   8
2   5
8   5
5   (null)

1   Leaf
2   Inner
3   Leaf
5   Root
6   Leaf
8   Inner
9   Leaf

Upvotes: 0

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Usually when working with trees, there is a separate table of nodes. If so, you don't have to use union for this but can move the logic into the select:

select id,
       (case when not exists (select 1 from tree t where t.id = n.id and t.p_id is null)
             then 'root'
             when not exists (select 1 from tree t where t.p_id = n.id)
             then 'leaf'
             else 'inner'
        end)
from nodes n;

You can also do this without the nodes table:

select v.id,
       (case when count(*) filter (where t.p_id is null) > 0 then 'root'
             when bool_and(v.is_child) then 'leaf'
             else 'inner'
        end)
from tree t cross join lateral
     (values (id, true), (p_id, false)
     ) v(id, is_child)
where v.id is not null
group by v.id
order by v.id;

Here is a db<>fiddle.

Upvotes: 1

Related Questions