SQL Server Troubleshooting: Select from NULL rows

---

Preparation and test

First we will create #test and #test_link tables and will inserted data in this table

 

create table #test (cities char(30),countries char(30))
insert #test values('Tokyo','Japan')
insert #test values('Moscow','Russian')
insert #test values('New York','USA')
insert #test values('London','GB')
 
create table #test_link (city char(30),companies char(30))
insert #test_link values('Tokyo','Toyota')
insert #test_link values('Moscow','Lada')
insert #test_link values('New York','HP')
insert #test_link values('London','Central Bank')
insert #test_link values('Berlin','BMW')
 
 
select * from  #test 
 
select * from  #test_link 
 
cities  countries
---------  -----------
Tokyo  Japan  
Moscow  Russian  
New York  USA  
London  GB  
 
city  companies
---------  -----------
Tokyo  Toyota  
Moscow  Lada  
New York  HP  
London  Central Bank  
Berlin  BMW  

and with simple scripts we will select wich city not in the #test table

select * from  #test_link
 where  city  not   in  ( select cities from #test )
 
 
city  companies
------------------------------ ------------------------------
Berlin  BMW  

and for testing selecting problems NULL rows we first need add NULL data to #test table 

and then will selecting this query again 

insert #test values(NULL,NULL)
 
select * from  #test
 
cities  countries
------------------------------ ------------------------------
Tokyo  Japan  
Moscow  Russian  
New York  USA  
London  GB  
NULL   NULL
 
----the NULL data was add
 
 
 ----and now we will run  our working query again
 
 select * from  #test_link
 where  city  not   in  ( select cities from #test )
 
city  companies
------------------------------ ------------------------------

Results show nothing was selected.

What happened with our query ?

Everytime working with NULL rows gives bad results.

Root cause

For this problems in SQL SERVER have standard from ANSI SQL  "ANSI_NULLS"

For working with NULL rows we need to use SET ANSI_NULLS OFF and run our query. And then again SET ANSI_NULLS ON, then for testing select NULL rows we are need again return our query

 SET ANSI_NULLS OFF  -----------------  first we are need  disable  ANSI_NULLS
 select * from  #test_link
 where  city  not   in  ( select cities from #test )
 SET ANSI_NULLS ON  -----------------  end we are need enable ANSI_NULLS  
 
 
city  companies
------------------------------ ------------------------------
Berlin  BMW  

Result was success, column was selected.

 

---