SQL Server Performance

Old vs. New Outer Join syntax

Discussion in 'General Developer Questions' started by rwshep2000, Dec 20, 2005.

  1. rwshep2000 New Member

    Hi,

    I am preparing my code for transition to SQL Server 2005. I am converting some queries using the old outer join syntax to the newer ANSI syntax. There is one query which I don't understand how to recreate in ANSI SQL.

    The query needs to return all employees employed during this 401k plan year, whether or not they made contributions. If they did contribute, it needs to return the amount of contribution (for a single payday). So I need to limit both tables with a "where" statment.


    Old query TSQL -- works
    =======================

    Select em.EmployeeNumber,
    gl.Contribution
    from Employees em, GeneralLedger gl
    where
    em.EmployeeNumber *= gl.EmployeeNumber
    and ( em.DateTerminated is null or em.DateTerminated = @ThisYear )
    and ( gl.Account = @401Account )
    and ( gl.Date = @PayDate );

    Returns 54 records (all employees).

    New query ANSI - doesn't work
    =============================

    Select em.EmployeeNumber,
    gl.Contribution
    from Employees em LEFT OUTER JOIN
    GeneralLedger gl on em.EmployeeNumber = gl.EmployeeNumber
    where
    and ( em.DateTerminated is null or em.DateTerminated = @ThisYear )
    and ( gl.Account = @401Account )
    and ( gl.Date = @PayDate );

    Returns 25 employees ( contributors only ).

    I've also tried this with a RIGHT OUTER and a FULL OUTER join, but I get the same results for some reason. Any idea what I'm doing wrong?

    Thanks,

    Bob Shepherd
  2. FrankKalis Moderator

    At a first glance, I would say, your


    and ( gl.Account = @401Account )
    and ( gl.Date = @PayDate );

    have turned the OUTER JOIN into an INNER JOIN, since you're referencing the unpreserved table gl.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  3. FrankKalis Moderator

  4. Madhivanan Moderator

    Frank, do you have English version of your blog?

    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

  6. rwshep2000 New Member

    Itzik Ben-Gan has a great explanation for why my problem is happening. He confirms my suspicion that an order-of-operations difference is the root of the problem. However, I still can't figure out whether this can be fixed, short of creating a temporary view and selecting from it. Is there a way I can write this in a single query?
  7. FrankKalis Moderator

  8. SQLDBcontrol New Member

    Does this work?




    Select em.EmployeeNumber,
    gl.Contribution
    from Employees em LEFT OUTER JOIN
    GeneralLedger gl on em.EmployeeNumber = gl.EmployeeNumber
    and ( gl.Account = @401Account )
    and ( gl.Date = @PayDate )
    where
    and ( em.DateTerminated is null or em.DateTerminated = @ThisYear );



    quote:Originally posted by rwshep2000

    Hi,

    I am preparing my code for transition to SQL Server 2005. I am converting some queries using the old outer join syntax to the newer ANSI syntax. There is one query which I don't understand how to recreate in ANSI SQL.

    The query needs to return all employees employed during this 401k plan year, whether or not they made contributions. If they did contribute, it needs to return the amount of contribution (for a single payday). So I need to limit both tables with a "where" statment.


    Old query TSQL -- works
    =======================

    Select em.EmployeeNumber,
    gl.Contribution
    from Employees em, GeneralLedger gl
    where
    em.EmployeeNumber *= gl.EmployeeNumber
    and ( em.DateTerminated is null or em.DateTerminated = @ThisYear )
    and ( gl.Account = @401Account )
    and ( gl.Date = @PayDate );

    Returns 54 records (all employees).

    New query ANSI - doesn't work
    =============================

    Select em.EmployeeNumber,
    gl.Contribution
    from Employees em LEFT OUTER JOIN
    GeneralLedger gl on em.EmployeeNumber = gl.EmployeeNumber
    where
    and ( em.DateTerminated is null or em.DateTerminated = @ThisYear )
    and ( gl.Account = @401Account )
    and ( gl.Date = @PayDate );

    Returns 25 employees ( contributors only ).

    I've also tried this with a RIGHT OUTER and a FULL OUTER join, but I get the same results for some reason. Any idea what I'm doing wrong?

    Thanks,

    Bob Shepherd

    Karl Grambow

    www.sqldbcontrol.com
  9. Adriaan New Member

    Do a LEFT JOIN against a derived table:

    Select em.EmployeeNumber,gl.Contribution
    from Employees em
    LEFT JOIN
    (SELECT t.EmployeeNumber, t.Contribution FROM GeneralLedger t
    WHERE t.Account = @401Account and t.Date = @PayDate) gl
    ON em.EmployeeNumber = gl.EmployeeNumber
    WHERE (em.DateTerminated is null or em.DateTerminated = @ThisYear)
  10. rwshep2000 New Member

    Hello again,

    I tested both Karl Grambow and Adriaan's methods and they both work. I did not know that you could put additional conditions inside a join statement. Thanks, Karl.

    Thank you Adriaan for reminding me how to do a derived table. From what I understand, "LEFT JOIN" and "LEFT OUTER JOIN" are synonyms. Is there any consensus whether the keyword "OUTER" should be included or excluded from the command? I've noticed that SQL's query builder always includes the word "OUTER" in an outer join.

    Thanks to you all for your help! You made my day.

    Bob Shepherd
  11. Adriaan New Member

    You can always leave out OUTER, even for a FULL JOIN, so why bother. It's definitely redundant.
  12. FrankKalis Moderator

    While I agree, that you don't need to write it, I think it's better for readibility to be explicite.

    ...and, AFAIK only in cases of INNER JOINs, then there is still something like this


    USE Pubs
    SELECT
    r.royalty
    , t.title
    , t.type
    , t.price
    FROM
    roysched r
    INNER JOIN
    titles t
    ON
    r.title_id = t.title_id
    ORDER BY
    r.royalty

    which is equivalent to


    SELECT
    r.royalty
    , t.title
    , t.type
    , t.price
    FROM
    roysched r
    JOIN
    titles t
    ON
    r.title_id = t.title_id
    ORDER BY
    r.royalty

    But now run this


    SELECT
    r.royalty
    , t.title
    , t.type
    , t.price
    FROM
    roysched r
    MERGE JOIN
    titles t
    ON
    r.title_id = t.title_id
    ORDER BY
    r.royalty

    When using a JOIN hint for an INNER JOIN, you *need* to be explicite, otherwise the statement throws an error.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  13. Adriaan New Member

    Well Frank, there is no such thing as a "MERGE JOIN" - MERGE bit is a hint that you can add to a regular INNER JOIN, LEFT JOIN or RIGHT JOIN.

    BOL is not too clear on this topic, for instance it doesn't really say that you can use JOIN for INNER JOIN (which I find confusing anyway) but then again my interpretation of the description of the syntax may be less than adequate ...
  14. FrankKalis Moderator

    Yes, I think I've mentioned that "When using a JOIN hint for an INNER JOIN, you *need* to be explicite". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />However, I find it strange that SQL Server always assumes an INNER JOIN, unless otherwise stated. But when using a JOIN hint, SQL Server gets confused and doesn't assume the INNER JOIN anymore as standard.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  15. Adriaan New Member

    You think we've belittled the SQL development team enough already?[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  16. FrankKalis Moderator

Share This Page