SQLTeam.com | Weblogs | Forums

IN vs EXISTS


#1

I am trying to get the EXISTS result but I am curious why the IN query did not produce the same result.

SELECT * 
    INTO #TempA
    FROM
    (
    SELECT 'A' AS Field1, '1' AS Field2
    UNION ALL
    SELECT 'A' AS Field1, '2' AS Field2
    UNION ALL
    SELECT 'A' AS Field1, '1' AS Field2
    UNION ALL
    SELECT 'B' AS Field1, '2' AS Field2
    UNION ALL
    SELECT 'B' AS Field1, '1' AS Field2
    UNION ALL
    SELECT 'B' AS Field1, '2' AS Field2
    ) aT

SELECT * FROM #TempA
WHERE Field1 NOT IN ('B') AND Field2 NOT IN ('2')
Field1 Field2
A 1
A 1

SELECT * FROM #TempA aT1
WHERE NOT EXISTS (SELECT * FROM #TempA WHERE aT1.Field1 IN ('B') AND aT1.Field2 IN ('2'))
Field1 Field2
A 1
A 2
A 1
B 1

#2

They're different conditions.

Take specifically 'A', 2.

It will fail the first condition because Field 2 = '2', so "NOT IN ('2')" is false.
It will pass the second condition because A is not in B.


#3

Thanks Scott. Since the Exists worked, is there a more simple way of writing this, by putting @Mode into the SELECT statement:

If @Mode
	BEGIN
		SELECT * FROM #TempA aT1
	END
ELSE
	BEGIN
		SELECT * FROM #TempA aT1
		WHERE NOT EXISTS (SELECT * FROM #TempA WHERE aT1.Field1 IN ('B') AND aT1.Field2 IN ('2'))
	END

#4

i think dynamic SQL can help

dont know if this is what you are looking for

declare @sql nvarchar(max) = ''

select @sql = case when @Mode then 'SELECT * FROM #TempA aT1' else
' SELECT * FROM #TempA aT1
WHERE NOT EXISTS (SELECT * FROM #TempA WHERE aT1.Field1 IN ('B') AND aT1.Field2 IN ('2'))' end

print @sql

exec(@sql)


#5

Perhaps..

   select * from #TempA a
   where not (a.Field1 = 'B' and a.Field2 = '2') or @mode = 1

#6

Thanks Harish and Femiolan

It worked!

DECLARE @Mode	AS BIT = 0
SELECT * FROM #TempA aT1
WHERE (NOT EXISTS (SELECT * FROM #TempA WHERE aT1.Field1 IN ('B') AND aT1.Field2 IN ('2')))
OR (@Mode = 0)

#7

I have been still playing with this logic. The following produces the same results but which methods would be the preferred? Instead of using an AND in the original post, I changed it to an OR. Would these two be equivalent?

SELECT * FROM #TempA
WHERE Field1 NOT IN ('B') OR Field2 NOT IN ('2')
-------------------------------------------------
SELECT * FROM #TempA aT1
WHERE NOT EXISTS (SELECT * FROM #TempA WHERE aT1.Field1 IN ('B') AND aT1.Field2 IN ('2'))

I am guessing the EXISTS is still the preferred way to EXCLUDE across fields because when using IN with OR, it will get tricky as the number of fields increase for exclusion?

Can one think of EXISTS as in terms of TRUE or FALSE? If the subquery returns 1+ records, then it is TRUE, otherwise it is FALSE?