Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.