Query with multiple IN-statements but without the cartesian product

Question

How could I make this kind of query e.g. in MySQL

SELECT * FROM Table t 
WHERE t.a IN (1,2,3) 
AND t.b IN (4,5,6) 
AND t.c IN (7,8,9) ...

so that the result would contain only the three rows:

t.a|t.b|t.c
---+---+---
 1 | 4 | 7
 2 | 5 | 8
 3 | 6 | 9

The above query of course returns all the combinations of the values in the IN clauses but I would like to get just the ones where the first elements of each tuple match, second elements of each tuple match and so on.

Is there any efficient way to do this?

By the way is there some common term for this kind of query or concept? I'm having hard time coming up with the question's title because I can't put this into words..

Answer 1

In MSSQL, you could do this:

SELECT * FROM Table t 
join (
        select 1 a, 2 b, 3 c
  union select 4 a, 5 b, 6 c
  union select 7 a, 8 b, 9 c
) x on t.a=x.a and t.b=x.b and t.c=x.c

I'm not sure if that will directly translate to MySQL.

The simpler way would of course be:

SELECT * FROM Table t 
WHERE (t.a=1 and t.b=4 and t.c=7)
   or (t.a=2 and t.b=5 and t.c=8)
   or (t.a=3 and t.b=6 and t.c=9)

Answer 2

select t1.a, t2.b, t3.c
from Table t1
inner join Table t2 on (t1.a = 1 and t2.b = 4) or (t1.a = 2 and t2.b = 5) or (t1.a = 3 and t2.b = 6)  
inner join Table t3 on (t2.b = 4 and t3.c = 7) or (t2.b = 5 and t3.c = 8) or (t2.b = 6 and t3.c = 9) 

Answer 3

SELECT  *
FROM    mytable
WHERE   (a, b, c) IN ((1, 4, 7), (2, 5, 8), (3, 6, 9))

, or a more index-friendly solution:

SELECT  *
FROM    mytable
WHERE   (a, b, c)  = (1, 4, 7)
        OR (a, b, c)  = (2, 5, 8)
        OR (a, b, c)  = (3, 6, 9)

Unfortunately, MySQL does not use the indexes for record types in the IN clauses.