Hacker News new | past | comments | ask | show | jobs | submit login

NOT EXISTS has been known to be faster than a join for years now. In what possible way is this news? NOT EXISTS allows for short circuiting, like an OR: if at least one record exists, processing can quit. The Join would need to process all records.

This puts into context all the people complaining about SQL and relational databases, and how great key-value stores are. Perhaps some of the sturm und drang is over the fact that, while SQL people admittedly may have little experience with Hadoop or other non-relational processing and storage, sounds like some HN folks have actually had little experience with the SQL they are disparaging.

Joe Celko's SQL for Smarties is full of great explanations of how to really use SQL, and is worth picking up if you want to see what SQL can really do and work around it's (many) warts. http://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp...




The query optimiser should be dealing with that. If you're doing an outer join and checking for the key being NULL then it can easily see that the operation can be short circuited.

It's all implementation details. I've worked with databases where a NOT EXISTS would run a query for every source row where as an outer join would run through once doing the join and was therefore faster.




Applications are open for YC Winter 2021

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: