Are there any caveats when using column names in 'IN' clause?

Question

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?

Biotic

Of course it is okay to use valid SQL syntax in a SQL query. – Attrition

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. – Chet

True! Probably it was me who had never explored with a string literal on LHS and column names within IN clause on RHS. :) Glad. – Biotic

Answer 1

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)

Case 2:

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

Devindevina

Thanks EzLo. It was helpful. – Biotic