difference betweene in and = MYSQL

Question

Possible Duplicate:
MYSQL OR vs IN performance

I was wondering whats the difference between:

SELECT * FROM table WHERE cat = 'cat1' OR cat = 'cat2' OR cat = 'cat3'

And:

SELECT * FROM table WHERE cat in ('cat1', 'cat2', 'cat3')

Is there any difference? As I tried they both give out same result.

score 2 · Accepted Answer · answered Feb 01 '13 at 15:41

2

They are identical the IN() is just a short-hand version using listing out all of the OR statements.

answered Feb 01 '13 at 15:41

Taryn

John Conde · Answer 2 · 2013-02-01T15:50:47.497

1

IN() just makes for a much shorter, and easier to read, syntax especially when you have a lot of OR clauses.

edited Feb 01 '13 at 15:50

answered Feb 01 '13 at 15:41

John Conde

score 0 · Answer 3 · answered Feb 01 '13 at 15:44

There is one more thing that you can do quite easily with IN but you can't with =.

SELECT * 
FROM `table`
WHERE `column` IN (
    SELECT col_name
    FROM `table2` -- or the same table
    WHERE `some_column` = 5
)

So basically you search in a subset of another table, which sometimes comes in handy.

Real life usage:

You have a list of administrable user types (each with its own permissions) in another table, and you want to enforce that the user type actually exists.

3 Answers3