Why null is getting compared with null in the join condition in SQL Server 2016? It's a simple self join I am trying to do on below data



and the O/P I am getting after running following query (This is not the complete output, just showing the null part of it)

select a.col1, a.col2, a.Length1, b.* 
from [dbo].[Combine] a 
  left join [dbo].[Combine] b on a.col1 =b.col1




Why Null is getting compared and coming 9 times?
Maybe the column col1 for these rows has a literal string value of ‘NULL’ and not a missing value null indicator? - sqlraptor

You can test by adding the conditions and comparing the output of the queries:

WHERE a.col1 = 'NULL'

WHERE a.col1 IS NULL