Combining and shortening two where clause conditions

Question

Schema:

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);

Can I shorten the following so it produces the same result and doesn't need the section OR xKEY IS NULL?

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

The top one is yours, the two below it both return the same result. Are either what you're looking for? http://www.sqlfiddle.com/#!6/53789/9 — Aushin, Apr 25 '13 at 15:24
@Aushin: FYI: if you end each `SELECT` with a semicolon, they will show their result sets separately: http://www.sqlfiddle.com/#!6/53789/10 — mellamokb, Apr 25 '13 at 15:28
@Aushin I'm not trying to avoid anything in particular - just have the feeling that the section `OR xKEY IS NULL` can somehow be amalgamated in with the rest of the script using something like `COALESCE` — whytheq, Apr 25 '13 at 15:36

Aleksandr Fedorenko · Accepted Answer · 2013-04-26T07:37:03.550

3

Use option with NOT EXISTS operator

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

Demo on SQLFiddle

Option with EXISTS and EXCEPT operators

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

Option with NOT EXISTS and INTERSECT operators

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

edited Apr 26 '13 at 07:37

answered Apr 25 '13 at 15:32

Aleksandr Fedorenko

16,594
6
37
44

+1 thanks - I use this structure quite a bit - this highlights a major difference to `NOT IN` – whytheq Apr 25 '13 at 15:42
would you expect the `NOT EXISTS` to run a lot faster? ...the production script seems to be running in 20secs now - compared to 5mins when using the old where clause?! – whytheq Apr 25 '13 at 16:27
Agreed, I always use EXISTS in the WHERE clause. Gives a good explanation Martin Smith in this post [NOT IN vs NOT EXISTS]:http://stackoverflow.com/questions/173041/not-in-vs-not-exists. Also in answer I added another options of query. – Aleksandr Fedorenko Apr 26 '13 at 07:33

score 1 · Answer 2 · edited Apr 26 '13 at 06:19

1

Try these:

--USING NOT EXISTS (DOES NOT REQUIRE THE IS NULL SECTION)

SELECT *
FROM #y AS T1
WHERE NOT EXISTS(SELECT * FROM #exclGeoKeys AS T2 WHERE T1.xKEY = T2.xKEY) 
--OR T1.xKEY IS NULL;

--USING NOT IN
SELECT *
FROM #y AS T1
WHERE T1.xKEY NOT IN (SELECT T2.xKEY FROM #exclGeoKeys AS T2) 
OR T1.xKEY IS NULL;

Hope this helps

edited Apr 26 '13 at 06:19

whytheq

34,466
65
172
267

answered Apr 25 '13 at 15:29

MikePR

2,786
5
31
64

score 1 · Answer 3 · answered Apr 27 '13 at 20:10

1

Unreal value

SELECT *
FROM   #y
WHERE  coalesce(xKEY,-1) NOT IN 
                 (
                 SELECT *
                 FROM #exclGeoKeys
                 )

answered Apr 27 '13 at 20:10

msi77

1,602
1
11
10

score 1 · Answer 4 · answered Apr 27 '13 at 20:17

1

Tried to use a left join?

SELECT #y.*
FROM   #y
LEFT JOIN #exclGeoKeys excl on excl.xKEY = #y.xKEY
WHERE excl.xKEY IS NULL

answered Apr 27 '13 at 20:17

Denis de Bernardy

75,850
13
131
154

Combining and shortening two where clause conditions

4 Answers4