SQL Server JOIN missing NULL values from left table-CodePudding

I have two tables in SQL Server:

  Table1:                               Table2:

  Col1(PK):      Col2:                  Col1:              
  ------------------------              ------
  1              1                      1                     
  2              <null>                 2                 
  3              5                      10                     
  4              <null>                 11                 
  5              12                     12

Now I want join these tables on Col2 but in result I want have all rows with Col2 where value is null and if not null only rows that Col2 exist in Table2.

When I try use Left Join in result have row from Col1(PK) = 3 but Col2 = 5 does not exist in Table2.

How can I achieve it?

I tried something like this, but it only returns the matched lines and ignores the null ones

select * 
from Table1 t1 
join Table2 t2 on t1.Col2 = t2.Col1 or t1.Col2 is null and t2.Col1 is null

Expected rows in result Col1(PK) 1,2,4,5. Because null in T1.Col2 is ok, and in T1.Col2 value in row 1(1) and 5(12) exist in T2.Col1

Something like If T1.Col2 == null => Is Ok Can Return If T1.Col2 != null => Check If Exists Value in T2.Col1 => Exists Is ok Can Return, Not Exists Skip

CodePudding user response：

I would use NOT NULL and EXISTS as mentioned in your description

select *
from table1 t1
where col2 is null or exists (
  select 1
  from table2 t2
  where t1.col2 = t2.col1
)

CodePudding user response：

Inner joins will return only the matching records and null values are simply ignored in a join condition. Null cannot be compared with a null, because there is no value.

Assuming that you have col2 in table2, you can try using left join.

select * 
from Table1 t1 
left join Table2 t2 on t1.Col2 = t2.Col2;