Which table is considered 'inner' in a nested loop join

Question

Can anyone tell me which table is considered to be the inner one in a nested loop join? For example if the query is from a inner join b on..., which one, a, or b will be considered inner? I knew that it is b, but from the article at dbsophic, the first example under Small outer loop with a well indexed inner input seems to suggest the reverse.

Answer 1

To be sure...

• "INNER JOIN" is a the logical (relational) join operator
• Inner and outer tables are concepts in the physical nested loop join operator

The choice of inner and outer tables for the physical operator is made by the optimiser and is unrelated to the logical operator.

Now, the nested loop psudeo code is this

for each row R1 in the outer table
    for each row R2 in the inner table
        if R1 joins with R2
            return (R1, R2)

So it doesn't make a difference in theory.

In practice, the optimiser will work out the best way around for inner and outer tables: which is what your article link should describe. A.k.a how to reduce the number of iterations

For completeness... INNER JOIN logical operator is commutative and associative
So A INNER JOIN B is the same as B INNER JOIN A.
There is no inner and outer table here

Answer 2

Actually, both tables are inner as only rows are returned if there is a match in both tables.
When doing an outer join, you specify which table should be the outer one:

• left outer join: The first table is the one for which all rows are returned and the second table is the one for which only the matching rows are returned.
• right outer join: The second table is the one for which all rows are returned and the first table is the one for which only the matching rows are returned.
• full outer join: All rows from both tables are returned.