FROM DOCTABLE t
WHERE t.DOCREFID = ANY
FROM DOCTABLE s
WHERE s.DOCID = ?
AND s.SECTION = ?
AND s.VERSION = ?)
AND t.RENDITION <> ?
AND t.DOCCOLUMN IS NOT NULL
What does the ANY do ?
There is little to add perhaps. Since Basic SQL syntax works on any environment, I extracted this little table from the SQL Ref Guide (Db2 zOS), to help understand these strange "Quantified" Predicates that are "never" used, well not very often, unless someone wants to confuse the reader:
Table 61. IN predicate and equivalent quantified predicates
IN predicate Equivalent quantified predicate
expression IN (fullselect1) expression = ANY (fullselect1)
expression NOT IN (fullselect1) expression <> ALL (fullselect1)
I am not a big fan of IN subqueries where they are correlated unless DB2 can transform the SQL into another construct. There I would also not be a big fan of the Quantified versions with correlation (even less so). Any IN subquery that cannot be transformed to a join or EXISTS variation can be dangerous if the resulting IN list is large. Any NOT IN subquery with a large list can be dangerous.
While I never code '= ANY', it is quite rare I code IN with subquery unless am feeling lazy (and I am confident of a good access path). Much more likely I would code a join, or EXISTS with a good probe index available.
What I am finding to be quite powerful these days, is join to a correlated subquery, and I use in my own longer running queries (not for trivial queries) often:
AND correlation predicate
ORDER BY cols
FETCH FIRST 1 ROW ONLY
) AS X
ON Join Predicates
Early days this created woeful performing access paths, but is pretty good in the modern day, thanks to Optimizer team.
DB2 Application Performance Specialist