r/PostgreSQL • • Mar 20 '22

How-To Mastering SQL Query Optimization

https://tvkoushik.medium.com/mastering-sql-query-optimization-622cc6addef2?sk=8e8edbfd5cd370461aa25cfb8d667c12
12 Upvotes

15 comments sorted by

View all comments

Show parent comments

2

u/[deleted] Mar 21 '22

I think the recommendation regarding IN vs. EXISTS refers to the situation where the source is a sub-query, not a list of constants.

Because

from t1
where t1.x in (select t2.y from t2)

is functionally the same as

from t1
where exists (select * from t2 where t1.x = t2.y)

However, I would say that most optimizer will treat them identically.

1

u/NimChimspky Mar 21 '22

And in that case I would use an inner join, not a sub query.

But sure, its a valid point.

2

u/[deleted] Mar 21 '22

No, an inner join and an IN/EXISTS condition are not the same thing. You can't (always) substitute one for the other.

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8d1b47e09247c6a7e77d0fef6bc1c0f7

1

u/NimChimspky Mar 21 '22

I'm not sure what you mean no. The results would be the same

2

u/[deleted] Mar 21 '22

Did you look at my example? The join and the IN do not return the same result.