COUNT(*) vs COUNT(column) difference in SQL - Visual Side-by-Side Comparison

Concept Flow - COUNT(*) vs COUNT(column) difference

Start Query

↓

COUNT(*) counts all rows

↓

COUNT(column) counts non-NULL values in that column

↓

Return count result

↓

End Query

The query starts, then COUNT(*) counts every row regardless of NULLs, while COUNT(column) counts only rows where the column is not NULL, then returns the count.

Execution Sample

SQL

SELECT COUNT(*) AS total_rows, COUNT(age) AS age_count FROM people;

Counts total rows and counts only rows where 'age' is not NULL in the 'people' table.

Execution Table

Step	Row ID	age Value	COUNT(*) Running Total	COUNT(age) Running Total
1	1	25	1	1
2	2	NULL	2	1
3	3	30	3	2
4	4	NULL	4	2
5	5	22	5	3
Exit	-	-	-	All rows processed

💡 All 5 rows processed; COUNT(*) counts all rows, COUNT(age) counts only non-NULL ages

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
COUNT(*)	0	1	2	3	4	5	5
COUNT(age)	0	1	1	2	2	3	3

Key Moments - 2 Insights

Why does COUNT(*) count more rows than COUNT(age)?

COUNT(*) counts every row no matter what, but COUNT(age) only counts rows where 'age' is not NULL, as shown in execution_table rows 2 and 4 where age is NULL and COUNT(age) does not increase.

Does COUNT(column) count rows with NULL values in that column?

No, COUNT(column) skips NULL values. In the execution_table, rows with NULL age do not increase COUNT(age), unlike COUNT(*) which counts all rows.

Visual Quiz - 3 Questions

Look at the execution_table, what is COUNT(age) after processing row 3?

A 1

B 2

C 3

D 0

💡 Hint

Check the 'COUNT(age) Running Total' column at Step 3 in the execution_table.

At which step does COUNT(*) first differ from COUNT(age)?

A Step 3

B Step 1

C Step 2

D Step 5

💡 Hint

Look at the running totals for COUNT(*) and COUNT(age) in execution_table rows 1 and 2.

If all 'age' values were NOT NULL, how would COUNT(*) and COUNT(age) compare?

A Both would be equal

B COUNT(age) would be greater

C COUNT(*) would be greater

D Cannot determine

💡 Hint

Consider how COUNT(age) counts only non-NULL values and COUNT(*) counts all rows.

Concept Snapshot

COUNT(*) counts all rows in a table, including those with NULLs.
COUNT(column) counts only rows where the specified column is NOT NULL.
Use COUNT(*) to get total rows.
Use COUNT(column) to count non-NULL entries in that column.
They differ when the column has NULL values.
Both return a single number as result.

Full Transcript

This visual execution shows how COUNT(*) and COUNT(column) work differently in SQL. COUNT(*) counts every row regardless of NULLs, while COUNT(column) counts only rows where the column has a value (not NULL). The example table has 5 rows with some NULL ages. The execution table traces counting step-by-step, showing COUNT(*) increases every row, but COUNT(age) only increases when age is not NULL. This helps beginners see why counts differ and when to use each function.