I have a WITH statement which returns a table like followings:
| id | parent_id |
|---|---|
| 1 | null |
| 3 | 2 |
| 4 | null |
| 5 | 4 |
The desired output of my query is where parent_id is null or parent_id is present in the id column:
| id | parent_id |
|---|---|
| 1 | null |
| 4 | null |
| 5 | 4 |
I wrote the following query:
SELECT * FROM items
WHERE parent_id IS NULL OR parent_id = ANY(SELECT id from items)
As far I have understood, lateral joins are faster than the ANY operator so my idea was to rewrite the above query using them. I started with:
SELECT * FROM items i1
JOIN LATERAL (SELECT * FROM items i2 WHERE i2.parent_id = i1.id ) t ON true
But where do I add the condition to take the items where parent_id is null?
CodePudding user response：
Use a self join:
SELECT t1.*
FROM items t1
LEFT JOIN items t2 ON t1.parent_id = t2.id
WHERE t1.parent_id IS NULL
OR t2.id IS NOT NULL
This is the best performing approach (assuming you have an index on the id column, which is almost certainly the case).