As of SQL Server 2005, any database in 90 compatibility mode (settable by sp_dbcmptlevel) cannot support non-ANSI OUTER JOINs. Sometimes these types of joins are called old style joins. An example of such a non-ANSI OUTER JOIN is the following:
SELECT t.*
FROM dbo.titles t, dbo.sales s
WHERE t.title_id *= s.title_id
If this is attempted in a database set for 90 compatibility mode, SQL Server 2005 returns the following error:
Msg 4147, Level 15, State 1, Line 4
The query uses non-ANSI OUTER JOIN operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI OUTER JOIN operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
In order to work, the query must be re-written where the OUTER JOIN is explicitly stated outside of the WHERE clause:
SELECT t.*
FROM dbo.titles t
LEFT JOIN dbo.sales s
ON t.title_id = s.title_id
Why is there such a big deal if the results are the same? As long as the OUTER JOIN is the only condition of the WHERE clause, there isn't an issue with regards to what the query returns. However, as soon as a second condition is added, there is ambiguity as to how to evaluate the query. For instance, consider the following query using the non-ANSI syntax:
SELECT t.*
FROM dbo.titles t, dbo.sales s
WHERE t.title_id *= s.title_id
AND s.title_id IS NULL
This query can be interpreted two different ways. The first way is where the OUTER JOIN is evaluated first and then the WHERE condition testing for cases when s.title_id is NULL. Here is how that would look with the ANSI OUTER JOIN syntax:
SELECT t.*
FROM dbo.titles t
LEFT JOIN dbo.sales s
ON t.title_id = s.title_id
WHERE s.title_id IS NULL
This way, when executed against the pubs sample database, returns two rows. The second way applies the WHERE clause to the sales table first, then evaluates the OUTER JOIN. Here is the query in ANSI OUTER JOIN syntax:
SELECT t.*
FROM dbo.titles t
LEFT JOIN (SELECT *
FROM dbo.sales
WHERE title_id IS NULL) s
ON t.title_id = s.title_id
Obviously, when the IS NULL test is performed first, there will be no rows found. But more importantly, since it's a LEFT OUTER JOIN (for this particular query), we are guaranteed to get every row from the titles table because there is no longer a condition to filter the result set after the JOIN. This means, for the pubs sample database, we're returning 18 rows. Clearly we have a discrepancy in the results. Therefore, our results are going to depend entirely upon what evaluation order SQL Server chooses. So what does SQL Server choose? For this particular query, SQL Server chooses to apply the NULL first, then the LEFT OUTER JOIN, or the second way. This is the equivalent of executing the following:
SELECT *
FROM dbo.titles
That's likely not what we wanted. If it was, then there's no point doing anything more complex than that. In any case, with the old style syntax, we can't get rid of the ambiguity. And that's why SQL Server 2005 insists the ANSI syntax has to be used. On a related note, I've seen some posts about SQL Server 2005 doing away with the old style INNER JOIN. That's not the case. The INNER JOIN in the WHERE clause is not ambiguous and is still supported in SQL Server 2005 as it is in the SQL-92 standard.
Note: The pubs sample database is provided with SQL Server 7.0 and 2000, but not SQL Server 2005. However, you can download both the Northwind and pubs database for the new version.
Technorati Tags:
DATABASE |
SQL |
T-SQL |
