sql server programming: [quoted text, click to view]

No. NULL is not equal to anything, including another NULL value. You are
really saying "WHERE COL_NAME != NULL" and that will return no rows
because every value is NOT EQUAL to NULL. OTOH, if you used an IN
clause, adding a NULL value would do nothing to the query. That is, the
value is ignored because nothing is equal to NULL.

You don't have to do anything to avoid not returning NULL values using
NOT IN since you can never get one back. For example:

create table #nulltest (a int)

insert into #nulltest values (null)
insert into #nulltest values (1)
insert into #nulltest values (2)
insert into #nulltest values (3)
insert into #nulltest values (4)

select a from #nulltest where a not in (1, 2, 3, null) -- returns
nothing

select a from #nulltest where a not in (1, 2, 3) -- returns 4

drop table #nulltest





--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Is it usual for a 'not in' statement to return no rows when the subquery has
a null in it? Also, is this a distinction to the left outer join method?
TIA

JB (JB@acme.Com) writes:
[quoted text, click to view]

There are indeed gotchas with NOT IN. I prefer NOT EXISTS:

SELECT *
FROM tbl
WHERE NOT EXISTS (SELECT *
FROM tbl2
WHERE tbl1.col = tbl2.col)

You need to learn NOT EXISTS anyway, if you need to do this with a
multi-column key.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp