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?
Solution 5
I believe those are "non-ANSI outer join operators". Your database compatibility level must be 80 or lower.
slf
"When the going gets tough, the tough get empirical" -- Jon Carroll
Updated on September 26, 2020Comments
-
slf over 3 years
Today while inside a client's production system, I found a SQL Server query that contained an unfamiliar syntax. In the below example, what does the
*=
operator do? I could not find any mention of it on MSDN. The query does execute and return data. As far as anyone knows, this has been in the system since they were using SQL Server 2000, but they are now running 2005.declare @nProduct int declare @iPricingType int declare @nMCC int set @nProduct = 4 set @iPricingType = 2 set @nMCC = 230 --Build SQL for factor matrix Select distinct base.uiBase_Price_ID, base.nNoteRate, base.sDeliveryOpt, IsNull(base.nPrice,0) as nPrice, IsNull(base.nPrice,0) + Isnull(fact.nFactor,0) as nAdjPrice, base.iProduct_ID, fact.iPosition as fiPosition, base.iPosition, CONVERT(varchar(20), base.dtDate_Updated, 101) + ' ' + CONVERT(varchar(20), base.dtDate_Updated, 108) as 'dtDate_Updated', fact.nFactor, fact.nTreasFactor, product.sProduct_txt , pfi.sPFI_Name, mccprod.nServicing_Fee, fact.nNoteRate as fNoteRate, mcc.nLRA_Charge as nLRA From tbl_Base_Prices base, tbl_Factors fact, tbl_Product product, tbl_PFI pfi, tbl_MCC mcc, tbl_MCC_Product mccprod Where base.iProduct_ID = @nProduct And base.iProduct_ID *= fact.iProduct_ID And base.iPosition *= fact.iPosition And base.nNoteRate *= fact.nNoteRate And base.iPricing_Type = @iPricingType And fact.iMCC_ID = @nMCC And fact.iProduct_ID = @nProduct And mcc.iMCC_ID = @nMCC And mcc.iPFI_ID = pfi.iPFI_ID And mccprod.iMCC_ID = @nMCC And mccprod.iProduct_ID = @nProduct And base.iProduct_ID = product.iProduct_ID and fact.iPricing_Type= @iPricingType Order By base.nNoteRate, base.iPosition