SQL Server NULLABLE column vs SQL COUNT() function

Question

Could someone help me understand something? When I can, I usually avoid (*) in an SQL statement. Well, today was payback. Here is a scenario:

CREATE TABLE Tbl (Id INT IDENTITY(1, 1) PRIMARY KEY, Name NVARCHAR(16))

INSERT INTO Tbl VALUES (N'John')
INSERT INTO Tbl VALUES (N'Brett')
INSERT INTO Tbl VALUES (NULL)

I could count the number of records where Name is NULL as follows:

SELECT COUNT(*) FROM Tbl WHERE Name IS NULL

While avoiding the (*), I discovered that the following two statements give me two different results:

SELECT COUNT(Id) FROM Tbl WHERE Name IS NULL
SELECT COUNT(Name) FROM Tbl WHERE Name IS NULL

The first statement correctly return 1 while the second statement yields 0. Why or How?

You should avoid `*` in `select *`, but you should not avoid `*` in `count(*)`. — Guffa, Nov 16 '11 at 07:54

score 5 · Accepted Answer · answered Nov 16 '11 at 07:44

5

That's because

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column

so when you count Id you get expected result, while counting Name no, but the answer provided by query is correct

answered Nov 16 '11 at 07:44

Marco

56,740
14
129
152

Gosh! I'll be damned! Its indeed true. `SELECT COUNT(Name) FROM Tbl` only returns the number of columns not having `NULL`. Damnit! This means its just safer to use `(*)` in a `COUNT` function to evaluate records in a table - unless one knows that a field is not and will never be nullable – John Gathogo Nov 16 '11 at 07:50
usually you'll get a warning from SQL SERVER `Null value is eliminated by an aggregate or other SET operation.` since that issue often causes confusion. – Code Magician Nov 16 '11 at 08:03

score 1 · Answer 2 · answered Nov 16 '11 at 07:49

Everything is described in COUNT (Transact-SQL).

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

ALL - is default

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

score 1 · Answer 3 · answered Nov 16 '11 at 07:54

"COUNT()" does not count NULL values. So basically:

SELECT COUNT(Id) FROM Tbl WHERE Name IS NULL

will return the number of lines where ("ID" IS NOT NULL) AND ("Name" IS NULL); result is "1"

While:

SELECT COUNT(Name) FROM Tbl WHERE Name IS NULL

will count the lines where ("Name" IS NOT NULL) AND ("Name" IS NULL); result will always be 0

score 0 · Answer 4 · answered Nov 16 '11 at 07:50

0

As it was said, COUNT (column_name) doesn't count NULL values. If you don't want use COUNT(*) then use COUNT(1), but actualy you will not see difference in performance.

answered Nov 16 '11 at 07:50

Igor Borisenko

3,806
3
34
49

score 0 · Answer 5 · answered Nov 16 '11 at 07:52

0

"Always avoid using *" is one of those blanket statements that people blindly follow. If you knew the reasons why you were avoiding * then you would know that none of those reasons apply when doing count(*).

answered Nov 16 '11 at 07:52

Brandon Moore

8,590
15
65
120

score 0 · Answer 6 · answered Nov 16 '11 at 08:33

The * in COUNT(*) is not the same * in SELECT * FROM...

SELECT COUNT(*) FROM T; very specifically means the cardinality of the table expression T.

SELECT COUNT(1) FROM T; will generate the same results as COUNT(*) but if the contents of the parentheses is not * then it must be parsed.

SELECT COUNT(c) FROM T; where c is a nullable column in table T will count the non-null values.

P.S. I'm comfortable with using SELECT * FROM... in the right circumstances.

P.P.S. Your 'table' has no key: consider INSERT INTO Tbl VALUES ('John', 'John', 'John', NULL, NULL, NULL); would be allowed by the results would be nonsense.

SQL Server NULLABLE column vs SQL COUNT() function

6 Answers6