Nested Joins Explained

May 26, 2022 by Robert Gravelle

Just when you thought you knew every type of join, here comes another! Perhaps you’ve heard of nested joins, or even nested-loop query plans, and wondered what they were. Well, wonder no more. Today’s blog will settle the mystery once and for all!

In the world of relational databases, there can be many different names for the same thing. Joins are no exception to this rule. In fact, when it comes to Nested Joins, database practitioners vary as per their opinions on them. Some say that there is no such thing; others are more pragmatic and acknowledge that they are simply an alternative term for multi-table joins.

In all likelihood, the term came about when referring to nested-loop query plans. These are often used by the query engine to answer joins. In its crudest form, a nested loop goes something like this:

for all the rows in outer table 
  for all the rows in the inner table 
    if outer_row and inner row satisfy the join condition 
      emit the rows 
  next inner 
next outer

This is the simplest, but also the slowest type of nested-loop. Meanwhile, multi-table nested-loop joins perform even worse because they scale out horrendously as the product of the number of rows in all the tables involved grows.

A more efficient form of nested loop is nested-loop-over-index:

for all the rows that pass the filter from the outer table 
  use join qualifier from outer table row on index on inner table 
    if row found using index lookup 
      emit the rows 
next outer 

Now that we’ve established that the term “Nested Joins” simply refers to joins between more than two tables, let’s take a quick look at their syntax.

Typically, when we need to join multiple tables and/or views, we would list them one by one, using this generic format:

FROM  Table1 
[ join type ] JOIN Table2 
                ON condition2 
[ join type ] JOIN Table3 
                ON condition3 

But this is not the only way. The official ANSI syntax standard for SQL proposes another valid way to write the above join:

FROM  Table1 
[ join type ] JOIN Table2 
[ join type ] JOIN Table3 
                ON condition3 
                ON condition2 

To make the above join style more human readable, we can add parentheses and indentation to make the meaning clearer:

FROM  Table1 
[ join type ] JOIN ( Table2 
                    [ join type ] JOIN Table3 
                                    ON condition3 ) 
                ON condition2 

Now it’s easier to see that the join between Table2 and Table3 is specified first and has to be done first, before joining to Table1. This query style also positions the join between Table2 and Table3 in such a way that they appear to be nested. In fact, we could consider the join between Table2 and Table3 to be nested.

Here is the same query in Navicat Premium written in both styles:

Today’s blog shed some light on the terms “Nested Joins” and “Nested-loop Query Plan”. So, next time you hear them, realize that they are merely referring to the joining of multiple tables or views, regardless of which syntax is employed in doing so.