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