Selecting Multiple Rows in One Query with Multiple Conditions

You have 2 basic syntactical options for doing this in one query and 2 options on whether to send the values in the query or load them first in a table:

• the normal AND / OR (parentheses are redundant here but it's good to use them with OR, just in case the WHERE gets more complicated):

  WHERE (  ( x = ? AND y = ? AND z = ? )
        OR ( x = ? AND y = ? AND z = ? )
        ... 
        OR ( x = ? AND y = ? AND z = ? )
        )
  

• compact IN with "row constructor":

  WHERE (x,y,z) IN ((?,?,?), (?,?,?), ...)
  

• load the triplets in a (temporary) table and JOIN:

  CREATE (TEMPORARY) TABLE tmp_table 
  --- ;
  
  INSERT INTO tmp_table (x,y,z)
  VALUES (?,?,?), (?,?,?), ... ;
  
  SELECT t.*
  FROM my_table AS t
    JOIN tmp_table AS tmp
    ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
   ;
  

• or:

    ON  (t.x, t.y, t.z) = (tmp.x, tmp.y, tmp.z) 
  

The first 2 options are equivalent but they may differ in efficiency, depending on version. This syntax of IN with tuples (row constructors) does not use indexes most effectively in older versions. In 5.7 the optimizer identifies the two syntaxes as equivalent (see MySQL docs: Row Constructor Expression Optimization). Test!

The other choice of using a table may be better when you want to query for a number of parameters / triplets. You can also index the (temp) table. Test!

So, the basic advice is to test in your version/configuration/setup, with the tables having sizes similar to their predicted sizes, and with various number of parameters.

---

Two more ways that might be worth testing too:

• The simple UNION ALL. Since we just want to run multiple identical queries where only the parameters differ. One disadvantage is that the query gets really long and clumsy-looking if the basic query is complex and you have many triplets to check:

  SELECT t.* FROM my_table AS t
  WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
  SELECT t.* FROM my_table AS t
  WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
  ---
  SELECT t.* FROM my_table AS t
  WHERE ( x = ? AND y = ? AND z = ? ) ;
  

• Using a derived table (with UNION ALL) in the JOIN variation. This may use an optimization (that was added in 5.5 or 5.6) that can materialize and index a derived table:

  SELECT t.*
  FROM my_table AS t
    JOIN 
        ( SELECT ? AS x, ? AS y, ? AS z  UNION ALL
          SELECT ?, ?, ?  UNION ALL
          ---
          SELECT ?, ?, ?
        )
      AS tmp
    ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
   ;