哪个SQL查询执行得更快

Question

当我运行这两个查询时，SQL Server Management Studio中的客户端统计信息显示第二个查询的总执行时间更快。谁能告诉我为什么第二个查询更快？

SELECT MERCHANT_NO, 
(SELECT CASE WHEN CITY IS NULL OR CITY='' THEN m.CITY 
ELSE 
    COALESCE((SELECT c.CITY 
    FROM CODE_CITY c 
    WHERE m.CITY=c.CODE_CITY_ID 
    ), m.CITY)  
    END) AS CITY, 
(SELECT CASE WHEN STATE IS NULL OR STATE='' THEN m.STATE 
ELSE 
    COALESCE((SELECT s.STATE 
    FROM CODE_STATE s 
    WHERE m.STATE=s.CODE_STATE_ID 
    ), m.STATE)  
    END) AS STATE, 
(SELECT MERCHANT_CAT_DESC FROM MERCHANT_CAT mc WHERE mc.MERCHANT_CAT_ID=m.MERCHANT_CAT_ID) AS MERCHANT_CAT_DESC 
FROM MERCHANT m 
WHERE 1=1 

SELECT MERCHANT_NO, 
(SELECT CASE WHEN t.CITY IS NULL OR t.CITY='' THEN m.CITY 
    ELSE 
    t.CITY 
    END AS CITY 
FROM(
     SELECT 
     CASE WHEN CITY IS NULL OR CITY='' THEN '' 
      ELSE 
      (SELECT c.CITY 
       FROM CODE_CITY c 
       WHERE merc.CITY=c.CODE_CITY_ID 
      )  
      END AS CITY 
      FROM MERCHANT merc 
      WHERE merc.MERCHANT_NO=m.MERCHANT_NO 
) t) AS CITY_DESC, 
(SELECT CASE WHEN cs.STATE IS NULL OR cs.STATE='' THEN m.STATE 
    ELSE 
    cs.STATE 
    END AS STATE 
FROM(
     SELECT 
     CASE WHEN STATE IS NULL OR STATE='' THEN '' 
      ELSE 
      (SELECT s.STATE 
       FROM CODE_STATE s 
       WHERE merc.STATE=s.CODE_STATE_ID 
      )  
      END AS STATE 
      FROM MERCHANT merc 
      WHERE merc.MERCHANT_NO=m.MERCHANT_NO 
) cs) AS STATE_DESC, 
(SELECT MERCHANT_CAT_DESC FROM MERCHANT_CAT mc WHERE mc.MERCHANT_CAT_ID=m.MERCHANT_CAT_ID) AS MERCHANT_CAT_DESC 
FROM MERCHANT m 
WHERE 1=1 

Answer 1

您没有使用COALESCE在第二查询数据。所以只有它比第一个查询更快。