Hi,

I have a situation where say I have three conditions in my query.

Select tbl1.user, tbl2.orgname from tbl1, tbl2 where tbl1.userid = ? and tbl1.location = ? and tbl1.org = tbl2.org

There is a situation where tbl1.org may not have a mapping to tbl2.org.

In this case i would still like to bring out the user and the orgname as blank instead of having the query not return any value at all.

Is there a way in which outer joins could be used to achieve this, if yes then how?

Yes .. Outer joins can do what you want ...

For the 'How?' part of your question , please refer to the IBM DB2 SQL Reference and/or Grahame Berchall's DB2 SQL Cookbook

Cheers
Sathyaram

The how part should look like this

select tbl1.user,tbl2.orgname
from tbl1 left outer join tbl2
on tbl1.org = tbl2.org
where tbl1.userid = ?
and tbl1.location = ?

The fields selected for tbl2 that do not have a mapping will contain null values.

Probably slightly better like this (not tested)...
The 'on' join will not exclude any rows in an outer join, so you would be better reducing the data set up front in a nested table select.

Damian

Very good point. Thanks for the refinement...

probably slightly better? no, only possibly slightly better

probably no difference whatsoever

in any relational database worthy of the name, a true case of six-o'-one or half-dozen-o'-t'other

leave the optimization to the optimiser

Hi,

Thanks a lot guys.

The query is working perfectly now.

I would have to disagree with you on that point. If the WHERE clause specified a field in the join it is likely that the optimiser would apply that filter before the outer join.

Typically, a WHERE clause *would* be applied after the join. I'm sure there are circumstances where the optimiser would also be able to use the table stats to rewrite the query, possibly where fields are indexed but I couldn't say for sure.

Bottom line, it's a bit naive to think that the optimiser will always do the work for you. Sometimes it helps if you can write decent SQL yourself!

Damian

My understanding is that in an indexed situation, a nested select performs not as well since once the data is in the BP it looses it's indexed access property. Can someone clarify?

My understanding is that once materialized, a nested table expression is held in memory unless it is so large that it should be written to disk. Retrieval of the data held in memory should be incredibly quick as it involves no disk IO. You would still have the benefit of any indexes used within the nested table expression, so provided you don't expect a massive, materialized results set, I don't see it impacting on performance.

PS My word is NOT final ;-)

Thanks, I was looking at it from my perspective which is a VLDB - you're probably right about reasonably sized databases where such result sets can reside in memory.