'Finding all matches for a list of literally referenced pairs [duplicate]

I'm struggling to check for the existence of several literally referenced values in the query that I am searching for in tableName. I have posted a generalized example of my attempt:

SELECT
    *
  FROM
    tableName
  WHERE column1 = 10
    AND CONCAT (column2, ",", column3) IN 
((column2_value_1, column3_value_1), 
(column2_value_2, column3_value_2));

column2_value_1 and column3_value_1 (and so on) would all be literally referenced by the values that would be found in those columns, e.g:

SELECT
    *
  FROM
    tableName
  WHERE column1 = 10
    AND CONCAT (column2, ",", column3) IN 
((123, "ABC"), 
(456, "DEF"));

How can I locate all instances in a table when I need to match two columns in the table with a list of pairs of literally referenced information?

mysql


Solution 1:[1]

Don't use CONCAT() for this case. MySQL supports tuple comparison:

WHERE column1 = 10
  AND (column2, column3) IN ((123, "ABC"), (456, "DEF"));

See https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#row-constructor-range-optimization

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bill Karwin

Related Questions