Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql

Question

Updating by @Cesar's request. Hope I understood what you want, if not, please revert. Quassnoi.

If I make an SQL query like this: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9), can I assume that MySQL will match only pairs from elements with same number in each list?

That is, (2, 3), (7, 9), ...?

For example, suppose we have a table like this:

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     1    |     2    |     3    |
 +----------+----------+----------+
 |     2    |     5    |     4    |
 +----------+----------+----------+
 |     3    |     7    |     9    |
 +----------+----------+----------+
 |     4    |     7    |     4    |
 +----------+----------+----------+
 |     5    |     2    |     9    |
 +----------+----------+----------+

Is it correct to assume that the only rows returned are 1 and 3 (and not 5)?

Answer 1

SELECT * FROM TABLE_NAME WHERE b IN(5,7) AND c IN(4,4)

This query will return rows, where b is either 5 or 7, AND c is 4.

What do you mean by "evaluation in pairs?"

Update:

I'll add one more row to the sample:

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     1    |     2    |     3    |
 +----------+----------+----------+
 |     2    |     5    |     4    |
 +----------+----------+----------+
 |     3    |     7    |     9    |
 +----------+----------+----------+
 |     4    |     7    |     4    |
 +----------+----------+----------+
 |     5    |     2    |     9    |
 +----------+----------+----------+

If you want to match the whole sets, you can use this syntax:

SELECT  *
FROM    table_name
WHERE   (b, c) IN ((2, 3), (7, 9))

This means: "return all rows where b is 2 and c is 3 at the same time, OR b is 7 and с is 9 at the same time."

In the example above, this query will return rows 1 and 3

But if you rewrite this query the other way around, like this:

SELECT  *
FROM    table_name
WHERE   b IN (2, 7)
        AND c IN (3, 9)

, this will mean "return all rows where b is either 2 or 7, AND c is either 3 or 9).

This will return rows 1, 3 and 5, since row 5 satisfies the condition for the second query but not for the first one.