The query uses non-ANSI outer join operators.

Error Message:
Msg 4147, Level 15, State 1, Line 3
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.

Severity level:

This error message appears when you try to use the old-style JOIN syntax against a database in SQL Server 2005 compatibility mode.

The SQL statement cannot be parsed and further execution is stopped.

Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. The old-style syntax must be converted into ANSI compliant JOIN syntax or the database must be run in compatibility mode less than 90 (SQL Server 2005).

This error message was introduced with SQL Server 2005.

  FROM Northwind.dbo.Orders o, Northwind.dbo.[Order Details] od
 WHERE o.OrderID *= od.OrderID

SQL Server 2005 still allows this syntax when the database is operated in a compatibility mode less than 90. Future versions of SQL Server however, are likely to support the syntax no more. Such statements such generally be rewritten to use the ANSI compliant JOIN syntax.


2 Responses to “The query uses non-ANSI outer join operators.”

  1. Why remove a syntax which is better in every way then the damn outer join syntax that sucks balls. Easier to read a query, easier to make sql queries in all aspects. Why remove something great and keep the old ways of doing.

    • To answer mike bo: As far as I can best tell, this is because this syntax does not provide sufficient accuracy (it’s ambiguous), the results of such a query can be different based on how the query optimiser chooses to perform the join, furthermore this syntax does not allow for the same complexity of joins and also due to the fact that it is better to have a unified syntax for all the types of joins and various db operations, instead of several operators with only partial functionality.
      As for your “Easier to read/easier to make” argument – that is I think just what you happen to be used to, for me this syntax is much more difficult to read in a complex query, because it is easy to miss a single star and worse for writing queries, because I can’t specify exactly the behaviour I want.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |