TLDR: JOIN Algorithms

Date: 2026-02-24 Source: https://arpitbhayani.me/blogs/join-algorithms

Overview

When you write a SQL query with a JOIN clause, you probably do not think much about what happens next. You just expect the database to return the right rows. But this simple keyword forces your database to make one of the most consequential decisions a query planner makes: which join algorithm should it use?

Key Points

• Index Join (Indexed Nested Loop Join)
• Pre-requisites: Before diving in, you should be comfortable with: and the difference between sequential and random I/O at a high level
• Why Join Algorithms Exist: Given two tables R and S, find all pairs of rows (r, s) where r.key = s.key.
• Nested Loop Join: This is the most intuitive join algorithm.
• Hash Join: Hash join is actually one of the most widely used join algorithms.
• If both relations are sorted on the join key, you can merge them in a single linear pass — similar to the merge step in merge sort.