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')
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 |
1 Like
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.
1 Like
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
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)
1 Like
Perhaps..
select * from #TempA a
where not (a.Field1 = 'B' and a.Field2 = '2') or @mode = 1
1 Like
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)
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?