Why there are no missing values from the right join piece of code?

[katrina11]

When I run the following code I get a bunch of DiagnosisCode values which are NULL

select *
FROM DW.dbo.Claim CL
inner join dw.dbo.Member md on md.memberid=ch.memberid
left outer join ##subset dg cl.PrimaryDiagnosisCode=dg.DiagnosisCode

However when I run the same code with a right join I do not get any empty/missing fields for PrimaryDiagnosisCode:

select *
FROM DW.dbo.Claim CL
inner join dw.dbo.Member md on md.memberid=ch.memberid
right outer join ##subset dg cl.PrimaryDiagnosisCode=dg.DiagnosisCode

Actually "DiagnosisCode" field allows null values while PrimaryDiagnosisCode from Claim table does not.

My questions are the following:

1.Could it happen because left hand of the equation is not nullable while right had is (both are varchar)?
2. If it cannot happen because of this reason then what do you thing might be a reason?
The thing is that although subset is less then claim table I still would expect at least a few missing values....

Thank you for the consideration!

Katrin

 

[gmmastros]

1.Could it happen because left hand of the equation is not nullable while right had is (both are varchar)?

The equal sign does not control which table is the left and right. The "Left" table is the table on the left side of the JOIN keyword, and the "Right" table is the one on the right side of the JOIN keyword.

I suspect there is more to the query than you are showing, which is unfortunate because there are other factors that can control the data that is returned. I say this because you have a join condition "md.memberid=ch.memberid" but you don't have any tables aliased as ch. This tells me it's not a straight copy/paste of the query you were actually running.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katrina11]

George

1. I did not mean "=" sign. Rather I meant data type,
PrimaryDiagnosisCose (varchar (8) not null)while
DiagnosisCode data type allows null.

Could it have an impact?

2. I changed left to right just have a quick look to see if I get some missing values from PrimaryDiagnosisCode

3. I excluded some inner joins (to construct Claims) for the sake of simplicity and forgot to change and allias to cl. It should not effect....

 

[gmmastros]

If you have row(s) in the DG table that do not match rows in the CL table, you should see NULLs in the PrimaryDiagnosisCode column.

One common mistake I see a lot involves where clauses with this type of query. Let's look at the RIGHT JOIN code.

select *
FROM   DW.dbo.Claim CL 
       inner join dw.dbo.Member md 
        on md.memberid=ch.memberid
       right outer join ##subset dg
         on cl.PrimaryDiagnosisCode=dg.DiagnosisCode

If a diagnosis code exists in the dg table that does not exist in the Claim table, you should still get a row that has NULL in the CL columns.

The common problem I see is when people put WHERE clause conditions on the outer table. In this case, the base table is ##Subset (dg) and the outer tables are Claim and Member. If a match does not exist with the base table, you will still get the rows. However, all the columns from the outer tables will have NULLS. So, if you have a where clause condition on the outer tables, you could end up comparing a condition with NULL, which would exclude the rows.

Ex:

select *
FROM   DW.dbo.Claim CL 
       inner join dw.dbo.Member md 
        on md.memberid=ch.memberid
       right outer join ##subset dg
         on cl.PrimaryDiagnosisCode=dg.DiagnosisCode
[!]Where  md.name = 'George'[/!]

In this case, if there is no match to the claim table, the values will be null, so there will be no match to the members table. When you compare the name to 'George', the actual value would be NULL which does not match 'George', so the row would not be returned. Often times, the fix for this situation is to put the where clause criteria in the ON clause, like this:

select *
FROM   DW.dbo.Claim CL 
       inner join dw.dbo.Member md 
        on md.memberid=ch.memberid
        [!]and  md.name = 'George'[/!]
       right outer join ##subset dg
         on cl.PrimaryDiagnosisCode=dg.DiagnosisCode

With the condition in the ON clause, you will only returns rows with NULL or 'George' in the name.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom