BETWEEN Versus > and < in DB2 SQL Query - Performance

chinnielr · New User Joined: 11 Jul 2005 Posts: 87

Hi All,

I have the following querys:

SELECT ID, ADDRESS
FROM EMPLOYEE A
WHERE ID=12345
AND CURRENT DATE BETWEEN A.EFF_DT AND A.EXP_DT

SELECT ID, ADDRESS
FROM EMPLOYEE A
WHERE ID=12345
AND CURRENT DATE > A.EFF_DT AND CURRENT DATE < A.EXP_DT

Out of these two queries which query yields better performance.

Here I am using operators > and < instead of BETWEEN.

Please suggest.

Thanks,
Chinni.

ashimer · Posted: Thu Jul 10, 2008 2:59 pm

Do an EXPLAIN on your query and see the o/p in PLAN_TABLE and DSN_STATEMNT_TABLE ...
BETWEEN is usually more efficient than <= predicate and the >= predicate
Except when comparing a host variable to 2 columns

I guess this concept was bought in DB2 versions after version 6 ... bfore that > and < used to perform better ... i read this in some blog by craigmullins ...

dbzTHEdinosauer · Posted: Thu Jul 10, 2008 3:12 pm