|
|
|
|
 Click here to copy the following block | set ansi_defaults on
go
create table #main_tbl(i int)
create table #in_tbl(i int)
insert #main_tbl values(1)
insert #main_tbl values(NULL)
insert #in_tbl values(2)
insert #in_tbl values(NULL)
select *from #in_tbl
select *from #main_tbl
go
Print 'Results with NULL in the main table & in_tbl...'
Print ''
select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)
select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )
select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go
Print 'Results with NULL in the in_tbl only...'
Print ''
update #main_tbl set i = 2 where i is null
select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)
select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )
select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go
Print 'Results with NULL in the main table only...'
Print ''
update #main_tbl set i = null where i = 2
update #in_tbl set i = 3 where i is null
select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)
select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )
select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go |
|
|
|
|
|
|
Submitted By :
Nayan Patel
(Member Since : 5/26/2004 12:23:06 PM)
|
 |
|
|
Job Description :
He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting. |
|
View all (893) submissions by this author
(Birth Date : 7/14/1981 ) |
|
|