Left Outer Join returns 0 rows

Dave Nance

Left Outer Join returns 0 rows
I'm looking up apars now, but sometimes you all are faster than that process. One of our developer's has a query
select * from
(select col1, col2, col3 from t1,t2,t3
where ....) as x
left outer join
(select col1, col2, col3, col4 from t4,t5
where ....) as y
on x.col1 = y.col1
and x.col2 = y.col2
This returns zero rows.
When I run the sub-queries separately I get 5 rows in X and a couple hundred in Y. So, with the outer join I should get returned the 5 rows from X and the corresponding rows from Y if they exist. When I change the left outer to full outer, I get the 5 rows matched with corresponding rows in Y. Also, when I change the "left outer" to "," and the "on" to "where" I get 5 rows returned, such as
select * from
(select col1, col2, col3 from t1,t2,t3
where ....) as x
,
(select col1, col2, col3, col4 from t4,t5
where ....) as y
where x.col1 = y.col1
and x.col2 = y.col2

I'd appreciate any ideas.

Dave Nance
First Health Services, Corp.
(804)527-6841



Mike Vaughan

Re: Left Outer Join returns 0 rows
(in response to Dave Nance)
This might shorten your search -- try looking at PQ41221.

Mike.
-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Thursday, January 11, 2001 3:16 PM
To: [login to unmask email]
Subject: Left Outer Join returns 0 rows


I'm looking up apars now, but sometimes you all are faster than that process. One of our developer's has a query
select * from
(select col1, col2, col3 from t1,t2,t3
where ....) as x
left outer join
(select col1, col2, col3, col4 from t4,t5
where ....) as y
on x.col1 = y.col1
and x.col2 = y.col2
This returns zero rows.
When I run the sub-queries separately I get 5 rows in X and a couple hundred in Y. So, with the outer join I should get returned the 5 rows from X and the corresponding rows from Y if they exist. When I change the left outer to full outer, I get the 5 rows matched with corresponding rows in Y. Also, when I change the "left outer" to "," and the "on" to "where" I get 5 rows returned, such as
select * from
(select col1, col2, col3 from t1,t2,t3
where ....) as x
,
(select col1, col2, col3, col4 from t4,t5
where ....) as y
where x.col1 = y.col1
and x.col2 = y.col2

I'd appreciate any ideas.

Dave Nance
First Health Services, Corp.
(804)527-6841

=======================



Susan Lawson

Re: Left Outer Join returns 0 rows
(in response to Mike Vaughan)
David,

When DB2 does query rewrite and changes this to an
inner join this can happen. Could you show the entire
query? The problem may lie in what is specified in the
WHERE clause - DB2 may be doing some re-write

Regards,
Susan Lawson
Yevich, Lawson and Associates, Inc (YL&A)
www.ylassoc.com


--- David Nance <[login to unmask email]> wrote:
> I'm looking up apars now, but sometimes you all are
> faster than that process. One of our developer's has
> a query
> select * from
> (select col1, col2, col3 from t1,t2,t3
> where ....) as x
> left outer join
> (select col1, col2, col3, col4 from t4,t5
> where ....) as y
> on x.col1 = y.col1
> and x.col2 = y.col2
> This returns zero rows.
> When I run the sub-queries separately I get 5 rows
> in X and a couple hundred in Y. So, with the outer
> join I should get returned the 5 rows from X and the
> corresponding rows from Y if they exist. When I
> change the left outer to full outer, I get the 5
> rows matched with corresponding rows in Y. Also,
> when I change the "left outer" to "," and the "on"
> to "where" I get 5 rows returned, such as
> select * from
> (select col1, col2, col3 from t1,t2,t3
> where ....) as x
> ,
> (select col1, col2, col3, col4 from t4,t5
> where ....) as y
> where x.col1 = y.col1
> and x.col2 = y.col2
>
> I'd appreciate any ideas.
>
> Dave Nance
> First Health Services, Corp.
> (804)527-6841
>
> =======================To
> change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can
>
>


=====
Regards,
Susan Lawson
Yevich, Lawson and Associates, Inc
www.ylassoc.com - DB2 Performance Journal

__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/