SQL SERVER – Count NULL Values From Column

The other day, I have received a very simple but interesting question by one of my earlier clients of Comprehensive Database Performance Health Check via email. The question was about how to count NULL values from the column.

SQL SERVER - Count NULL Values From Column countnull-800x457

Honestly, this is a very simple thing to do but I can totally understand why my client was confused and here is a very simple script to demonstrate the issue of counting NULL values from Column.

First, let us create a table and populate it with a few NULL Values.

-- Create a Table
CREATE TABLE Table1 (Col1 INT);
-- Insert Data
INSERT INTO Table1 VALUES (1), (2);
INSERT INTO Table1 VALUES (NULL),(NULL),(NULL);

In the above script, we have inserted 3 NULL values.

Now run the following command to count all the NULL values from the table.

SELECT COUNT(Col1,0) CountCol
FROM Table1
WHERE Col1 IS NULL;

When you see the result of the query, you will notice that even though we have 3 NULL values the query says there are no NULL values. This is because all the aggregate values ignore the NULL values. If you want to count the NULL values, you will have to first convert the NULL values to different values and then apply the aggregate function as demonstrated in the following script.

SELECT COUNT(ISNULL(Col1,0)) CountCol
FROM Table1
WHERE Col1 IS NULL;

When you run the above script you will notice that the query is now demonstrating the correct value of NULL values. The reason for ignoring the NULL value is very simple; as this prevents unknown or inapplicable values from affecting the result of the aggregate.

SQL SERVER - Count NULL Values From Column countNULL1

However, if you have kept your ANSI Warnings on, you can find the warning in the SSMS warning message. You can read more about the ANSI Warnings over here, here and here.

Please leave a comment and let me know if you use any such tricks when you have to work with the NULL values. I personally, do not like the NULL values in my tables unless they are absolutely required. I have seen NULL creating enough confusion for the DBAs and Query Engine. However, this is the topic for some other blog posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Truncating Data and ANSI_WARNINGS
Next Post
SQL SERVER Management Studio – Rebuild All Indexes on Table

Related Posts

3 Comments. Leave new

  • Hi Pinal,

    Below code will also returns count of NULL values in a table.

    SELECT COUNT(*) CountCol
    FROM Table1
    WHERE Col1 IS NULL

    BR
    Narendra

    Reply
  • Why * ?, instead put 1 so that executes faster. Also, if all columns has NULL value then count will be wrong.

    SELECT COUNT(1) CountCol
    FROM Table1
    WHERE Col1 IS NULL

    Reply
  • Jose María Laguna
    March 5, 2020 5:42 pm

    Another way:

    — count (*) count all rows
    — count (colum) count all not null rows

    select count (*) – count(column) from table
    where …

    Reply

Leave a Reply

Menu