SQL Server *= Operator?

Solution 1:

Remove this code immediately and replace with a left join. This code does not always interpret correctly (Sometimes SQL Server decides it is a cross join) even in SQL Server 2000 and thus can give incorrect results! Also it is deprecated for the future (Using Outer Joins, SQL Server 2000 documentation archived from the original).

I'm going to add that in adjusting to left joins you should remove all of those other implicit joins as well. The implicit join syntax has been obsolete since 1992, there is no excuse for it still being in production code. And mixing implicit and explicit joins can give unexpected results.


Solution 2:

It is a left outer join, =* is a right outer join.

E.g. the following are equal;

  SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.ID = Table2.FK_ID

  SELECT * FROM Table1, Table2 WHERE Table1.ID *= Table2.FK_ID

Solution 3:

The non-ANSI syntax for outer joins (*= and =*) is on the official list of deprecated features that will be removed in the next version of SQL.

The following SQL Server Database Engine features will not be supported in the next version of SQL Server. Do not use these features in new development work, and modify applications that currently use these features as soon as possible.

The replacement feature is the ANSI compliant syntax of JOIN.

Solution 4:

It's a shorthand join syntax. Take a look at this thread which covers this topic.

Transact-SQL shorthand join syntax?