Left Join与INNER Join执行效率对比.为什么Left Join跟INNER Join的差别很大，下图是他们的执行计划比对，望高手解决下解决方案

Left Join与INNER Join执行效率对比.为什么Left Join跟INNER Join的差别很大，下图是他们的执行计划比对，望高手解决下

SQL code

/*    功能说明：  创建测试    修改说明：    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_SaleCarENDGOCREATE 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));GOBEGIN/*    功能说明：  用循环加入测试数据    修改说明：    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 PriceFROM   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.CheckoutDateGROUP  BY A.SaleCarIdSELECT A.SaleCarId,       Sum(Price)AS PriceFROM   Fact_SaleCar A       LEFT JOIN (SELECT Max(CheckoutDate) AS CheckoutDate,                         SaleCarId                  FROM   Fact_SaleCar B                  GROUP  BY SaleCarId) C         ON A.SaleCarId = C.SaleCarId            AND A. CheckoutDate = C.CheckoutDateGROUP  BY A.SaleCarId 

[解决办法]
left join 时系统做的逻辑运算量大于inner join

确认了一下，以上观点正确。但是是在相同的关联条件下。

个人觉得是因为这么一回事：
inner join 只需选出能匹配的记录
left join 不仅需要选出能匹配的，而且还要返回左表不能匹配的，所以多出了
这一部分逻辑运算

查看更多 下一篇