multidimensional "WHERE IN" condition?

user_1640715 0
Viewed 57

As is well-known, we can simplify

SELECT * FROM A WHERE X=1 OR X=4 OR X=9

as follows:

SELECT * FROM A WHERE X IN (1,4,9)

Is there a similar shortcut possible for "multidimensional" conditions as in the following:

SELECT * FROM A WHERE (X=1 AND Y=2) OR (X=4 AND Y=3) OR (X=9 AND Y=5)

in a form resembling something like this

SELECT * FROM A WHERE [X,Y] IN ( [1,2], [4,3], [9,5] )

?

I would be willing to count anything as a simplification that uses O(n) characters where the factor is significantly smaller than in the naive example (i.e., adding one more case makes the query string grow by less than OR (X=... AND Y=...)) even though there is perhaps more constant overhead.

mysql sql
3 Answers

Yes, you may use WHERE IN (...) with tuples in MySQL:

SELECT *
FROM A
WHERE (x, y) IN ((1, 2), (4, 3), (9, 5));

Demo

user_18632290 
select *
from tbl
where (c_a, c_b) in (select c_a, c_b  from tbl_2);

here is a demo

user_65650380

You can use CONCAT function with delimiter in next way:

SELECT * FROM A WHERE CONCAT(X,'&',Y) IN ( '1&2', '4&3', '9&5' );

or more visual:

SELECT * FROM A WHERE CONCAT('[', X, ',', Y, ']') IN ( '[1,2]', '[4,3]', '[9,5]' );
user_33993560
Related