Have an interesting scenario - user found a discrepancy with rows returned when running the 2 below queries:
select Columns from SomeView
where 1=1
and AnotherColumn in ('XYZ')
and AnotherColumn = '1'
The above query does not return any results, however the one below does...
select Column from SomeView
where 1=1
and AnotherColumn = 'XYZ'
and AnotherColumn = '1'
Why?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 8, 2025 at 9:15 pm
My understanding of how the engine interprets "equals" versus "IN()" is pretty much the same. The IN operator is used to compare a column value against a set of values whereas, the = operator is used to compare a column value to a specific value. When running both queries, the execution plans look the same...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 8, 2025 at 9:53 pm
Do you have any DDL and test data to demonstate this?
January 8, 2025 at 10:12 pm
Sure...
USE [TempDB]
GO
DROP TABLE IF EXISTS #Results
SELECT 'A' [Column1], 'B' [Column2] INTO #Results
SELECT * FROM #Results
SELECT * FROM #Results
WHERE 1=1 AND [Column1] in ('A') AND [Column2] = 'B'
SELECT * FROM #RESULTS WHERE 1=1 AND [Column1] = 'A' AND [Column2] = 'B'
Here's the output:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 8, 2025 at 10:14 pm
What discrepancy?
Both queries should return nothing (if AnotherColumn is in SomeView), since AnotherColumn can't equal both 'XYZ' and '1'.
Is the leading space intended in 'XYZ'?
Negated/clarified by example
' A' is not equal to 'A' -- leading space in the in example):
SELECT * FROM #Results
WHERE 1=1 AND [Column1] in ('A') AND [Column2] = 'B'
January 8, 2025 at 10:26 pm
Found the issue... the user who found the issue provided me with the code... which I just modified here to reproduce it "as-is"... when I copied the same code into Teams to share with a colleague (because it really didn't make any sense to be either) I caught the issue... there's some embedded character before the "A"
In the query window is looks like:
'A'
But when pasting it into Teams, and email, etc it's pastes as:
' A'
LOL - too funny!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 8, 2025 at 10:36 pm
To see what is actually happening - include the actual execution plan. When you have that - open the properties page and view the actual code that SQL Server is executing for each query.
Also noted that your provided script doesn't match the provided screenshot. In your script you are looking for IN (' A') and in the screenshot you have IN ('A') - notice the missing leading space in the screenshot.
The IN portion would normally be converted to multiple OR statements. But - a single value should not include OR condition and the query that is executed should be the same.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply