Left Join与INNER Join实施效率对比.为什么Left Join跟INNER Join的差别很大，下图是他们的执行计划比对，望高手解决下
日期：2014-05-17 浏览次数：20801 次
Left Join与INNER Join执行效率对比.为什么Left Join跟INNER Join的差别很大，下图是他们的执行计划比对，望高手解决下
/*
功能说明： 创建测试
修改说明： Create by LY on 2011-09-11
*/
IF EXISTS (SELECT 1
FROM SYSOBJECTS
WHERE id = OBJECT_ID('Fact_SaleCar')
AND type = 'U')
BEGIN
DROP TABLE Fact_SaleCar
END
GO
CREATE TABLE [dbo].Fact_SaleCar
(
SaleCarId VARCHAR(20) NOT NULL,
SaleName VARCHAR(50) NULL,
CheckOutDate DATETIME NULL,
Price Float NULL
CONSTRAINT PK_Fact_SaleCar PRIMARY key (SaleCarId)
);
GO
BEGIN
/*
功能说明： 用循环加入测试数据
修改说明： Create by LY on 2011-09-11
*/
DECLARE @NUM INT;
SET @NUM=1;
/*------- 【20万条】---- */
WHILE @NUM <= 100000
BEGIN
INSERT INTO dbo.Fact_SaleCar
SELECT '商店'+RTRIM(@NUM),'SSS'+RTRIM(@NUM),GETDATE(),@NUM;
SET @NUM=@NUM+1;
END;
END;
SELECT A.SaleCarId,
Sum(Price)AS Price
FROM Fact_SaleCar A
INNER JOIN (SELECT Max(CheckoutDate) AS CheckoutDate,
SaleCarId
FROM Fact_SaleCar B
GROUP BY SaleCarId) C
ON A.SaleCarId = C.SaleCarId
AND A. CheckoutDate = C.CheckoutDate
GROUP BY A.SaleCarId
SELECT A.SaleCarId,
Sum(Price)AS Price
FROM Fact_SaleCar A
LEFT JOIN (SELECT Max(CheckoutDate) AS CheckoutDate,
SaleCarId
&nbs
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
