sql server - SQL joins on Null values -

hi have 2 tables , b

table has 2 columns a1 (int) , a2(varchar(5)) table b has 2 columns b1 (int) , b2(varchar(5))

table has values

a1     a2 1      aaa null   bbb 2      ccc 3      ddd 

table b has values as

b1     b2 1      app 2      new 3      disc 

i want result as

a1     a2      b2 1      aaa     app null   bbb     null 2      ccc     new 3      ddd     disc 

note--

bear in mind im joining a1 b1 a1 has null , want null in result.

you can use called left join achieve these results. interpret it's functionality as:

give me rows table on left , if rows table on right fulfill conditions, join them. otherwise use null values in place.

so if select * left join b on ..., a on left , hence selected. b on right used if on condition satisfied. if isn't, null used in place of it's values, giving wanted!

i'd point out things might weird if b ever has null value b1. such, i'd add check this:

select a1, a2, b2 left join b   on a1 not null , a1 = b1; 

you can see fiddle running here: http://sqlfiddle.com/#!9/5ca6d/5

edit: null thing doesn't seem cause problems in mysql 5.6 since null = null false (and null != null), i'd feel weird leaving unaccounted for..