CODEWITHSUNDEEP
sqljoinnested-loops
NedStarkOfWinterfell
NedStarkOfWinterfell

Reputation: 5223

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

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.

Upvotes: 6

Views: 5955

Answers (3)

benik9
benik9

Reputation: 191

I wonder if we need to separate the thinking into "outer join" and "inner loop join".

For outer join, there is a convention (tradition?) that names LEFT of LEFT JOIN as "outer table". (see below from Sybase) It seems syntactical designation. (Daniel was explaining this.)

For inner join there is no such distinction, but for nested loop join, there is a need to determine which table becomes the driving table, and it is determined by the optimizer.

Sybase has an article describing inner and outer tables. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug153.htm "The terms outer table and inner table describe the placement of the tables in an outer join"

Upvotes: 0

gbn
gbn

Reputation: 432639

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

Upvotes: 8

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174457

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.

Upvotes: 2

Related Questions