Including NULLs In Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Including NULLs In Join

Author

Topic

krisdotca
Starting Member

6 Posts

Posted - 2010-04-27 : 16:05:17

Hi There,

I have a view that joins a number of tables. Some of the join columns may have NULL values - e.g. assignedphysician.

I read here http://www.mssqltips.com/tip.asp?tip=1447 that the way to allow NULL in a join is to use something like: isnull(a.Trim,'999999') = isnull(b.Trim,'999999'). Does anyone know of a more elegant way to do that - seems like a bit of a hack to use a string that you hope will not occur in your db. I've included my big ugly join below in case it helps. :)

CREATE VIEW vw_CasesFacilitiesUsers
AS SELECT cases.CaseId, cases.CreationDate, cases.CaseTitle, cases.WorkflowStep
,patient.UserId AS PatientUserId, patient.UserName AS PatientUserName
,assignedphysician.UserId AS AssignedPhysicianUserId, assignedphysician.UserName AS AssignedPhysicianUserName
,createdbyuser.UserId AS CreatedByUserId, createdbyuser.UserName AS CreatedByUserName
,createdbyfacility.FacilityId AS CreatedByFacilityId, createdbyfacility.FacilityName AS CreatedByFacilityName
,assignedfacility.FacilityId AS AssignedFacilityId, assignedfacility.FacilityName AS AssignedFacilityName
FROM Cases AS cases
JOIN aspnet_Users AS patient ON cases.PatientUserId = patient.UserId
JOIN aspnet_Users AS assignedphysician ON cases.AssignedPhysicianUserId = assignedphysician.UserId
JOIN aspnet_Users AS createdbyuser ON cases.CreatedByUserId = createdbyuser.UserId
JOIN Facilities AS createdbyfacility ON cases.CreatedByFacilityId = createdbyfacility.FacilityId
JOIN Facilities AS assignedfacility ON cases.AssignedFacilityId = assignedfacility.FacilityId
GO

krisdotca
Starting Member

6 Posts

Posted - 2010-04-27 : 16:19:35

I think I answered my own question but if someone could confirm for me, that would be great. I modified the join to specify exactly how the NULL columns will be handled using LEFT INNER JOIN as follows:

--------------------------------------------------------------------
-- view between Cases, Facilities and aspnet_User --
--------------------------------------------------------------------
CREATE VIEW vw_CasesFacilitiesUsers
AS SELECT cases.CaseId, cases.CreationDate, cases.CaseTitle, cases.WorkflowStep
,patient.UserId AS PatientUserId, patient.UserName AS PatientUserName
,assignedphysician.UserId AS AssignedPhysicianUserId, assignedphysician.UserName AS AssignedPhysicianUserName
,createdbyuser.UserId AS CreatedByUserId, createdbyuser.UserName AS CreatedByUserName
,createdbyfacility.FacilityId AS CreatedByFacilityId, createdbyfacility.FacilityName AS CreatedByFacilityName
,assignedfacility.FacilityId AS AssignedFacilityId, assignedfacility.FacilityName AS AssignedFacilityName
FROM Cases AS cases
INNER JOIN aspnet_Users AS patient ON cases.PatientUserId = patient.UserId
LEFT OUTER JOIN aspnet_Users AS assignedphysician ON cases.AssignedPhysicianUserId = assignedphysician.UserId
INNER JOIN aspnet_Users AS createdbyuser ON cases.CreatedByUserId = createdbyuser.UserId
INNER JOIN Facilities AS createdbyfacility ON cases.CreatedByFacilityId = createdbyfacility.FacilityId
LEFT OUTER JOIN Facilities AS assignedfacility ON cases.AssignedFacilityId = assignedfacility.FacilityId
GO

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 16:20:57

quote:
Originally posted by krisdotca

Hi There,

I have a view that joins a number of tables. Some of the join columns may have NULL values - e.g. assignedphysician.

If it's the "cases.AssignedPhysicianUserId" column that has the nulls, then you can use a LEFT JOIN instead of a JOIN. A LEFT JOIN returns all rows for the left table, regardless of whether or not there's a matching record in the right table. A JOIN (or INNER JOIN) only includes records from the left table that match records in the right table (and visa versa).

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 16:22:27

Please illustrate relations and some examples.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 04:12:55

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2008/12/24/null-on-joined-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources