Letters, February 2004

Controlling Join Order: Parentheses Optional but Helpful

I found Itzik Ben-Gan's T-SQL Black Belt column "Take Control of Joins" (January 2004, InstantDoc ID 40621) very interesting. I'm well aware of the behavior and techniques he describes for controlling the logical order of multitable join processing; in fact, ANSI and SQL Server Books Online (BOL) mandate the behavior Ben-Gan observes. But he's correct that BOL doesn't provide a detailed description of the behavior, and I appreciate Ben-Gan's explanation.

If you look closely at the FROM definition in BOL, you'll find the parentheses that Ben-Gan writes about. However, they are always optional, and they're also redundant. So Ben-Gan's statement, "I just enclosed the inner join between Orders and OrderDetails in parentheses, forcing SQL Server to process the join in the parentheses as an independent step" isn't completely accurate. The second technique he explains, using JOIN-condition order, hits on the real way to control join processing. The placement of the ON clauses actually determines the join order. The syntax also forces the chiastic relationship that Ben-Gan describes. On closer inspection of the FROM definition in BOL, you'll discover that you can't reshuffle the ON conditions arbitrarily and maintain a workable syntax.

Although the parentheses are optional and redundant for determining join-evaluation order, Ben-Gan's suggestion for using them is extremely useful advice for complex queries that use mixed join types. And the use of parentheses will become even more valuable in the context of APPLY in the SQL Server Yukon release. Overall, I'm mesmerized by how the semantics and syntax intertwine within the ON clause. Another valuable article would be to explore the differences between using the conditions inside the ON clause versus outside in the WHERE clause. Thanks for an excellent article and a fascinating theme.

—Lubor Kollar
Group Program Manager
Microsoft SQL Server
Engine Development Team
[email protected]

I appreciate your instructive comments and explanation of how the ON clauses determine join order. I looked for an explanation and documentation in BOL and searched specifically in the FROM clause section—but obviously, not far enough. After receiving your comments, I found the ( ] < joined_table > [ ) clause, which shows the parentheses. But as you mention, BOL doesn't go into detail about the clause or explain the parentheses or how to control the logical order of processing. So to restate your explanation, the parentheses have no effect on the logical order of processing; the join conditions do. But if you use parentheses and follow with a join condition, you logically get the same behavior I describe. I haven't written an article describing the differences between the ON and the WHERE clauses yet, but I agree that it's an important subject—and one I'll tackle soon.

—Itzik Ben-Gan