sql server - join on null values

Question

I have two tables. One has a list of links and the other one holds thier styles if available. The later is a sparse table, i.e. it does not have corresponding rows when their values are null. I run the following query:

select hl.*, hls.colorCode, hls.bold
from HeaderLinks hl, HeaderLinkStyles hls 
where hl.LinkId = hls.linkID
order by row asc, [column] asc

I want to modify this so that if a row does not exist for the specific record, these columns will receive null values in the result set.

Thank you!

get more info about the join visually : http://stackoverflow.com/questions/3308122/how-do-i-decide-when-to-use-right-joins-left-joins-or-inner-joins-or-how-to-dete/3308153#3308153- i have already pasted in my answer also — Pranay Rana, Jun 01 '11 at 09:04
Stop using implicit joins. They are very poor programming practice and were replced in 1992! — HLGEM, Jun 01 '11 at 18:10

score 4 · Accepted Answer · answered Jun 01 '11 at 08:55

4

Left Join

Select hl.*, hls.colorCode, hls.bold 
From HeaderLinks hl
Left Join HeaderLinkStyles hls on hl.LinkId = hls.linkID
order by row asc,[column] ASC

answered Jun 01 '11 at 08:55

Pankaj

9,749
32
139
283

Thank you! I have not used joins in a while, I should get reacquainted :) – Elad Lachmi Jun 01 '11 at 09:01
2

@Elad Lachmi, you should not be writing queries that don't use explicit joins. Implicit joins are more likley to be harder to maintain and will have more bugs due to the ease with which they create accidental cross joins. Ther is not excuse to ever write an implicit join again. They are simply a bad programming practice like using Go To. – HLGEM Jun 01 '11 at 18:13

score 1 · Answer 2 · answered Jun 01 '11 at 08:56

To get the NULL for not exist records you need to use either LEFT OUTER JOIN or RIGHT OUTER JOIN on the table.......

Select hl.*, hls.colorCode, hls.bold From HeaderLinks hl
Left Join HeaderLinkStyles hls on hl.LinkId = hls.linkID order by row asc,[column] ASC

enter image description here

Check joins over here : Visual Representation of SQL Joins

score 1 · Answer 3 · answered Jun 01 '11 at 08:57

A left or full join will fill a row with null when no match is found:

select  *
from    HeaderLinks hl
full outer join
        HeaderLinkStyles hls 
on      hl.LinkId = hls.linkID

A left join only fills the right hand table with nulls, a right join only the left hand table, and a full join fills both. For a visual illustration see A Visual Explanation of SQL Joins.

score 0 · Answer 4 · answered Jun 01 '11 at 08:55

0

You need to use left outer join

select hl.*, hls.colorCode, hls.bold
from HeaderLinks hl
    left join HeaderLinkStyles hls on
      hl.LinkId = hls.linkID  
order by row asc, [column] asc

Using Outer Joins

answered Jun 01 '11 at 08:55

Alex Aza

76,499
26
155
134

score 0 · Answer 5 · answered Jun 01 '11 at 08:56

0

You need to use LEFT JOIN

Select 
  hl.*, 
  hls.colorCode, 
  hls.bold 
from 
  HeaderLinks hl 
LEFT JOIN 
  HeaderLinkStyles hls on hl.LinkId = hls.linkID
order by 
  row asc,[column] ASC

answered Jun 01 '11 at 08:56

heximal

10,327
5
46
69

sql server - join on null values

5 Answers5