What is a result of the following query for table with 10 rows when there are 2 clients with the name column having a NULL value?

SELECT COUNT(name) FROM clients

Experience Level: Medior
Tags: DatabasesSQL

Answer

Explanation

The result is 8 because if you use COUNT(column), only rows with NOT NULL value in the column are counted.

If you however use COUNT(*), rows with any value (including NULL) in the column are counted.

Example

DECLARE @clients TABLE (id INT, name NVARCHAR(50))

INSERT INTO @clients (id, name) VALUES (1, '1 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (2, '2 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (3, '3 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (4, '4 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (5, '5 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (6, '6 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (7, '7 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (8, '8 Peter Johnson')
INSERT INTO @clients (id, name) VALUES (9, NULL)
INSERT INTO @clients (id, name) VALUES (10, NULL)

SELECT COUNT(name) FROM @clients
-- Returns 8

SELECT COUNT(*) FROM @clients
-- Returns 10
 

Comments

No Comments Yet.
Be the first to tell us what you think.