Mainframe DB2 SQL IN list multiple columns

In DB2 version 7 a feature called row value expression was introduced. A row value expression is a list of columns or constants which you can compare with another list of columns or constants with the same number of elements. You may also compare the list with the result of an IN subselect.

Let me give you an example which hopefully is far more explanatory than a long verbal description:

...
WHERE A = 3
AND B = 'THE SUN'

may be written as:

...
WHERE (A, B) = (3, 'THE SUN')

using a row vaule expression. The above rewrite may save some keyboard work and in other sitiations it may make your SQL more readable. When I explore such new inventions in DB2 I cannot wait to try them out in other contexts. Unfortunately the only other legal context is combined with an IN subselect:

...
WHERE (A, B) IN
( SELECT X, Y
FROM ANOTHER_TABLE
)
When I executed the above SQL statement it took a very, very long time before I got a reply while:...
WHERE EXISTS
( SELECT 0
FROM ANOTHER_TABLE
WHERE (A, B) = (X, Y)
)

gave me a reply right away. Therefore I will recommend you to exercise great care when using a row value expression in combination with an IN subselect. I have not tried it on DB2 version 8 or DB2 9. Maybe these versions have improved. To be honest I cannot see the value of the IN subselect combined with the row value expression because an EXISTS does the job just as good with better performance in most cases. Please note that the IN version allows for non-correlated subselects while the EXISTS version will always be correlated. Please consult the explain output and try the non-correlated version before putting it into production, because it may perform very badly.