Scalar sub query in db2

[nshishir]

In oracle, there is a performance improvement if scalar subqueries are
used instead of joins. Does this hold good for Db2 (8.2) too?

[Serge Rielau]

ns******@gmail.com wrote:

In oracle, there is a performance improvement if scalar subqueries are
used instead of joins. Does this hold good for Db2 (8.2) too?

Inherently a scalar subquery is not a join.
I.e. a scaar subquery will raise an error if more than one match is
found. You cannot (easily) model that with an OUTER JOIN (you need the
OUTER to handle the NULL)

DB2 will convert scalar subqueries to outer joins (with some spice added
for semantics as noted above) at its own discretion.
So obviously in DB2 a scalar subquery is far from "always better".

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[--CELKO--]

>In Oracle, there is a performance improvement if scalar subqueries are used instead of joins. Does this hold good for Db2 (8.2) too? <<

I don't think that statement is true in general for any SQL product.
There are two kinds of scalar subqueries:

1) evaluates to a constant -- (SELECT MAX(a) FROM Foobar) AS big_a

2) correlated to a containing query -- (SELECT MAX(a) FROM Foobar AS F
WHERE F.x = G.x) AS big_a_in_G

Then you can put them in the FROM clause or the SELECT clause, etc. If
I have a clustered table in Oracle, joins are already done, etc. There
are too many options for a general statement like that.