Evaluating multiple 'IN' expressions in WHERE clauses in mysql

Question

Update using @Cesar request. I hope I understand what you want, if not, please come back. Quassnoi.

If I make an SQL query as follows: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9) , can I assume that MySQL will only match pairs of elements with the 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 returned rows 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 - 4 .

What do you mean by "evaluation in pairs?"

Update:

I will add another line 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 all 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 , OR b is 7 and is 9 at the same time."

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

But if you rewrite this request in another way, for example:

 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 lines 1 , 3 and 5 , since line 5 satisfies the condition of the second query, but not for the first.

Answer 2

The return of lines 2 and 4 is correct, although your choice (4.4) may make it a little more confusing as it is redundant. AI means that the string must satisfy your conditions so that they are true. If the query had WHERE b IN(5,7) AND c IN(4,9) , you would get lines 2, 3, and 4.

If you think about it in pairs, you need to have all the combinations. for example, b IN(5,7) AND c IN(4,9) will give (5,4), (5,9), (7,4) and (7,9) as possible combinations that will work, and NOT only (5,4) and (7, 9)

Answer 3

You can evaluate each condition in order, it can give you a better idea of ​​what is going on here. Your query indicates that all values ​​should be selected, where b is 5 or 7, and c is 4, so reduce the table using the 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 following condition, both must be true to select a row ( c IN (4,4) , which essentially matches 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 4

Your example does not accurately illustrate your question, but several IN clauses are not related to each other; they are evaluated sequentially, like any other WHERE clause.

So the following query

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

will match any of the following elements:

  bc
 ----
 5 4
 5 8
 7 4
 7 8 

---

IN can be seen as an abridged comparison. This means that the previous request 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 this is not quite for the reason that you assume.

Answer 5

Yes, I think you're right.

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

For large tables, several "IN" clauses will cause performance problems.

EDIT:

The above poster is correct, with IN (4, 4) pointless. You can just as easily say "And c = 4."

If you convert sentences to their logical equivalents, you will see why:

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