<pedrocorreia.net ⁄>
 

<NULL Trouble In SQL Server Land ⁄ >




clicks: 3681 3681 2006-10-28 2006-10-28 goto programacao myNews programacao  Bookmark This Bookmark This


A couple of mistakes that people do when working with NULL data. If you have NULLS in a table did you know that COUNT(*) will include the NULLS but COUNT(column) will not. That and more can be found in this article.

I am seeing a lot of searches for SQL + Nulls from this site so I decided to blog about it.
Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on ,not to off.

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

--We get back value 1 here
SELECT * FROM testjoin WHERE ID IN(SELECT ID FROM testnulls)

--Nothing is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)

--Value 3 is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)


--value 3 is returned
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)


--value 3 is returned
SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL



--a count of 3 is returned
SELECT COUNT(*) FROM testnulls

-- a count of 2 is returned, the count ignores NULL values
SELECT COUNT(id) FROM testnulls

--By using coalesce the count is also 3
SELECT COUNT(COALESCE(id,0)) FROM testnulls

--all 3 rows are returned
SELECT * FROM testnulls

-- 1 row is returned
SELECT * FROM testnulls
WHERE ID = 1



este é só um excerto do artigo, para aceder ao artigo completo, clique no link em baixo:
this is just a small excerpt from the article, to access the full article please click in the link below:

http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-l...




Subscribe News RSS  Subscribe News Updates by E-mail





myNews <myNews show="rand" cat="programacao" ⁄>

RouterJs: easy routing for your ajax Web applications new ...

RouterJs is a simple router for your ajax web apps. It's build upon History.js which means that Rout (...)

clicks: 17061 17061 2012-05-14 2012-05-14 goto url (new window) haithembelhaj.g... goto myNews programacao


Backbone computed properties new ...

This gist shows one way to implement read- and write-enabled computed properties on a Backbone Model (...)

clicks: 16862 16862 2012-05-13 2012-05-13 goto url (new window) https://gist.gi... goto myNews programacao


HTML5 jQuery Paint Plugin new ...

Websanova Paint is a HTML5 canvas based jQuery plugin. It allows you to free paint on a canvas area (...)

clicks: 27754 27754 2012-05-12 2012-05-12 goto url (new window) websanova.com/t... goto myNews programacao


Create Instagram Filters With PHP new ...

In this tutorial, I'll demonstrate how to create vintage (just like Instagram does) photos with PHP (...)

clicks: 16666 16666 2012-05-12 2012-05-12 goto url (new window) net.tutsplus.co... goto myNews programacao


Android Query new ...

Android-Query (AQuery) is a light-weight library for doing asynchronous tasks and manipulating UI el (...)

clicks: 17019 17019 2012-05-12 2012-05-12 goto url (new window) code.google.com... goto myNews programacao


Real-time Applications With Node.js and Socket.IO new ...

Hey everyone! Sorry about the long pause since the last blog post, life has been quite hectic for th (...)

clicks: 17018 17018 2012-05-11 2012-05-11 goto url (new window) codingcookies.c... goto myNews programacao


Sass vs. LESS vs. Stylus: Preprocessor Shootout new ...

CSS3 preprocessors are languages written for the sole purpose of adding cool, inventive features to (...)

clicks: 16343 16343 2012-05-11 2012-05-11 goto url (new window) net.tutsplus.co... goto myNews programacao


15 Handpicked jQuery Drop Down Menus Tutorials new ...

Here we are presenting another brilliant collection of 15 jQuery navigation menu that you can downlo (...)

clicks: 15962 15962 2012-05-10 2012-05-10 goto url (new window) smashingapps.co... goto myNews programacao