WHERE x IN (5) vs WHERE x = 5 ... why use IN?

Question

Something I've noticed recently is people using IN where it seems kind of inappropriate. I'm feeling like I'm missing a trick - perhaps there are speed gains or something?

This query:

SELECT * FROM pages WHERE is_visible IN ($visibility)

Why use that? Why not:

SELECT * FROM pages WHERE is_visible = $visibility

Sure, if it were this:

SELECT * FROM pages WHERE is_visible 
IN ($visibility,$invisibility,$somethingelse)

Then WHERE x IN (1,2,3) is a better alternative to WHERE x = 1 OR x = 2 OR x = 3, right? But surely this is the only scenario where you should use IN?

Anyway thanks for your time - I look forward to your replies!

Answer 1

Maybe it is unknown wether $visibility refers to a single value or multiple values? Doesn't quite fit your example, but I have seen such use in other places.

Answer 2

Then WHERE x IN (1,2,3) is a better alternative to WHERE x = 1 OR x = 2 OR x = 3, right? But surely this is the only scenario where you should use IN?

No, you can use IN with a subquery as well.

... where field in (select field from another_table)

Answer 3

Maybe $visibility is dynamically generated, like this:

$visibility = implode(', ', array_map('intval', array(1, 2, 3)));

Where array(1, 2, 3) may come from untrusted sources.