outer join table filtering

[Henry J.]

Is it true that the following query:

SELECT emp.empno, emp.lastname, dept.deptname
FROM emp LEFT OUTER JOIN dept
ON emp.workdept = dept.deptno
WHERE emp.salary 50000.00;

will run faster in general if rewritten as:

SELECT emp.empno, emp.lastname, dept.deptname
FROM (SELECT empno, lastname FROM emp WHERE salary 50000.00) as
e
LEFT OUTER JOIN dept
ON emp.workdept = dept.deptno

I thought the where clause in the 1st query would filter out rows in
emp before joining with dept, just like the 2nd query does? How about
this:

SELECT emp.empno, emp.lastname, dept.deptname
FROM emp LEFT OUTER JOIN dept
ON emp.salary 50000.00 and emp.workdept = dept.deptno

Thanks.

[ChrisC]

On Oct 2, 8:47*am, "Henry J." <[e-mail]:

Is it true that the following query:

* * SELECT emp.empno, emp.lastname, dept.deptname
* * *FROM emp LEFT OUTER JOIN dept
* * * * * ON emp.workdept = dept.deptno
* * * WHERE emp.salary 50000.00;

CELKO has a good discussion about the logic behind what is going on.
However, if you look at the plan that DB2 is using to generate this,
you will find that it takes several shortcuts to speed up processing.
DB2 will look at the statistics for the tables behind the query and
decide what to do based on those statistics. I suspect since you
imply that your first rewrite of the query was faster than the second
one that DB2 might have decided that joining the two tables up would
filter out more rows than applying the "emp.salary 50000.00"
condition would, and so it joined them up first and then filtered. If
the statisics were different, DB2 may decide to filter first and then
join. It may be that with better statistics with distribution turned
on that DB2 might run this query better for you.

In reply to CELKO, since this criteria is on the OUTER part of the
join, it is safe to apply the filter before the join - there is no
chance of getting extra rows this way. If the criteria was on the
inner part of the join, then yes, bad results would happen.

-Chris