CODEWITHSUNDEEP
postgresqllateral-join
Emaborsa
Emaborsa

Reputation: 2860

Rewrite Where condition using Lateral Join

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?

Upvotes: 0

Views: 262

Answers (1)

Bohemian
Bohemian

Reputation: 425083

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).

Upvotes: 1

Related Questions