Which one to Choose | Left Outer Join or NOT in | DB2

anshuljain26 · Posted: Mon Apr 30, 2012 8:34 pm

Hi,

My DB2 version doesn't support the Except function, hence I would like to have suggestions on optimized way to construct a Query of

SQL 1
Minus
SQL 2

OPTION 1: USE NOT IN

SELECT A.EMP_NO FROM A
WHERE A.EMP_NO NOT IN
(SELECT B.EMP_NO FROM B)
WITH UR;

OPTION 2: USE OF LEFT OUTER JOIN

SELECT DISTINCT A.EMP_NO
FROM A LEFT OUTER JOIN B ON
A.EMP_NO = B.EMP_NO
WHERE B.EMP_NO IS NULL
WITH UR
;

I have compressed the 1st select, originally it is combination of 5 tables all with Inner Join.
Approx data to scan: 1million records.
Mode: Batch

Please suggest.

Thanks.

sushanth bobby · Posted: Mon Apr 30, 2012 8:55 pm

Hi Anshul,

You could do something like this also,

anshuljain26 · Posted: Mon Apr 30, 2012 9:24 pm

Thanks Sushant.

Thatls more of correlated join, but still want to learn on Performance impact and the best one.

Cheers
Anshul

dbzTHEdinosauer · Posted: Mon Apr 30, 2012 9:56 pm

well the only thing we can do is analyze the output to an EXPLAIN for both thses queries.

as of vsn 8 or so, there is a second table associated with the population of columns by EXPLAIN
that you can include.

after you have resolved all the normal 'bottle-necks',
you should understand what works best with the tables with stated attributes.

anshuljain26 · Posted: Tue May 01, 2012 9:17 am

Thanks Dick.

In that case, I would have to wait for the design to reach DBA as he is permiited to run EXPLAIN here, sadly.

Thanks!!

dbzTHEdinosauer · Posted: Tue May 01, 2012 8:13 pm

anshuljain26 · Posted: Tue May 01, 2012 9:12 pm

Dick, we do not have authroity to process BIND in the environment.

A bad message from you, highly.

anshuljain26 · Posted: Tue May 01, 2012 9:38 pm

Just to add further, it's all together a different environement. I cannot name the client for which this is being used, where we have to only send them the code. No compilcation, No testing and all!!

and just to add I know the several ways here to check the EXPLAIN and even through other tools.

Just FYI: QMF has been decomissioned here due to cost factors.

and I do not understand why did you get sad for DBA's..I never meant anything there..
Guess you have had something .... for the day.

Cheers!!

dbzTHEdinosauer · Posted: Tue May 01, 2012 10:11 pm

no, i have had a good day.

as far as this thread is concerned,
1. the first thing anyone does when attempting to tune sql,
is get an EXPLAIN output and do some thinking.

2. as you have only provided excuses as to why you are right and we (i) are(am) wrong,
instead of anything that could be used to provide you an answer,
i bid you good luck and goodbye.

anshuljain26 · Posted: Tue May 01, 2012 10:14 pm

Dick, you being 65, I can't fool you. But this is how Change Technical Design is drafted here..