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.

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

Following up on my previous question where we found query performance difference between different join styles (ie. nested vs. traditional), I have realized that the nested syntax also modifies the behavior of the query. Consider the following 2 queries.

SELECT  a.*, m.*, n.*
FROM    dbo.Autos a
LEFT JOIN dbo.Models m
  JOIN dbo.Manufacturers n  -- <-- Nested INNER JOIN
  ON n.ManufacturerID = m.ManufacturerID
ON m.ModelID = a.ModelID

enter image description here

This does not have to make the manufactures join in order to include an auto row with a ModelID that is NOT in the Models table.

enter image description here

Using the traditional syntax, we have to change the join to Manufactures to an outer join, like so... but this changes the query plan.

SELECT a.*, m.*, n.*
FROM dbo.Autos a
LEFT JOIN dbo.Models m
ON m.ModelID = a.ModelID
LEFT JOIN dbo.Manufacturers n -- <-- Now LEFT OUTER JOIN
ON n.ManufacturerID = m.ManufacturerID

enter image description here

share|improve this question
4  
The two queries are logically different, so the execution plans are different. What is your question? –  Jon Seigel Oct 29 '13 at 17:20
2  
Are you asking for a way to rewrite LEFT JOIN ... INNER JOIN ... ON ... ON without changing the semantics and plan? If so You might consider Right Join –  Martin Smith Oct 29 '13 at 17:26
5  
Personally I prefer the syntax LEFT JOIN (... INNER JOIN ON ...) ON ... -- so much easier to read than either the double-ON or any kind of RIGHT join. –  Jon Seigel Oct 29 '13 at 17:30
5  
@JeffInCO - They can produce different results. There is an example of that in my previous link about Right Outer Join. –  Martin Smith Oct 29 '13 at 18:27
1  
The 2 queries are equivalent only if there is a Foreign Key from Models (ManufacturerID) to Manufacturers (ManufacturerID) and it can be trusted. And even then, I'm not at all sure that this kind of (rewriting) rule has been implemented in and examined by the optimizer. –  ypercube Oct 30 '13 at 7:09

1 Answer 1

up vote 6 down vote accepted

If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

The second plan has a lower estimated cost, so in that limited sense it is 'better'.

The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form of the query happens to find a plan using hash join and a table spool early on. The estimated cost of that plan is so low that the optimizer does not bother looking for anything better.

The second form of the query happens to find a plan using only nested loops outer joins early in the search process, and again the optimizer decides that plan is good enough. It so happens that this plan is estimated to be cheaper.

That said (as mentioned in the question comments) the two queries are not semantically identical. This may not be important to you if you can guarantee that the results will always be the same for all possible future states of your database, but the optimizer cannot make that assumption. It only ever produces plans that are guaranteed to produce the same results specified by the SQL, in all circumstances.

I have realized that the nested syntax also modifies the behaviour of the query.

The 'nested syntax' is just one aspect of the whole ANSI join syntax specification. To enable a full logical specification for more complex join patterns, the specification allows (optional) parentheses, and FROM clause subqueries.

The query can be written using the same ANSI syntax using parentheses:

SELECT
    A.*,
    M.*,
    N.* 
FROM dbo.Autos AS A
LEFT JOIN
(
    dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) ON M.ModelID = A.ModelID;

This form clearly shows that the logical requirement is to left join from Autos to the result of inner joining Manufacturers to Models. Omitting the optional parentheses gives the form you call 'nested':

SELECT
    A.*,
    M.*,
    N.* 
FROM dbo.Autos AS A
LEFT JOIN dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
    ON M.ModelID = A.ModelID;

This is not a different syntax - it is just omitting optional parentheses and reformatting a bit.

As Martin mentioned, it is also possible in this case to express the logical requirement using inner joins followed by a right outer join:

SELECT
    A.*,
    M.*,
    N.* 
FROM dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
RIGHT JOIN dbo.Autos AS A
    ON A.ModelID = M.ModelID;

All three query forms above use the same ANSI join syntax. All three also happen to produce the same physical execution plan with the data set provided:

Common execution plan

As I mentioned in my answer to your previous question, queries that express exactly the same logical requirement will not always produce the same execution plan. Which logical query form you prefer to use is largely a question of style. There is no correlation between one particular style and 'better' query plans in general. I would generally advise against rewriting a query to get a particular plan if the new query is not genuinely logically identical to the original.

The SQL standard also allows FROM clause subqueries, so yet another way to write the same query specification is:

SELECT * 
FROM dbo.Autos AS A
LEFT JOIN
(
    SELECT
        N.ManufacturerID,
        ManufacturerName = N.Name,
        M.ModelID,
        ModelName = M.Name
    FROM dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) AS R1
    ON R1.ModelID = A.ModelID;

Using the traditional syntax, we have to change the join to `Manufacturers to an outer join, like so... but this changes the query plan.

This probably changes the meaning of the query, in which case it is technically not a valid alternative (but see ypercube's comment on your question).

The (optional) parentheses in the ANSI join syntax are there precisely for more complex join requirements like this, so you should not be afraid to use them where necessary.

share|improve this answer
    
Thanks to you and the commentators, I'm close to understanding these concepts. Most importantly ~"it is very important to understand the semantic equivalence of the queries" -thanks @ypercube I think your explanation of optional parentheses indicates my 'nested' style is not wrong but as ypercube and JonSeigel comment it's probable preferable to use them. –  JeffInCO Oct 30 '13 at 22:06

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.