SQL queries optimization - postgresql

Question

Am a beginner in SQL. How can I optimize this query

select * from t1 where parent_id in (select id from t1 where id= 'X')

UNION

select * from t1 where parent_id in (select id from  t1 where parent_id in (select id from t1 where id= 'X'))
UNION

select * from t1 where parent_id in (select id from t1 where parent_id in (select id from  t1 where parent_id in (select id from t1 where id= 'X')))

Possible duplicate of [Find Parent Recursively using Query](https://stackoverflow.com/questions/3699395/find-parent-recursively-using-query) — Nir Alfasi, May 02 '19 at 21:25

James · Answer 1 · 2019-05-02T21:58:54.720

1

You can try with recursive cte:

WITH RECURSIVE cte AS (
  select * from t1 where t1.id='X'
  union all
  select t1.*
  from cte c
    join t1
      on t1.id=c.parent_id
)
select distinct * from cte

edited May 02 '19 at 21:58

answered May 02 '19 at 21:51

James

2,954
2
12
25

SQL queries optimization - postgresql

1 Answers1