Query with multiple IN statements but no Cartesian product

Question

How can I make such a request, for example? 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 contains only three lines:

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

The above query, of course, returns all combinations of values ​​in IN clauses, but I would like to get only those that match the first elements of each tuple, secondly, the elements of each match, etc. .

Is there an effective way to do this?

By the way, is there a general term for this kind of query or concept? It’s not easy for me to come up with the name of the question because I cannot say it in words.

Answer 1

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

or a more convenient search 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)

, MySQL IN.

Answer 2

MSSQL :

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

, MySQL.

, , :

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 3

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)