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

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)

Answer 2

Yes, I believe you are correct.

Effectively, 'IN' can be considered shorthand for (b = 5 OR b = 7). This is NOT how it works 'under the hood', but it is an easy way to think of it.

For large tables, multiple 'IN' clauses will cause performance issues.

EDIT:

The above poster is correct, c IN (4, 4) is pointless. You could just as easily say 'AND c = 4'.

If you convert the clauses to their logical equivalents, you see why:

SELECT * FROM table WHERE (b = 5 OR b = 7) AND (c = 4 OR c = 4)

Answer 3

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 4

You can evaluate each condition in order, it might give you a better idea on what is happening here. Your query states that all values should be selected where b is either 5 or 7 and c is 4, so let's reduce the table using first condition (b IN (5,7)):

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     1    |     2    |     3    | < No match
 +----------+----------+----------+
 |     2    |     5    |     4    | < Match
 +----------+----------+----------+
 |     3    |     7    |     9    | < Match
 +----------+----------+----------+
 |     4    |     7    |     4    | < Match
 +----------+----------+----------+

Now, let's evaluate the next condition, both must be true in order for a row to be selected (c IN (4,4), hich is essentially the same as c = 4):

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     2    |     5    |     4    | < Match
 +----------+----------+----------+
 |     3    |     7    |     9    | < No match
 +----------+----------+----------+
 |     4    |     7    |     4    | < Match
 +----------+----------+----------+

Everything else is valid:

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     2    |     5    |     4    |
 +----------+----------+----------+
 |     4    |     7    |     4    |
 +----------+----------+----------+

Answer 5

Your example does not exactly illustrate your question, but multiple IN clauses are not related to one another; they are evaluated in sequence like any other WHERE clause.

Thus, the following query

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

will match any of the following:

b  c
----
5  4
5  8
7  4
7  8

---

IN can be considered shorthand for or-separated comparisons. This means the previous query can also be written as (the mechanics are slightly different, but the concept is the same):

SELECT * FROM FOO WHERE (b = 5 OR b = 7) AND (c = 4 OR c = 8)

---

So, in your example, yes, the only rows returned are 2 and 4. But it is not quite for the reason you suppose.