SQL Server Performance Forum – Threads Archive

Old vs. New Outer Join syntax

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

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

Check out the reply by Itzik Ben-Gan here:
http://groups.google.de/group/micro…read/thread/357c55c205b60277/1ceccbebad520cdc —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank, do you have English version of your blog? Madhivanan Failing to plan is Planning to fail

No, I’m afraid not. It’s time-consuming enough to maintain the site itself and help here. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

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?

Can you post both table structures and some sample data along with the required output? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

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

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)

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

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

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

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 …

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=’

‘ />] 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. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

You think we’ve belittled the SQL development team enough already?[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

Personally I don’t think you can blame it on the dev team. However, the documentation is sometimes suboptimal. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>