Elad Lachmi
Elad Lachmi

Reputation: 10561

sql server - join on null values

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!

Upvotes: 3

Views: 5436

Answers (5)

Andomar
Andomar

Reputation: 238048

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.

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176886

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

Upvotes: 1

heximal
heximal

Reputation: 10517

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

Upvotes: 0

Alex Aza
Alex Aza

Reputation: 78447

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

Upvotes: 0

Pankaj
Pankaj

Reputation: 10095

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

Upvotes: 4

Related Questions