SQL Server 2008 and Non-ANSI Joins

04.15.11

Problem:

You are upgrading your old SQL server to be compliant with SQL Server 2008 but you are getting errors on incompatible non-ANSI outer join operators ("*=" or "=*").

Solution:

Replace these outer joins with the following syntax.

Left Join:

SELECT e.*, a.AttatchmentPath, a.FileSize
FROM InboxEmails e, EmailAttachments a
WHERE a.EmailID =* e.EmailID

-- The Fix
SELECT e.*, a.AttatchmentPath, a.FileSize
FROM InboxEmails AS e
LEFT JOIN EmailAttachments AS a
ON a.EmailID = e.EmailID

Right Join:

SELECT e.*, a.AttatchmentPath, a.FileSize
FROM InboxEmails e, EmailAttachments a
WHERE a.EmailID *= e.EmailID

-- The Fix
SELECT e.*, a.AttatchmentPath, a.FileSize
FROM InboxEmails AS e
RIGHT JOIN EmailAttachments AS a
ON a.EmailID = e.EmailID

Gotcha: The old way of doing the joins handles nulls differently. There is some pixy dust in how sql limts the amount of record returned. The new syntax removes rows much earlier in the process eliminating rows with nulls that may have showed up in the older style of joins.