A Nested Loop JOIN processes rows from one input and searches for matching rows in the other input. It can be efficient when one input is small and the other side has an appropriate index.
Understanding JOIN algorithms helps explain why the same SQL query can perform differently depending on table size and indexes.
A small Customers result may be used to efficiently find matching Orders through an indexed CustomerID column.
SELECT
c.CustomerName,
o.OrderID
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;Common in query optimization and execution-plan analysis.
- Nested Loop is a JOIN execution strategy.
- It can work well when one input is small.
- Indexes can make lookups efficient.
- Large unindexed inputs can make it expensive.
- The optimizer chooses the JOIN strategy.
What is a Nested Loop JOIN?
It is a JOIN algorithm that processes rows from one input and searches for matching rows in another input.
When can Nested Loop JOIN perform well?
It can perform well when one input is relatively small and the other input has an efficient index for the JOIN condition.