4

I just had a need to search a particular string value in 2 columns, so my usual approach is 

SELECT ... FROM ...
WHERE (col1 = 'xyz' OR col2 = 'xyz')

But if I had to check multiple values (just for a quick check), say once for 'abc', then 'www', then 'lol', and so on, it is a pain.

So, I tried this and it worked! I never knew this syntax was supported.

SELECT ... FROM ...
WHERE 'xyz' IN (col1, col2)

Is it ok to use this way? I mean are there any caveats?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
ranit.b
  • 217
  • 3
  • 8
  • 3
    Of course it is okay to use valid SQL syntax in a SQL query. – Gordon Linoff Feb 06 '19 at 12:46
  • 2
    Internally `X IN (A, B, C)` gets expanded to `X = A OR X = B OR X = C`, which, together with the symmetry of `=`, allows you to establish equivalency easily. The only thing to avoid (but that's true in both cases) is mixing types in the expressions, since implicit conversions can have surprising results. – Jeroen Mostert Feb 06 '19 at 12:47
  • True! Probably it was me who had never explored with a string literal on LHS and column names within IN clause on RHS. :) Glad. – ranit.b Feb 06 '19 at 12:49

1 Answers1

5

The engine does the exact same operation on both cases. You can see it clearly on the Predicate:

DECLARE @Table TABLE (
    FirstValue INT,
    SecondValue INT)

INSERT INTO @Table (
    FirstValue,
    SecondValue)
VALUES
    (1, 10),
    (2, 20)

Case 1:

SELECT
    *
FROM
    @Table AS T
WHERE
    1 IN (T.FirstValue, T.SecondValue)

enter image description here

Case 2:

SELECT
    *
FROM
    @Table AS T
WHERE
    T.FirstValue = 1 OR T.SecondValue = 1

enter image description here

EzLo
  • 13,780
  • 10
  • 33
  • 38