SQL Server * =运算符? [英] SQL Server *= Operator?

问题描述

今天，在客户的生产系统中，我发现了一个SQL Server查询，其中包含一个不熟悉的语法.在下面的示例中，*=运算符的作用是什么?我在MSDN上找不到任何提及.该查询确实执行并返回数据.据任何人所知，自从他们使用SQL Server 2000以来，它就一直存在于系统中，但是现在它们正在运行2005.

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 

推荐答案

立即删除此代码，并用左联接替换.即使在SQL Server 2000中，此代码也不一定总是正确解释(有时SQL Server认为这是交叉联接)，因此可能给出错误的结果！将来也不推荐使用.

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.

我要补充一点，在调整左连接时，您也应该删除所有其他隐式连接.自1992年以来，隐式连接语法已过时，没有任何借口，因为它仍然存在于生产代码中.混合使用隐式联接和显式联接会产生意外结果.

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.

这篇关于SQL Server * =运算符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！