I'm faced here at work (IBM) with the fact that the runtime of a SQL query
can be reduced from 7 minutes to 2-3 seconds if <conditions> is moved from
the join to the where clause. (b/c of the left outer join "<conditions>" was
slightly changed into "B.ID is null or (<conditions>)"

Now I'm wondering whether in general it is better to use the where clause or
what else makes the difference (or why db2 can't optimize it itself) ?

--
MfG/Sincerely
Toralf Förster
pgp finger print: 7B1A 07F4 EC82 0F90 D4C2 8936 872A E508 7DB6 9DA3

You compared...

query (1):
A left outer join B
on B.ID = A.ID
and <conditions>

with

query (2):
A left outer join B
on B.ID = A.ID
where B.ID is null
or (<conditions>)

If <conditions> contain a condition for A,
those two queries would not be equivalent.

Because, wheather a row of A does or doesn't satisfy the condition for
A,
all rows of A would be included in the result of query (1).
This nature of outer join is often forgot.

But a row of A that didn't satisfy the condition for A
and there was a row of B matching with the row,
the row of A would not be included in the result of query (2),

In conclusion,
the result set of (2) would be same or smaller than the result set of
(1).

On Nov 3, 8:31*pm, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

Those two queries would not be equivalent.
The result set of (b) would be same or smaller than the result set of
(a).

Because, wheather a row of A does or doesn't satisfy the
<conditions>,
all rows of A would be included in the result of query (a).
This nature of outer join is often forgot.

But,
a pair of a row of A and a row of B which didn't satisfy the
<conditions>
would not be included in the result of query (b).

On Nov 3, 5:01*am, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
In other words, if the conditions in the outer join would have caused
it not to join, then you will get the row from A returned.
In teh second query where conditions are not matched in the where
cause, the row from A is not returned.

Thx for the answers, if I understood it correctly, I've to verify, if it is
ok to move <conditions> for this special purpose.

--
MfG/Sincerely
Toralf Förster
pgp finger print: 7B1A 07F4 EC82 0F90 D4C2 8936 872A E508 7DB6 9DA3

These are not the same query!

Get a copy of SQL FOR SMARTIES where I work out a full example with
all the steps to show the differences. There is usually a copy
sitting around the office at every IBM shop I have visited.

On Nov 3, 9:01*pm, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

Query (1) or (a):
------------------------------ Commands Entered
------------------------------
WITH
a(id , col_x) AS (
VALUES
(1 , 10)
, (2 , 25)
, (3 , 35)
, (4 , 40)
, (5 , 55)
)
,b(id , col_y) AS (
VALUES
(1 , 11)
, (2 , 13)
, (4 , 14)
, (5 , 22)
)
SELECT a.* , b.*
FROM a
LEFT OUTER JOIN
b
ON b.id = a.id
AND (MOD(col_x, 10) = 0
AND
MOD(col_y, 2) = 1
)
;
------------------------------------------------------------------------------

ID COL_X ID COL_Y
----------- ----------- ----------- -----------
1 10 1 11
2 25 - -
3 35 - -
4 40 - -
5 55 - -

5 record(s) selected.

Query (2) or (b)
(using same data):
SELECT a.* , b.*
FROM a
LEFT OUTER JOIN
b
ON b.id = a.id
WHERE b.id IS NULL
OR (MOD(col_x, 10) = 0
AND
MOD(col_y, 2) = 1
)
;
------------------------------------------------------------------------------

ID COL_X ID COL_Y
----------- ----------- ----------- -----------
1 10 1 11
3 35 - -

2 record(s) selected.

Test data were made as followings:

WITH
a(id , col_x) AS (
VALUES
(1 , 10)
, (2 , 25)
, (3 , 35)
, (4 , 40)
, (5 , 55)
)
,b(id , col_y) AS (
VALUES
(1 , 11)
, (2 , 13)
, (4 , 14)
, (5 , 22)
)
SELECT .....
FROM a
LEFT OUTER JOIN
b
ON b.id = a.id
AND (MOD(col_x, 10) = 0
AND
MOD(col_y, 2) = 1
)
;
------------------------------------------------------------------------------

ID COL_X ID COL_Y cond for A cond for B
----------- ----------- ----------- ----------- ---------- ----------
1 10 1 11 Yes Yes
2 25 - - No Yes
3 35 - - No matched row in B
4 40 - - Yes No
5 55 - - No No

5 record(s) selected.

Tonkuma wrote:

Great example - thx Tonkuma.

Now I think I understood it.

--
MfG/Sincerely
Toralf Förster
pgp finger print: 7B1A 07F4 EC82 0F90 D4C2 8936 872A E508 7DB6 9DA3