MSSQL 查询之 NOT IN & LEFT JOIN

文章目录

NOT IN 方式查询
LEFT JOIN 方式查询
总结
发表评论

NOT IN 方式查询

SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T2.points IS NULL OR T2.points = 0 )     -- 条件记录 3870358 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '')                         -- 条件记录 6286655 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.create_date <= '2020-03-01 00:00:00'  -- 条件记录 6731569 条
  -- 查询语句一耗时 7s, 返回记录 150681 条
UNION 
SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '' )                        -- 条件记录 6286656 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.uid NOT IN
    (
      SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 225 条
      SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 271 条
      SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 221 条
      SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 388 条
      SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 373 条
      SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 5986 条
      SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 23079 条
      SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1        -- 子查询记录 24095 条
       -- 子查询总耗时 1m14s, 返回记录 28411 条
    )  -- 条件记录 6711840 条
  -- 查询语句二耗时 1h13m52s, 返回记录 191067 条
ORDER BY
    T1.region,
    T1.streets,
    T1.village,
    T1.organize;
-- 总耗时 1h15m35s, 返回记录 191067 条

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '') -- 条件记录 6286655 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条

-- 查询语句一耗时 7s, 返回记录 150681 条

UNION

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '' ) -- 条件记录 6286656 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.uid NOT IN

(

SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 225 条

SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 271 条

SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 221 条

SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 388 条

SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 373 条

SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 5986 条

SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 23079 条

SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1 -- 子查询记录 24095 条

-- 子查询总耗时 1m14s, 返回记录 28411 条

) -- 条件记录 6711840 条

-- 查询语句二耗时 1h13m52s, 返回记录 191067 条

ORDER BY

T1.region,

T1.streets,

T1.village,

T1.organize;

-- 总耗时 1h15m35s, 返回记录 191067 条

LEFT JOIN 方式查询

SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T2.points IS NULL OR T2.points = 0 )     -- 条件记录 3870358 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '')                         -- 条件记录 6286655 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.create_date <= '2020-03-01 00:00:00'  -- 条件记录 6731569 条
  -- 查询语句一耗时 7s, 返回记录 150681 条
UNION 
SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  LEFT JOIN
    (
      SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 225 条
      SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 271 条
      SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 221 条
      SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 388 条
      SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 373 条
      SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 5986 条
      SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 23079 条
      SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1        -- 子查询记录 24095 条
        -- 子查询耗时 1m14s, 返回记录 28411 条
    ) AS T3 ON T3.account_code=T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '' )                        -- 条件记录 6286656 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T3.id IS NULL                            -- 条件记录 6711856 条
ORDER BY
    T1.region,
    T1.streets,
    T1.village,
    T1.organize;
-- 总耗时 1m46s, 返回记录 191067 条

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '') -- 条件记录 6286655 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条

-- 查询语句一耗时 7s, 返回记录 150681 条

UNION

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

LEFT JOIN

(

SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 225 条

SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 271 条

SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 221 条

SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 388 条

SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 373 条

SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 5986 条

SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 23079 条

SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1 -- 子查询记录 24095 条

-- 子查询耗时 1m14s, 返回记录 28411 条

) AS T3 ON T3.account_code=T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '' ) -- 条件记录 6286656 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T3.id IS NULL -- 条件记录 6711856 条

ORDER BY

T1.region,

T1.streets,

T1.village,

T1.organize;

-- 总耗时 1m46s, 返回记录 191067 条

总结

UNION 与 WHERE 语句应用将返回记录小的条件最先进行匹配，以小表的记录去遍历大表耗时更短；
本次查询中 NOT IN 与 NOT EXISTS 耗时基本相同，由于查询字段存在唯一值、故无法通过索引优化；所以 NOT EXISTS 方式没有明显优势。

文章出自: 本站技术文章均为原创，版权归 "Desen往事 - 个人博客" 所有；部分图片来源于 Yandex ，转载本站文章请注明来源。

本文标题：MSSQL 查询之 NOT IN & LEFT JOIN

本文链接：https://www.desenpast.com/databases/mssql-5303.html

Desen往事

简单生活，知足常乐

MSSQL 查询之 NOT IN & LEFT JOIN

NOT IN 方式查询

LEFT JOIN 方式查询

总结

发表评论取消回复

NOT IN 方式查询

LEFT JOIN 方式查询

总结

相关文章

发表评论 取消回复

发表评论取消回复