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.

Answer 2

The return of rows 2 & 4 is correct, though your choice of (4,4) can make it a little more confusing, as it is redundant. The AND means that the row must satisfy both your conditions to be true. If the query had WHERE b IN(5,7) AND c IN(4,9), you would get rows 2, 3, and 4 returned.

If you think of it in pairs, you need to have all the combinations. e.g., b IN(5,7) AND c IN(4,9) would yield (5,4), (5,9), (7,4), and (7,9) as possible combinations that would work, and NOT just (5,4) and (7,9)