Leisure time out of most hectic QA release day, playing with Count and Null

It became a leisure time out of most hectic QA release day, when my  friend ask me to design 10 typical query, don’t know why this SQL Server is so interesting it fuels up energy to even a non living atmosphere. Here is my first query goes.

What will be the output of the third query if following first two query produce the below result

select count(*) as TotalCount from dbo.MyTable

TotalCount

-----------

9

select count(*) as WhereCount from dbo.MyTable where MyID = 4

WhereCount

-----------

3

select count(*) as MyCount from dbo.MyTable where MyID <> 4

WhereCount

-----------

??

Before come to any conclusion let’s stimulate the same problem

CREATE TABLE dbo.MyTable

    (

      MyID INT

    , MyChar VARCHAR(10)

    );

   GO

Command(s) completed successfully.

Our table is ready, let’s insert some data and prepare our environment for testing.

INSERT INTO dbo.MyTable

        ( MyID, MyChar )

    VALUES

        ( 1, 'A' ),

        ( 2, 'B' ),

        ( NULL, 'C' ),

        ( 4, 'D' ),

        ( NULL, 'E' ),

        ( 6, 'F' ),

        ( 6, 'G' ),

        ( 4, 'H' ),

        ( 4, 'I' ); 

   GO

Let’s find out the total count of rows in the table

select count(*) from dbo.MyTable

TotalCount

-----------

9

So its 9 in count, lets try to find out now of rows where “MyID” field has value equal to 4

select count(*) as WhereCount from dbo.MyTable where MyID = 4

MyCount

-----------

3

So here it comes out as 3, lets now come to our asked query output

select count(*) as MyCount from dbo.MyTable where MyID <> 4

Here we saw from previous two output that

Total row count = 9

Total row with MyId value equal to 4 is = 3

So if we are asked

Total row with value not equal to 4, in a simple mathematic term we can reply,

Total row with value not equal to 4=(Total row count) – (Total row with MyId value equal to 4)

Total row with value not equal to 4 = 9-3 = 6

Now let’s run the query to find the real answer for the following query

select count(*) as MyCount from dbo.MyTable where MyID <> 4

MyCount

-----------

4

The output is not footing to our thought, lets find out where we went wrong

Lets first query the table data

MyID        MyChar

----------- ----------

1           A

2           B

NULL        C

4           D

NULL        E

6           F

6           G

4           H

4           I

(9 row(s) affected)

From the output we can see total counts of rows are 9 in number and number of rows with MyID equal four are 3. Now if we check number of rows with values not equal to 4:

Considering Null is 6

Not considering Null is 4

Now if we recall our query the count came out as 4

select count(*) as MyCount from dbo.MyTable where MyID <> 4

MyCount

-----------

4

This means MyID <> 4 skip Null values while evaluating count(*) function.

Conclusion: While calculating count, SQL Server skip NULL values.

 

A cup of tea with SQL Server is the greatest combination :)

 

Post Reference: Vikram Aristocratic Elfin Share