Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have heard mixed information on this and am hoping for a canonical or expert opinion.

If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them?

For a contrived example, the JOIN to MyParent depends on the JOIN to MyChild: http://sqlfiddle.com/#!3/31022/5

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    MyChild AS c
        ON c.[Id] = gc.[ParentId]
LEFT OUTER JOIN
    MyParent AS p
        ON p.[id] = c.[ParentId]

enter image description here

Compared to http://sqlfiddle.com/#!3/31022/7

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    (
    MyChild AS c            
    LEFT OUTER JOIN
        MyParent AS p
            ON p.[id] = c.[ParentId]
    )
    ON c.[Id] = gc.[ParentId]

enter image description here

As shown above these produce different query plans in SS2k8

share|improve this question
    
I like using nested joins: michaeljswart.com/2012/09/when-i-use-nested-joins It may be a matter of style though. –  Michael J Swart Feb 10 at 17:29
    
@MichaelJSwart your blog only appears to discuss when the dependent JOIN is an INNER JOIN –  Matthew Feb 10 at 18:09
1  
How do you wish to define "better"? Personally, I find the first much easier to read - my mind doesn't bounce around trying to reverse engineer the relationship. Having ON ... ON twice in a row (parenthesis or not) is very confusing. –  Aaron Bertrand Feb 10 at 18:47
4  
When I find no performance difference between two ways of doing something, the next question I ask myself is: if I got hit by a bus or won the lottery tonight, which version would be most easily understood and maintained by whoever takes over my code tomorrow? –  Aaron Bertrand Feb 10 at 18:52
1  
The use plan hint works when transplanting the second query plan to the first but not vice versa. –  Martin Smith Feb 10 at 21:39

2 Answers 2

up vote 2 down vote accepted

This is absolutely not a canonical answer but I noticed that for the nested loops query plans shown in the SQL Fiddle it was possible to apply the plan from Query 2 to Query 1 with the use of the USE PLAN hint but attempting the reverse operation fails with

Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

Disabling the optimizer transformation rule ReorderLOJN prevents the previously successful plan hint from succeeding too.

Experimenting with greater quantities of data shows that SQL Server is certainly capable of transforming (A LOJ B) LOJ C to A LOJ (B LOJ C) naturally as well but I didn't see any evidence that the reverse is true.

A very contrived case where the first query performs better than the second is

DROP TABLE  MyGrandChild , MyChild,  MyParent

CREATE TABLE MyParent
(Id int)

CREATE TABLE MyChild
(Id int PRIMARY KEY
,ParentId int,
Filler char(8000) NULL)

CREATE TABLE MyGrandChild
(Id int
,ParentId int)

INSERT INTO MyChild
                      (Id, ParentId)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY @@SPID),
                     ROW_NUMBER() OVER (ORDER BY @@SPID)    
FROM master..spt_values  v1, master..spt_values                  

INSERT INTO MyGrandChild
                      (Id, ParentId)
OUTPUT INSERTED.Id INTO MyParent
SELECT TOP (3000) Id, Id AS ParentId
FROM MyChild
ORDER BY Id

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN MyChild AS c
         ON c.[Id] = gc.[ParentId]
       LEFT OUTER JOIN MyParent AS p
         ON p.[Id] = c.[ParentId]

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN( MyChild AS c
                        LEFT OUTER JOIN MyParent AS p
                          ON p.[Id] = c.[ParentId])
         ON c.[Id] = gc.[ParentId] 

Which gives plans

enter image description here

For me Query 1 had an elapsed time of 108 ms vs 1,163 ms for Query 2.

Query 1

Table 'Worktable'. Scan count 0, logical reads 0 
Table 'MyChild'. Scan count 0, logical reads 9196
Table 'MyGrandChild'. Scan count 1, logical reads 7
Table 'MyParent'. Scan count 1, logical reads 5

Query 2

Table 'MyParent'. Scan count 1, logical reads 15000
Table 'MyChild'. Scan count 0, logical reads 9000 
Table 'MyGrandChild'. Scan count 1, logical reads 7

So it might be provisionally assumed that the first ("unnested") syntax is potentially beneficial as it allows more potential join orders to be considered but I haven't done exhaustive enough testing to have much confidence in this as a general rule.

It may well be entirely possible to come up with counter examples where Query 2 performs better. Try both and look at the execution plans.

share|improve this answer

there is no such JOIN Type called "Nested Join". it is a another variation of writing the JOIN may be for readability convenience purpose. you can see them as a "Sub Queries" for understanding purpose only.

if you are more concern about code readability then my take is, it's individual's choice what they are conferrable with.

And if you are concern with performance of the query, and "Force JOIN ORDER" hint is not used in the query, then it doesn't matter if query is written with "Nested Join" or All "Outer Join". SQL server come up with the order based on cost of joining two tables and/or result. SQL Server does the JOIN between two sets of data at a time only.

in fact, imagine that in the second way "nested join" if SQL server decides to do the second part, "MyChild AS c LEFT OUTER JOIN MyParent AS p ON p.[id] = c.[ParentId]" and those table happen to have rows that are going to discard in NEXT LEFT JOIN. in that case SQL server has spent unnecessary resources on doing the OUTER JOIN these two and passing that result to next JOIN.

you can also look similar question asked and answered appropriately here. Understanding 'Nested join' syntax

share|improve this answer
1  
Why, then, do they produce different query plans without using the FORCE JOIN ORDER hint? –  Matthew Feb 10 at 20:31
    
w/o that hint, we can not guarantee the JOIN order and as you are seeing the different execution plan that proves that. for example in the first way "using all Outer Join" SQL server might be doing any of this Two. first "MyChild + MyGrandChild" and Then JOIN to "MyParent". Or First "MyChild + MyParent" and then JOIN to "MyGrandChild". –  Anup Shah Feb 10 at 22:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.