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

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?

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.

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 ...

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