THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

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

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2007/09/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null/

Published Saturday, September 22, 2007 11:35 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

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

September 22, 2007 5:09 PM
 

Hugo Kornelis said:

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.

September 22, 2007 6:17 PM
 

James Luetkehoelter said:

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).

September 24, 2007 8:13 AM
 

James Luetkehoelter said:

er, another, not any :)

September 24, 2007 8:13 AM
 

Michael Lato said:

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.

September 24, 2007 4:09 PM
 

Hugo Kornelis said:

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>

September 30, 2007 4:29 PM
 

Hugo Kornelis said:

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.

October 1, 2007 2:17 AM
 

ScottPletcher said:

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 :-) .

October 3, 2007 9:42 AM
 

Denis Gobo said:

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

December 10, 2007 3:02 PM
 

phil said:

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)

April 8, 2008 6:12 AM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement