Geek Answers Handbook

Which table is considered "internal" in a nested loop join

Can someone tell me which table is considered internal 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 internal? I knew this was b , but from the dbsophic article , the first example in the Small outer loop with a well indexed inner input seems to suggest the opposite.

+4
source share
3 answers

Sure...

  • "INNER JOIN" is a logical (relational) join operator
  • Internal and external tables are concepts in a physical nested loop join statement

The selection of internal and external tables for the physical operator is made by the optimizer and is not associated with a logical operator.

Now the nested 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 this has no meaning in theory.

In practice, the optimizer will work best for internal and external tables: this is what your link to the article should describe. Aka how to reduce the number of iterations

For completeness ... INNER JOIN logical operator is commutative and associative
So, A INNER JOIN B same as B INNER JOIN A
There is no internal or external table.

+6
source

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

  • left external join: the first table is the one for which all rows are returned, and the second table is the one for which only the corresponding 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 corresponding rows are returned.
  • full outer join: all rows from both tables are returned.
+1
source

I wonder if it is necessary to divide thinking into “external connection” and “unification of the internal cycle”.

For an outer join, there is a convention (tradition?) That calls LEFT LEFT JOIN as an "external table". (see below from Sybase) This seems like a syntax notation. (Daniel explained this.)

For the inner join, there is no such difference, but for nested join of loops it is necessary to determine which table becomes the leading table, and it is determined by the optimizer.

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

0
source

More articles:


All Articles