DB2 SQL query

[leo6]

Hi everybody,

I have a DB2 query as follows . Basically it goes for a tablescan for TAB4 which is the table in the
subquery under NOT EXISTS. Can the whole query be replaced using INNER JOIN between TAB1,TAB2,TAB3
and a LEFT OUTER JOIN for TAB4 ? Could anyone help me in this ? Thank you.
SELECT
a.col1 , a.col2 , a.col3
FROM
TAB1 a, TAB2 b,TAB3 c
WHERE
a.col1 = b.col1 AND
a.col2=c.col2 AND
a.col3 = 'X' AND
a.col4 = 'Y' AND
b.col4 = 'M' AND
NOT EXISTS
(
SELECT * FROM TAB4 d
WHERE d.col1 = a.col1 and
d.col6 = a.col6 and
d.col2 = a.col2 and
d.col10 = 'CDE' and
d.col11 >= '1990-05-30'
)

 

[PruSQLer]

Is the intent to grab rows only when there is no match between your TAB1 and TAB4 parameters? If so, then yes, you can Left Outer Join TAB1 to TAB4 and put "d.col1 is NULL" in the WHERE clause.

At least it appears that way from a quick perusal

 

[ddiamond]

Something like this might work.

SELECT  
    a.col1, a.col2, a.col3 
FROM TAB1 a
  INNER JOIN TAB2 b ON
    a.col1 = b.col1 
  INNER JOIN TAB3 c ON
    a.col2 = c.col2  
  LEFT JOIN TAB4 d ON
    d.col1 = a.col1 and 
    d.col6 = a.col6 and  
    d.col2 = a.col2 and 
    d.col10 = 'CDE' and 
    d.col11 >= '1990-05-30' 
WHERE
    a.col3 = 'X' AND
    a.col4 = 'Y' AND 
    b.col4 = 'M' AND 
    d.col1 is null

There will be a slight difference in the results, however. Your query with the exists clause will only return results if none of the matching d records are CDE with >=1990-05-03. The query with the left join will return results if any of the matching d records are not CDE and not >=1990-05-03.

 

[juliane26]

I actually like the original SQL statement best. always prefer NOT EXISTS over an outer join.
if tablescans happen - how about an index ?
and just in case: did you update your statistics ?

if you want to try the left outer join - ddiamond gave you the statement

Juliane