Data · SQL

Difference between COUNT(*) and COUNT(column) in SQL

Published

A table with 4 rows: what does COUNT(*) return?

From COUNT(*) to a count after a LEFT JOIN, in five steps:

  1. Tell rows apart from non-NULL values
  2. Count a $0 discount as a value
  3. Keep each value once with DISTINCT
  4. Count a customer’s orders after a LEFT JOIN
  5. Practice

Why does COUNT(column) return less than COUNT(*)?

COUNT(*) counts rows; COUNT(phone) skips NULL cells. On 12 rows with 3 NULL phones, the first returns 12 and the second returns 9.

Let’s take five customers, two of them with no phone.

SELECT COUNT(*),
       COUNT(phone)
FROM customers
customers
idnamephone
1Emma(312) 555-0148
2Noah(415) 555-0193
3MiaNULL
4Liam(617) 555-0162
5AvaNULL
result
COUNT(*)COUNT(phone)
53
  1. COUNT(*) counts the rows of the table, whatever they hold.

  2. Which rows does COUNT(phone) skip? Tap them in the table.

    Go down the phone column and look for NULL.

  3. Count the rows that are left, the ones where phone is not NULL.

SELECT COUNT(phone),
       COUNT(*) - COUNT(phone)
FROM customers
customers
idnamephone
1Emma(312) 555-0148
2NoahNULL
3Mia(206) 555-0127
4Liam(617) 555-0162
5Ava(512) 555-0185
6EthanNULL

Does COUNT(column) count zero values?

Only NULL is skipped: a $0 discount is a value. Out of 10 orders, with 3 NULL discounts and 4 discounts of $0, COUNT(discount) returns 7.

Let’s take five orders and their discounts.

SELECT COUNT(discount)
FROM orders
orders
iddiscount
101$5
102$0
103NULL
104$10
105$0
result
COUNT(discount)
4
  1. Order 102 has a $0 discount. Does COUNT(discount) count this row?

  2. Go down the discount column and skip only the NULL cells.

SELECT COUNT(discount)
FROM orders
orders
iddiscount
101$10
102$0
103NULL
104$0
105$5

What does COUNT(DISTINCT column) count?

COUNT(DISTINCT city) counts each city only once and skips NULL. A column that holds Austin 6 times and Denver 3 times returns 2.

Let’s take five customers and their cities.

SELECT COUNT(DISTINCT city)
FROM customers
customers
idnamecity
1EmmaAustin
2NoahDenver
3MiaAustin
4LiamNULL
5AvaBoston
result
COUNT(DISTINCT city)
3
  1. Austin appears twice. How many times does COUNT(DISTINCT city) count it?

  2. Liam’s city is NULL. Does COUNT(DISTINCT city) count it as a city?

  3. Keep each city once, leave out NULL, then count.

SELECT COUNT(city),
       COUNT(DISTINCT city)
FROM customers
customers
idnamecity
1EmmaDenver
2NoahBoston
3MiaDenver
4LiamDenver
5AvaNULL
6EthanMiami

Why does COUNT(*) return 1 for a customer with no orders?

After a LEFT JOIN, a customer with no orders, say the one whose id is 7, keeps one row where orders.id is NULL. For that customer, COUNT(*) returns 1 and COUNT(orders.id) returns 0.

Let’s take three customers, one of them with no orders.

SELECT customers.name,
  COUNT(*) AS num_rows,
  COUNT(orders.id) AS num_orders
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
GROUP BY customers.name
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_id
1011
1021
1033
join result
customers.nameorders.id
Emma101
Emma102
NoahNULL
Mia103
result
namenum_rowsnum_orders
Emma22
Noah10
Mia11
  1. GROUP BY customers.name makes one count per customer. COUNT(*) counts Noah’s rows in the join result, whatever they hold.

  2. Since Noah has no order, the LEFT JOIN put NULL in orders.id. COUNT(orders.id) skips NULL cells.

  3. Which other customer has the same num_rows as Noah?

SELECT customers.name,
  COUNT(*) AS num_rows,
  COUNT(orders.id) AS num_orders
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
GROUP BY customers.name
customers
idname
1Emma
2Noah
3Mia
4Liam
orders
idcustomer_id
1011
1023
1033

How do you check a SQL COUNT quickly?

It goes faster if you first read what is inside the parentheses: * counts rows, a column counts its cells that are not NULL, and DISTINCT keeps each value once.

  1. SELECT COUNT(discount)
    FROM orders
    orders
    iddiscount
    101$0
    102NULL
    103$20
    104$0
    105NULL
    106$5