Losing Null records with Left Outer Join (SQL Server 2005)

**azimmer** · Oct 9 '07, 02:08 PM

Originally posted by smcirish

- My left table V1 will sometimes have a value of NULL for V1.prog_log_key

- I am trying to join this field, to the right table H1

FROM dbo.V_HEAD_DETA IL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID

- I want to keep the null values in the left table V1, if there is no match in H1

- the old join syntax, V1.prg_lob_key *= H1.rmap_id kept my null values

- the new join, (Left Outer Join) leaves out the NULLS

Know of any tricks for keeping the NULL values? I'm using SQL Server 2005

SELECT DISTINCT

V1.RYEAR, V1.RCAT_TYPE AS v1_rcat,
V1.RM_ID AS v1_rm_id, V1.RMAP_ID, V1.RMAP_NAME AS V1_rmap_name,
V1.IMT_ID, V1.IMT_KEY, V1.CDESC2, V1.MDESC2, V1.IDESC2 AS V1_idesc2,

V1.PROGRAM AS V1_program, V1.INS_YEAR AS V1_ins_year,
V1.S_YYYY, V1.S_MM, V1.E_YYYY, V1.E_MM,
V1.CMT_KEY, V1.prg_lob_key, H1.RCAT_TYPE, H1.RM_ID, H1.RMAP_NAME

FROM dbo.V_HEAD_DETA IL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID

WHERE (V1.RYEAR = '2007') AND
(V1.RCAT_TYPE <> 'L') AND
(V1.IMT_STATUS = 'E' OR V1.IMT_STATUS = 'A') AND
(H1.RCAT_TYPE = 'L')

Funny, I've just tried and it works fine for me in a made-up example. Can it be that your WHERE clause (and/or the data set) also changed since you tried with the old syntax? (Perhaps parenthesis were at other places?) You can try with an empty WHERE clause and sort out if you have the rows...

**nerio** · Oct 19 '07, 11:13 AM

For some reason OUTER JOIN consideres fields having both NULL values as not equal (at least in MSSQL 2000). I use this as workaround:

Code:

FROM dbo.V_HEAD_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 
ON (V1.prg_lob_key = H1.RMAP_ID or V1.prg_lob_key is null 
	and H1.RMAP_ID is null)

Losing Null records with Left Outer Join (SQL Server 2005)

Losing Null records with Left Outer Join (SQL Server 2005)

Comment

Comment