Wrote a deep dive on how databases actually execute JOINs.
This goes back to something I worked on during my master’s, when we were asked to build a SQL database from scratch (part of the database systems course). I finally took the time to compile those thoughts and learnings.
In this write-up, I cover the algorithms running behind the scenes when we use a JOIN - nested loop join, hash join, merge join, index join, grace hash join, and broadcast join - and why query planners choose one over another.
If you have ever wondered why a JOIN can be instant in one case and terribly slow in another, or how databases join tables with billions of rows efficiently, this should help build a clear mental model. Give it a read.