Dr. Unknown, or how I learned to stop worrying and love the NULL

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

Denis Gobo — Sat, 22 Sep 2007 21:09:08 GMT

Great article, another reason not to use IN is when you fat-finger the column name

Example:

CREATE TABLE TestTable1 (id1 int)

CREATE TABLE TestTable2 (id2 int)

INSERT TestTable1 VALUES(1)

INSERT TestTable1 VALUES(2)

INSERT TestTable1 VALUES(3)

INSERT TestTable2 VALUES(1)

INSERT TestTable2 VALUES(2)

SELECT *

FROM TestTable1

WHERE id1 IN (SELECT id1 FROM TestTable2)

It doesn't matter that the TestTable2 doesn't have a id1 column, all rows will be returned

Exist is the way to go

SELECT *

FROM t1

WHERE EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )

or a join

SELECT t1.*

FROM TestTable1 t1

JOIN TestTable2 t2 ON t2.id2 = t1.id1

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

Hugo Kornelis — Sat, 22 Sep 2007 22:17:08 GMT

Good point, Denis!

This also shows why you should always prefix all column names with the table name (or alias) in a query that touches more than one table.

BTW, the JOIN solution is only equivalent if TestTable2.id2 is constrained to be UNIQUE or PRIMARY KEY, otherwise it might generate duplicate rows that the other queries don't produce.

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

James Luetkehoelter — Mon, 24 Sep 2007 12:13:39 GMT

Any excellent post Hugo - ironically I was just about to post my next NULL post (hmm...that would make a good April Fool's joke...gotta remember that).

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

James Luetkehoelter — Mon, 24 Sep 2007 12:13:58 GMT

er, another, not any :)

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

Michael Lato — Mon, 24 Sep 2007 20:09:09 GMT

Further to your UNIQUE constraint note above, I have just spoken with some Microsoft techs at the PASS conference about precisely the same request (allow multiple NULL values in a UNQUE constraint). SQL Server 2008 will allow filtered indexes and the UNIQUE keyword is valid. You can test this in the July CTP.

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

Hugo Kornelis — Sun, 30 Sep 2007 20:29:56 GMT

Hi Michael,

Good to know that there will be some way available to us to allow multiple NULLs in a UNIQUE constraint - though I still think that this should be the default behaviour of UNIQUE constraint, as per ANSI standard.

But if I can't have full ANSI compatibility, I'll grab this with all might! <g>

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

Hugo Kornelis — Mon, 01 Oct 2007 06:17:46 GMT

The fourth (and final) part of this series about NULL is now available at http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx.

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

ScottPletcher — Wed, 03 Oct 2007 13:42:47 GMT

Great article! Informative and thought provoking. Btw, within the last day I helped someone on Experts-Exchange who had indeed used a NULL in a NOT IN list, so it does happen :-) .

The Sad State Of Programmers Part 2 : The In Person Interview

Denis Gobo — Mon, 10 Dec 2007 20:02:13 GMT

This is part two of a three part series. Part one was about the phone interview , this part is about

re: Dr. Unknown, or how I learned to stop worrying and love the NULL

phil — Tue, 08 Apr 2008 10:12:53 GMT

excellent post, you really saved my bacon trying to solve a select where name = @name (but if not specified) select all 'name' values including nulls :)... WHERE (ProductCategoryId = @ProductCategoryId)

OR (ProductCategoryId IS NULL AND @ProductCategoryId IS NULL)

OR (ProductCategoryId IS NOT NULL AND @ProductCategoryId IS NULL)