IBM Mainframe Forum

Hi All,

Season's Greetings!!

I am executing a query which Left Outer Joins Table A with Table B. The Query looks like -

Select *
FROM Table A a1
LEFT JOIN Table B b1
ON a1.col1 = b1.col1
AND a1.DT BETWEEN '03/26/2011' AND '03/26/2999'
AND a1.col1='123456'.

The problem which I am facing is that the query is returning rows which do not satisfy the criteria a1.col1='123456' & hence, the unqualified rows, as per my understanding. Can somebody please help me in understanding the rationale behind it. I have already tried putting this conditon a1.col1='123456' with the where clause & then the query works as expected.

Many Thanks in Advance.

Thanks & Regards,
Nik

You already established that following code return the expected (by you) result


Now to explain why the first select returns all row of A:
That's what a left join does : it returns all rows on A and then searches for a row in B with "a1.col1 = b1.col1 and a1.DT BETWEEN '03/26/2011' AND '03/26/2999' AND a1.col1='123456'."
If no such row is found in B, it will still return the A-row with empty (null) fields for B-columns

This technique is even sometimes used as a performance trick.
For example if you know a customer of type<>"T" does not have any insurances you can code
select * from customer A left join insurance B on A.type = 'T' and a.icust = b.icust
which should avoid a search on insurance when a.type = 'T'

Thanks so much GuyC for your reply. I am so sorry but, I am still not able to understand the logic behind this. I understand that in case of a left join, all the rows of the Left table i.e. Table A in our case will be returned even if there is no corresponding row in Table B but, here, we have put the condition on Table A only.

As per your explanation, a1.col1='123456' condition is not at all honored in case of a Left Join. In that case, what is that we are achieving by using this condition. To summarise, when you say, all rows from A, then, why does the query returns rows having col1 value other than 123456.

Thanks & Regards,
Nik

Hello,

If no such row is found in B, it will still return the A-row with empty (null) fields for B-columns

The query is performing as IBM intended. . . Unfortunately, this is not always what we want

If some other results are needed and you post an explanation with a bit of data samples, someone may have a suggestion.

Hi Dick,

Thanks so much for your reply. Please find below the sample Table data.



As per my understanding the query -
should return only the row -

123456 03/26/2012 C

but, I can see 100 & 200 also in the output. That is what has confused me.Please let me know your opinion on the same. I can not modify the query since, it is already there in the production but, need to develop an understanding to predict the result.

Thanks & Regards,
Nik

Hello,

I can not modify the query since, it is already there in the production

If the query is wrong - it will need to be changed. . . Giving the business users incorrect data just because some code is already in production is not acceptable anywhere i've ever supported.

Why was the LEFT JOIN used? Suggest you find the business specifications for the process and determine if the code is incorrect or if the returned data is incorrect (or that there is really no problem).

When posting code, jcl, data, etc, you need to use the Code tag. I tried to Code your post, but you had manually "spaced" things which removed the alignment. There is a Preview function that will let you see your post as it will appear to the forum. When the post appears as you want, Submit. A little proctice and becomes automatic.

Let me try to explain it this way :
WHERE ... is a SEARCH-condition. If this evaluates to false then the row does not qualify.
ON ... is a JOIN-condition . If this evaluates to false the join fails. In case of a left join : the left row is still returned, the right one isn't. It doesn't matter if in this criteria are columns of table A, B or even from a table that has nothing to do with the join.

Hi GuyC,

Thanks for your reply. Now, I understand!!

Thanks & Regards,
Nik