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

2

u/NimChimspky Mar 20 '22

I don't know why people say prefer "exists" over "in", they are just very different things. Its like saying prefer "and" instead of "or".

2

u/coyoteazul2 Mar 21 '22

Maybe because they have the same use case? Both determine if a row exists in a set

2

u/NimChimspky Mar 21 '22

in is shorthand for

username='asdas' or username='tieiqw'

exists is not

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.

1

u/MonCalamaro Mar 21 '22

I think the case is even stronger for NOT IN (select...) vs NOT EXISTS (select ...), which are treated very differently by the optimizer and return different results if the select returns a null.

1

u/[deleted] Mar 21 '22

You are right, if there is a difference in performance then NOT EXISTS is typically faster than NOT IN.

But there is a much more important reason to use NOT EXISTS: NOT IN handles NULL values in a way that is surprising to most people. So it's almost always better to use NOT EXISTS anyway.