COUNT function behavior in SQL - Step-by-Step Execution

Concept Flow - COUNT function behavior

Start Query

↓

Scan Table Rows

↓

Check Each Row for COUNT Criteria

↓

If COUNT(*)

↓

If COUNT(column)

↓

Aggregate Count

↓

Return Result

The COUNT function scans table rows, counts all rows or only those with non-null values depending on usage, then returns the total count.

Execution Sample

SQL

SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees;

Counts total rows and counts rows with non-null salary values in the employees table.

Execution Table

Step	Row Data (id, salary)	COUNT(*) Running Total	COUNT(salary) Running Total	Action
1	(1, 5000)	1	1	Count row for both COUNT(*) and COUNT(salary) because salary is not NULL
2	(2, NULL)	2	1	Count row for COUNT(*) only; salary is NULL so not counted in COUNT(salary)
3	(3, 7000)	3	2	Count row for both COUNT(*) and COUNT(salary)
4	(4, 0)	4	3	Count row for both COUNT(*) and COUNT(salary); zero is counted as value
5	(5, NULL)	5	3	Count row for COUNT(*) only; salary NULL not counted in COUNT(salary)
End	No more rows	5	3	Finished counting all rows

💡 All rows processed; COUNT(*) counts all rows, COUNT(salary) counts only rows with non-null salary

Variable Tracker

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

Key Moments - 2 Insights

Why does COUNT(*) count more rows than COUNT(column) in the example?

COUNT(*) counts every row regardless of NULLs, while COUNT(column) only counts rows where the column value is NOT NULL, as shown in execution_table rows 2 and 5.

Does COUNT(column) count rows where the column value is zero?

Yes, zero is a valid value and not NULL, so COUNT(column) includes it, as seen in execution_table row 4.

Visual Quiz - 3 Questions

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

A 1

B 2

C 3

D 0

💡 Hint

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

At which step does COUNT(*) reach 4?

A After processing row 2

B After processing row 3

C After processing row 4

D After processing row 5

💡 Hint

Look at the COUNT(*) Running Total column in execution_table for each step.

If all salary values were NOT NULL, how would COUNT(salary) compare to COUNT(*) at the end?

A COUNT(salary) would be equal to COUNT(*)

B COUNT(salary) would be zero

C COUNT(salary) would be less than COUNT(*)

D COUNT(salary) would be greater than COUNT(*)

💡 Hint

Refer to the rule that COUNT(column) counts only non-null values; if none are NULL, counts match.

Concept Snapshot

COUNT(*) counts all rows including those with NULLs.
COUNT(column) counts only rows where column is NOT NULL.
NULL values are ignored by COUNT(column).
Zero and empty strings are counted by COUNT(column).
Use COUNT(*) to get total rows regardless of NULLs.

Full Transcript

The COUNT function in SQL counts rows in a table. COUNT(*) counts every row no matter what. COUNT(column) counts only rows where the column has a value that is not NULL. For example, if a table has 5 rows but some rows have NULL in the salary column, COUNT(*) returns 5 but COUNT(salary) returns fewer. Zero values count as valid values for COUNT(column). This visual trace shows step-by-step how the counts increase as each row is checked. It helps understand why COUNT(*) and COUNT(column) can return different results.