Geek asks and answers

Sql server - join to null values

I have two tables. One has a list of links, and the other their styles, if available. This is later a sparse table, i.e. It does not have corresponding rows when their values ​​are zero. 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 change this so that if a row does not exist for a particular record, these columns will get null values ​​in the result set.

Thank!

+3
source share
5 answers

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
+4
source

To get NULL for non existing records, you need to use 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 connection here: Visual representation of SQL connections

+1

A leftor fulljoin will fill in the string nullif no match is found:

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

The left join fills only the right table with zeros, the right joins only the left table, and the full join fills both. For a visual illustration, see Visual Explain SQL Connections .

+1
source

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 external connections

0
source

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
0
source

More articles:


All Articles