Hi,
Can anyway tell me is it better to do:

SELECT Lastname, workdept
FROM Employee
WHERE workdept IN ('E11', 'E21')

and

SELECT Lastname, workdept
FROM Employee
WHERE workdept = 'E11' OR workdept = 'E12'

Do an Explain and find out.

and this will tell you whether there is a performance difference

in addition to that consideration, there is the question of query clarity, comprehension, and maintainability -- for these, the IN syntax is ~much~ preferred

Let's say that (in your example) "IN" will never be less performant than "OR", for any RDBMS and version.

Hi,
Just ran two similar queries to above...

1.
SELECT EMPID, NAME, DEPT FROM EMPLOYEES WHERE EMPID=0;

AND

2.
SELECT EMPID, NAME, DEPT FROM EMPLOYEES WHERE EMPID IN(0);

This table is pretty simple has no indexes and very little data.
Exact same explain / access plan for both queries. Even the number of timerons where the same.

Then I change EMPID to be an index. Again exact same explain plan for both.

I am just putting this information so anyone who is reading this thread gets the conclusion that different syntax does not mean different explain plan.

Cheers.

in the first example you stated
WHERE workdept IN ('E11', 'E21')
next sample
WHERE workdept = 'E11' OR workdept = 'E12'
this will have a different access plan

this is not the same as single values without OR

wow, if that's true, then DB2 is a totally weird database system

all the other database systems i've worked with will use the exact same plan for them

of course, because of the values, they will produce different results, but that's a different issue

I'd say "it may have a different access plan, depending on many things".