CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012multiple-columns
Piotr L
Piotr L

Reputation: 1095

Multiple columns and the IN operator

There is a table:

CREATE TABLE dbo.TestTable
    ( id INT IDENTITY(1, 1) NOT NULL
    , data1 VARCHAR(255) NULL
    , data2 VARCHAR(255) NULL
    , data3 VARCHAR(255) NULL
    )

... and some sample data:

INSERT  INTO dbo.TestTable
VALUES    ( 'some data'  , 'some other data'  , 'and another')
        , ( 'some data 2', 'some other data 2', 'and another 2')
        , ( 'some data 3', 'some other data 3', 'and another 3')
        , ( 'some data 4', 'some other data 4', 'and another 4')
        , ( 'some data 5', 'some other data 5', 'and another 5')
        , ( 'some data 6', 'some other data 6', 'and another 6')

... and finally a simple SELECT query:

SELECT *
FROM dbo.TestTable tt 
WHERE  tt.data1 IN ('x', 'y', 'z')
    OR tt.data2 IN ('x', 'y', 'z')
    OR tt.data3 IN ('x', 'y', 'z')

Note: in my actual scenario both the number of values in the IN operator and the number of dataXX columns are much larger.

As you can see, the list of values sought ('x', 'y', 'z') is repeated multiple times. I am looking for a "smarter" way of building this query, purely to avoid copying the "OR tt.dataXX in (...)" line many, many times.

Is there any way I could use the ('x', 'y', 'z') list of values just ONCE in the above SELECT query and have all tt.dataXX columns covered?

If so, what is it?

Upvotes: 1

Views: 630

Answers (1)

Maarten
Maarten

Reputation: 22945

I can't test it here, but you should be able to do something like this using the table value constructor.

SELECT *
FROM dbo.TestTable tt
WHERE EXIST (
    SELECT 1
    FROM (VALUES ('x'), ('y'), ('z')) AS b(Name)
    WHERE b.Name IN (tt.data1, tt.data2, tt.data3)
)

Upvotes: 3

Related Questions