sql - 合并和缩短两个 where 子句条件

Question

架构:

CREATE TABLE #exclGeoKeys (xKEY INT);
INSERT INTO #exclGeoKeys
    values
    (1),
    (2);

CREATE TABLE #y (NAME CHAR(1),xKEY INT);
INSERT INTO #y
    values
    ('A',1),
    ('C',2),
    ('D',NULL),
    ('E',3),
    ('F',4);

我可以缩短以下内容以产生相同的结果并且不需要 OR xKEY IS NULL 部分吗？

SELECT *
FROM   #y
WHERE  xKEY NOT IN 
                 (
                 SELECT * 
                 FROM #exclGeoKeys
                 ) 
       OR 
       xKEY IS NULL;

Answer 1

将选项与 NOT EXISTS 结合使用运算符(operator)

SELECT *
FROM #y t
WHERE NOT EXISTS (
                  SELECT 1
                  FROM #exclGeoKeys t2
                  WHERE t.xKEY = t2.xKEY
                  )

演示 SQLFiddle

带有 EXISTS 和 EXCEPT 运算符的选项

SELECT *
FROM #y t
WHERE EXISTS (
              SELECT t.xKEY
              EXCEPT
              SELECT t2.xKEY
              FROM #exclGeoKeys t2
              )

带有 NOT EXISTS 和 INTERSECT 运算符的选项

SELECT *
FROM #y t
WHERE NOT EXISTS (
                  SELECT t.xKEY
                  INTERSECT
                  SELECT t2.xKEY
                  FROM #exclGeoKeys t2
                  )