CODEWITHSUNDEEP
mysqlsql
flymike
flymike

Reputation: 945

SQL "IN" combined with "=" in WHERE clause

I'm struggling with someone else's code. What might the WHERE clause do in the following (MySQL) statement?

    SELECT * FROM t1, t2 WHERE t1.id = t2.id IN (1,2,3)

It's not providing the desired result in my case, but I'm trying to figure what the original author intended. Can anyone provide an example of the use of a WHERE clause like this?

Upvotes: 1

Views: 129

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

This condition starts from the right, evaluates t2.id IN (1,2,3), gets the result (0 or 1), and uses it for join with t1.id. All rows of t2 with id from the IN list are joined to the row in t1 that has id of one; all other rows of t2 are joined with the row in t1 that has id of zero. Here is a small demo on sqlfiddle.com: link.

It is hard to imagine that that was the intent of the author, however: I think a more likely check was for both items to be in the list, and also being equal to each other. The equality to each other is important, because it looks like the author wanted to join the two tables.

A more modern way of doing joins is with ANSI SQL syntax. Here is the equivalent of your query in ANSI SQL:

SELECT * FROM t1 JOIN t2 ON t1.id = t2.id IN (1,2,3)

Upvotes: 4

Related Questions