Why do I get an error when I select multiple values across multiple columns using IN but not when us-CodePudding

I want to get a specific subset of data from my table based on values I specify across 3 columns.

The following SQL is returning the expected results (but only 1 record from the table).

SELECT *
FROM myTable
WHERE (col1, col2, col3) = (value1, value2, value3);

Example of results:

Column 1	Column 2	Column 3	Column 4
value 1	value 2	value 3	value 4

Now all I want to do is return multiple records from the table with specified values for col1, 2 and 3.

I changed the above query as follows:

SELECT *
FROM myTable
WHERE (col1, col2, col3) IN ((value1, value2, value3), (value5, value6, value7));

I was expecting 2 rows from my table to return. An example of my expected results:

Column 1	Column 2	Column 3	Column 4
value 1	value 2	value 3	value 4
value 5	value 6	value 7	value 8

But my query gives me the following error: SQL Error [42601]: [SQL0104] Token 'value1' was not valid. Valid tokens: (.

Please help with correct syntax.

CodePudding user response：

When you have multiple rows to code you it must be preceded by a values keyword like below

SELECT *
  FROM myTable
 WHERE (col1, col2, col3) IN (
  values (value1, value2, value3), (value5, value6, value7)
);

CodePudding user response：

I'd try:

SELECT *
FROM myTable
WHERE (col1, col2, col3) IN (value1, value2, value3)
   OR (col1, col2, col3) IN (value5, value6, value7);

This one isn't using the (optional) Feature "T051, Row types"