SQL Query - "in" clause vs "="

[SAM KUMAR]

Hi:

I was wondering whether "IN" clause is faster or "EQUAL" is faster. For ex:

Query #1
SELECT * FROM dept
WHERE dept_name = 'SALES'

Query #2
SELECT * FROM dept
WHERE dept_name in ('SALES')

In some scneario, I need to pass multiple dept_name, in that case I need to use "IN" clause.

I was wondering if there is any performance impact if I use "IN" clause even though I am passing one dept name?

Thanks in advance.

SK

[Jeanne Boyarsky]

Sam,
In terms of performance, both should be the same. I have tried it in db2 and Oracle, so I know that the optimizer does the same thing in those databases.

Having said that, you should use a prepared statement so you gain the efficiency when repeating the same number of in clause parameters.
For example, for one dept, your string would be:
SELECT * FROM dept WHERE dept_name in (?)
For three depts, your string would be:
SELECT * FROM dept WHERE dept_name in (?,?,?)
and so forth.

[Stefan Wagner]

I guess it's rdbms-dependent.

And you may replace:

...AND name IN ('zappa', 'joplin', 'jagger');
-- with
...AND (name = 'zappa'
   OR name = 'joplin'
   OR name = 'jagger');

It should be easy to write a performance-test for your rdbms.

[Jeanne Boyarsky]

The examples that Stefan posted also perform the same in DB2 and Oracle.