IBM Mainframe Forum

[Nik22Dec]

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

[GuyC]

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

Select all

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

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'

[Nik22Dec]

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

[dick scherrer]

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.

[Nik22Dec]

Hi Dick,

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

Select all

Table 1 -
Col1           DT
100           03/26/2012
200           03/26/2012
123456           03/26/2012
500           03/26/2012

Select all

Table 2 -
Col1            Col2
100            A
200            B
123456           C
500           D

As per my understanding the query -

Select all

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'.

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

[dick scherrer]

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.

[GuyC]

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.

[Nik22Dec]

Hi GuyC,

Thanks for your reply. Now, I understand!!

Thanks & Regards,
Nik