sql查询两张表不同的数据及sql查询语句优化总结

sql查询两张表不同的数据及sql查询语句优化，

方法一（推荐）

WITH   C AS ( SELECT   Name
               FROM     ConsumerCouponApply A
               WHERE    NOT EXISTS ( SELECT 1
                                         FROM   ConsumerCouponApply B
                                         WHERE  B.Name = A.Name
                                         GROUP BY B.Name )
               GROUP BY A.Name
             )
    SELECT  COUNT(1)
    FROM    C

方法二

WITH    C AS ( SELECT   A.Name
               FROM     ConsumerCouponApply A
               GROUP BY A.Name
               EXCEPT
               SELECT   B.Name
               FROM     ConsumerCouponApply B
               GROUP BY B.Name
             )
    SELECT  COUNT(1)
    FROM    C

方法三

SELECT  COUNT(A.Name)
    FROM    ConsumerCouponApply A
            LEFT JOIN ConsumerCouponApply B ON A.Name = B.Name
    WHERE   B.ID IS NULL

方法四

WITH    C AS ( SELECT   A.Name
                   FROM     ConsumerCouponApply A
                   WHERE    ( SELECT    COUNT(1)
                              FROM      ConsumerCouponApply B
                              WHERE     B.Name = A.Name
                              GROUP BY  B.Name
                            ) IS NULL
                   GROUP BY A.Name
                 )
        SELECT  COUNT(1)
        FROM    C

依次查询响应时间：