Multi-column SQL Filtering

Question

I have a MySql table that I want to query for rows in which the columns of the pair are in a specific set. For example, let's say my table looks like this:

id | f1 | f2 ------------- 1 | 'a' | 20 2 | 'b' | 20 3 | 'a' | 30 4 | 'b' | 20 5 | 'c' | 20 

Now I want to extract lines in which the pair (f1, f2) is either ('a', 30) or ('b', 20), namely lines 2,3, 4. I also want to do this using a style filter "IN" since I can have many pairs to extract. If I try something like:

SELECT * FROM my_table WHERE f1 IN ('a','b') AND f2 IN (30, 20)

I get the Cartesian product of the values ​​given for f1 and f2 in the IN clauses, that is, the rows with all possible combinations for f1 = 'a' or 'b' and f2 = 30, 20, so row 1 is also selected.

In short, I need something like:

SELECT * FROM my_table WHERE (f1,f2) IN (('a',30), ('b',20))

with valid SQL syntax only :-)

Any ideas?

Answer 1

This is a valid syntax.

If you do not like other alternatives:

 SELECT * FROM my_table WHERE (f1, f2) = ('a', 30) OR (f1, f2) = ('b', 20) 

Or using the connection:

 SELECT * FROM my_table T1 ( SELECT 'a' AS f1, 30 AS f2 UNION ALL SELECT 'b', 20 ) T2 ON T1.f1 = T2.f1 AND T1.f2 = T2.f2 

Answer 2

 SELECT * FROM my_table WHERE (f1= 'a' AND f2=30) OR (f1='b' AND f2=20); 

Answer 3

A crude but practical way is to use string concatenation:

 SELECT * FROM MyTable WHERE CONCAT(f1, '_', f2) IN ('a_30', 'b_20')