Guys,
Here is the problem!!!

SELECT Fld1 FROM SCHEMA1.TABLE1
WHERE
SCHEMA1.TABLE1.Fld1= (SELECT Fld2 FROM Schema2.TABLE2)

The two tables belong to diff schemas.
Imagine the sub-query is retrieving 20 records of Fld2 and passing them to Fld1. It gives an error which says that the query cannot be executed since the sub-query is returning multiple no. of records.
Can any one help me out.....
Thanks,
Shiva.

not sure about the two different schemas, but you could try IN instead of =

SELECT Fld1 FROM SCHEMA1.TABLE1
WHERE
SCHEMA1.TABLE1.Fld1
IN (SELECT Fld2 FROM Schema2.TABLE2)

I would always use an EXISTS clause for this kind of query. As far as I remember, using IN would cause a full table scan. I believe that this is the case for all DBMS.

SELECT Fld1 FROM SCHEMA1.TABLE1
WHERE
EXISTS
(SELECT * FROM Schema2.TABLE2
WHERE SCHEMA1.TABLE1.Fld1 = Schema2.TABLE2.Fld2 )

If I'm not talking rubbish, could somebody please explain why this is the case because it has bugged me for a long time now! Surely the optimiser should be able to rewrite an IN type query using the EXISTS syntax where the subset is the result of a SELECT?

Damian

both the EXISTS and IN forms will be executed as a JOIN anyway

again, assuming there's no difficulty operating across separate schemas

SELECT Fld1 FROM SCHEMA1.TABLE1
INNER JOIN Schema2
ON SCHEMA1.TABLE1.Fld1 = Schema2.TABLE2.Fld2


rudy

Yes, but won't the IN form initially retrieve *all* the rows of its SELECT before performing the join?

no idea, but i doubt it

you'd have to do the EXPLAIN to be sure

(they have EXPLAIN in DB2, right?)

I know it seems ridiculous but I'm quite certain that this is the case.

I've observed the optimiser taking a much more sensible path when I've re-written a query from the IN form to EXISTS.

Aside from DB2, I've had limited exposure to Informix, Ingres and Oracle and I'm sure the same is true of them. I just don't understand why!

Please put me out of my misery .

Hi,

Using DB2 V8.1.4 on windows the IN and EXISTS queries show exactly the same explain path.

Hope this helps.

I think that the optimization outcome will depend very much on the statistics of the table in question. EXISTS predicate I believe is guaranteed to select a single record (since we don't care about other records if even a single one is found). In that case index access will always be chosen (provided that an appropriate index exists). On the other hand, IN requires that all matching records are retrieved, and the optimizer may choose a table scan even if an index exists, depending on the table size and distribution statistics (or lack thereof).

If no index exists on the table then either predicate will cause a table scan; in that case the difference in the query execution time will be less significant, especially if the record that satisfies the condition is at the end of the table.

Hope this makes sense.

Nick

That would make sense. It begs the question however, if I (as a human) am able to optimise such a query by applying some very trivial logic, why should the optimiser fail?

is that true in DB2? if so, that's decidedly weird

i'd've thought that as soon as one matching row is found, the IN is satisfied, and the optimizer wouldn't have to retrieve any more

I think that's correct. However, before searching for a match the agent builds the entire result set of what's between the brackets of IN(...). At least this is what I think I remember after attending the DB2 optimization workshop :-)

I don't think it fails - it's just that its logic is probably different. You may want to try changing query optimization level to entice the optimizer to use more (or less) trivial logic :-)

I think I eluded to it earlier in this thread; this behaviour is common in other DBMS (including Oracle). It probably makes perfect sense if you are proficient with relational algebra and set theory, which is why it makes no sense to me!