Double IN Statements in SQL

Question

Just curious about the IN statement in SQL. I know I can search multiple columns with one value by doing

'val1' IN (col1,col2)

And can search a column for multiple values

col1 IN ('val1','val2')   

But is there a way to do both of these simultaneously, without restorting to an repeating AND / OR in the SQl? I am looking to do this in the most scalable way, so independent of how many vals / cols i need to search in. So essentially:

('val1','val2') IN (col1,col2)

but valid.

Answer 1

You could do something like this (which I've also put on SQLFiddle):

-- Test data:
WITH t(col1, col2) AS (
    SELECT 'val1', 'valX' UNION ALL
    SELECT 'valY', 'valZ'
)
-- Solution:
SELECT *
FROM t
WHERE EXISTS (
    SELECT 1
    -- Join all columns with all values to see if any column matches any value
    FROM (VALUES(t.col1),(t.col2)) t1(col)
    JOIN (VALUES('val1'),('val2')) t2(val)
    ON col = val
)

Of course, one could argue, which version is more concise.