Problem With Outer Join

[MattSmithProg]

Hi,

I have a funny problem that I can't seem to solve.

I have two tables. I am trying to do a Left Outer Join on these tables. My understaning of Outer joins is that, in a Left Outer Join you will get all of the Left table in the query with matched columns if available to the right table and null values for unmatched columns. Therefore I would expect that if I do a query on the Left table by itself according to the where clause that I use for the Outer Join I should get the same amount of rows as the Outer Join would produce. I don't!!!!!

My original query is this.

Select PPC.TFN, PPC.Yr, PPC.NATCODE, PPC.CHANGEINTAX, PPC.CHANGEINPENALTY, PPC.CHANGEININTEREST, PPC.CHANGEINTAXINC
FROM KDAPROD.UAGLE_CASMAN_ACHIEVEMENT PPC
WHERE PPC.FINALISED_DT BETWEEN '01/07/2001' AND '22/04/2002' AND PPC.NATCODE IN ('8-SCHM-A-ELB', '8-SCHM-S-ELB', '8-SCHM-V-ELB')
ORDER BY PPC.TFN, PPC.YR, PPC.NATCODE

Which returns 2099 rows.

And my Outer Join query is this.

Select PPC.TFN, PPC.Yr, PPC.NATCODE, PPC.CHANGEINTAX, PPC.CHANGEINPENALTY, PPC.CHANGEININTEREST, PPC.CHANGEINTAXINC, ACR.Finalisation_Result
FROM KDAPROD.UAGLE_CASMAN_ACHIEVEMENT PPC
LEFT OUTER JOIN PTAPP_ACR_Inactive ACR ON PPC.TFN = ACR.TFN AND PPC.NATCODE = ACR.NAT
WHERE PPC.FINALISED_DT BETWEEN '01/07/2001' AND '22/04/2002' AND PPC.NATCODE IN ('8-SCHM-A-ELB', '8-SCHM-S-ELB', '8-SCHM-V-ELB')
ORDER BY PPC.TFN, PPC.YR, PPC.NATCODE

Which returns 2113 rows.

Does anyone have an explanation. I have racked my brain for the last week and can't explain.

Thanks Matt Smith

No two nulls are the same

 

[bperry]

This is because the Left Join returns all records from the left table PPC (whether there is a match or not), plus ALL matching rows in the right table ACR.

So, if a record in PPC has 1 row, and ACR has two rows that match PPC, then two rows are returned by the query.

In your case, there may be some PPC rows that do not have a ACR match, but there are also some PPC rows that have more than one match in ACR. Altogether, it looks like there are 2113 - 2099 = 14 extra records in ACR that match rows in PPC.

Hope this helps,
bperry

 

[bperry]

P.S.
I meant to add this is very normal behavior of Left Joins. You often will see more records as a result of the Left Join. Nothing unusual at all: that is the way Left Joins work.

bperry

 

[MattSmithProg]

Thanks bPerry,

Your wisdom as usual is enlightening Matt Smith

No two nulls are the same

 

[bperry]

Hardly.
glad to help a bit.
bp