WHERE condition using ='value' returns different results that using IN('value')

  • 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

  • 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

  • Do you have any DDL and test data to demonstate this?

     

  • 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:

    Screenshot 2025-01-08 at 4.12.14 PM

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • 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

     

    • This reply was modified 22 hours, 47 minutes ago by  ratbak.
  • ' 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'

     

  • 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

  • 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