Losing Null records with Left Outer Join

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)

Losing Null records with Left Outer Join

Author

Topic

smcirish
Starting Member

3 Posts

Posted - 2007-10-08 : 15:30:06

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

- 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?

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_DETAIL_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')

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-08 : 16:09:16

this happens because the results of an ANSI OUTER JOIN are not the same as the old style outer join syntax (*= and =*).

The reason is that in the ANSI OUTER JOIN syntax the join is performed first because it is specified in the FROM clause. The old style syntax had the join being specified in the WHERE clause. In the old syntax you could not specify that a join be performed before other WHERE clause operations. It was up to the optimizer to determine which order to perform the join and other WHERE clause filtering operations - the result of which is what you see here.

btw, ANSI INNER JOINs results will be the same as when you use the old JOIN syntax in the WHERE clause. It is only with OUTER JOINs that the resultsets can be different.

-ec

sshelper
Posting Yak Master

216 Posts

Posted - 2007-10-08 : 16:09:41

How about putting another condition in the join:

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

SQL Server Helper
http://www.sql-server-helper.com

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-08 : 16:11:00

Try:

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_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID
AND(H1.RCAT_TYPE = 'L')
WHERE (V1.RYEAR = '2007') AND
(V1.RCAT_TYPE <> 'L') AND
(V1.IMT_STATUS = 'E' OR V1.IMT_STATUS = 'A')

Future guru in the making.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-08 : 16:24:42

quote:
Originally posted by Zoroaster
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
AND(H1.RCAT_TYPE = 'L')
WHERE (V1.RYEAR = '2007') AND
(V1.RCAT_TYPE <> 'L') AND
(V1.IMT_STATUS = 'E' OR V1.IMT_STATUS = 'A')

How can you specify WHERE clause conditions (AND(H1.RCAT_TYPE = 'L') in the FROM clause?

-ec

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-08 : 16:32:18

I obviously can't test the above but I had a similar problem before where I was provided a recommendation from a colleague to structure it in a similar fashion. Essentially to make sure the unpreserved table is referenced on the ON clause rather then the WHERE clause, I can't tell you anything beyond that.

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-08 : 16:43:51

I found something on the web referencing this method here, with the legend himself piping in (Celko):
http://www.thescripts.com/forum/thread144808.html

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 20:18:07

"How can you specify WHERE clause conditions (AND(H1.RCAT_TYPE = 'L') in the FROM clause?"

Its in the JOIN (and for the H1 table), so that's OK isn't it?

I do that often!

OUTER JOIN SomeTable AS H1 ON SomePK matches, but only where H1.Status = This_Or_That

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:29:36

[code]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_DETAIL_Program AS V1
LEFT JOIN dbo.T_ROAD_HEAD AS H1 ON H1.RMAP_ID = V1.prg_lob_key
AND H1.RCAT_TYPE = 'L'
WHERE V1.RYEAR = '2007'
AND V1.RCAT_TYPE <> 'L'
AND V1.IMT_STATUS IN ('E', 'A')[/code]

E 12°55'05.25"
N 56°04'39.16"

smcirish
Starting Member

3 Posts

Posted - 2007-10-10 : 09:45:15

Thanks Zoroaster!!
The code you provided is working.

-smcirish

quote:
Originally posted by Zoroaster

Try:

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_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID
AND(H1.RCAT_TYPE = 'L')
WHERE (V1.RYEAR = '2007') AND
(V1.RCAT_TYPE <> 'L') AND
(V1.IMT_STATUS = 'E' OR V1.IMT_STATUS = 'A')

Future guru in the making.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 09:52:43

More on criteria and LEFT OUTER JOINS:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

and

http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

- Jeff
http://weblogs.sqlteam.com/JeffS

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-10 : 10:13:07

quote:
Originally posted by smcirish

Thanks Zoroaster!!
The code you provided is working.

-smcirish

No problem, glad I could help.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources