On Wed, Nov 19, 2014 at 10:48 AM, daku.sandor [via PostgreSQL] <[hidden email]> wrote:
Slightly off:
I prefer "exists" to "join" if it's possible while on the list I almost never see any answer that uses "exists". Is my exists fixation is some kind of bad practice?
I tend not to use exists even where it is warranted...mostly because it just sounds wrong to me for some reason. Correlated subqueries are tougher for me to reason and understand quickly so if a join or IN version of a query will work I tend to go that way first.
The optimizer does a decent job of making exists and joins functionally equivalent. Exists and IN are not in the presence of NULL so I am often concerned about using IN instead of Exists but still end up doing it and only changing if the query performs badly.
The ON clause is more obvious than a equality condition in a where clause in a subquery. I tend to use "JOIN (subquery) alias ON" instead of exists too - partially to separate out the subsetting logic (WHERE) from the join logic (ON)