Microsoft – SQL Server – Counting “Null”

Issued a quick Query and it looked right:

         select
                    tblData.stateID
                  , count(tblData.stateID)

         from dbo.rawData tblData

                left outer join dbo.lookupState tblLookUpState

                   on tblData.stateID = tblLookupState.stateID

         group by

                     tblData.stateID
                   , tblLookUpState.Name

Everything looked right. But, I knew that in a few records we have some Null data
in the stateID column.

The only way to fix is to:

         select
                    tblData.stateID
                  , count(tblData.stateID) as incorrectCount
                  , count(isNull(tblData.stateID), 0) as goodCount

         from dbo.rawData tblData

                left outer join dbo.lookupState tblLookUpState

                   on tblData.stateID = tblLookupState.stateID

         group by

                     tblData.stateID
                   , tblLookUpState.Name

The basic problem is that unless forced to, MS SQL Server has problems knowing what
to do with nulls.

As everyone knows, this problem manifests when finding matches, such as:

wrong:

stateID = null

good:

stateID is null

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s