Syntax and Encapsulation Odd INNER JOIN

Question

I usually have a good understanding of JOINS, but this is new.

Suppose there are three tables (the classic case of two tables and the third linker table):

  Customer Product Transaction
 -------- ------- -----------
 ID ID CustomerID
 Name Desc ProductID
           Cost date

(Simplified, I cannot reproduce the actual structure, this is not my code.)

Usually, to get the table "who bought what when", I would do the following:

SELECT Customer.Name, Product.Desc, Transaction.Date FROM Product INNER JOIN Transaction ON Transaction.ProductID = Product.ID INNER JOIN Customer ON Transaction.CustomerID = Customer.ID 

But the following was presented to me:

 SELECT Customer.Name, Product.Desc, Transaction.Date FROM Product INNER JOIN ( Transaction INNER JOIN Customer ON Transaction.CustomerID = Customer.ID) ON Transaction.ProductID = Product.ID 

What is it? Another syntax or performance trick?

(This is in SQLServer, FYI, but presumably this can be applied to others ...)

Answer 1

Brackets do not change semantics. The ON clause position controls the logical order of joins.

First request

 SELECT Customer.Name, Product.Desc, Transaction.Date FROM Product INNER JOIN Transaction ON Transaction.ProductID = Product.ID INNER JOIN Customer ON Transaction.CustomerID = Customer.ID 

Second request

(backup brackets removed)

 SELECT Customer.Name, Product.Desc, Transaction.Date FROM Product INNER JOIN Transaction INNER JOIN Customer ON Transaction.CustomerID = Customer.ID ON Transaction.ProductID = Product.ID 

So, logically, in the first example, the connection on Transaction, Product first occurs, then the virtual table obtained from this connects to Customer , while in your second example, the connection on Transaction, Customer Product first connects to the virtual table obtained from this, connects to Product

This is only logical, and since internal joins are associative and commutative, it probably won't make any difference to the execution plan (unless you add OPTION (FORCE ORDER) to the query), but it can do for external joins.

This is covered by Itzik Ben Gan here , but there are a number of inaccuracies in the article, see the subsequent letter from Lubor Kollar .