COUNT(*) vs COUNT(column) difference in SQL - Trade-offs & Expert Analysis

Overview - COUNT(*) vs COUNT(column) difference

What is it?

COUNT(*) and COUNT(column) are two ways to count rows in a database table. COUNT(*) counts all rows, including those with NULL values in any column. COUNT(column) counts only rows where the specified column is not NULL. Both are used to find how many records meet certain conditions.

Why it matters

Knowing the difference helps you get accurate counts in your data. Without this, you might count rows incorrectly, leading to wrong decisions or reports. For example, counting customers with or without missing phone numbers requires different counts. This distinction ensures your data analysis matches reality.

Where it fits

Before this, you should understand basic SQL SELECT queries and what NULL means in databases. After this, you can learn about filtering with WHERE, grouping with GROUP BY, and aggregate functions like SUM and AVG.

Mental Model

Core Idea

COUNT(*) counts every row, while COUNT(column) counts only rows where that column has a value (not NULL).

Think of it like...

Imagine counting apples in a basket: COUNT(*) is like counting every apple regardless of condition, while COUNT(column) is like counting only apples that are not rotten.

┌───────────────┐
│   Table Rows  │
├───────────────┤
│ Row 1: col=5  │
│ Row 2: col=NULL│
│ Row 3: col=10 │
└───────────────┘

COUNT(*) = 3 (all rows)
COUNT(col) = 2 (only rows with col not NULL)

Build-Up - 6 Steps

1

Foundation

Understanding COUNT(*) Basics

Concept: COUNT(*) counts all rows in a table or result set, including those with NULLs.

When you write SELECT COUNT(*) FROM table;, the database counts every row returned, no matter what values the columns have. NULL values do not affect this count because it counts rows, not column values.

Result

The output is the total number of rows in the table or query result.

Understanding that COUNT(*) counts rows regardless of content helps you get total row counts quickly and reliably.

2

Foundation

Understanding COUNT(column) Basics

Concept: COUNT(column) counts only rows where the specified column is NOT NULL.

When you write SELECT COUNT(column) FROM table;, the database counts only rows where 'column' has a value. Rows where 'column' is NULL are ignored in this count.

Result

The output is the number of rows with a non-NULL value in that column.

Knowing COUNT(column) skips NULLs helps you count meaningful data entries in a specific column.

3

Intermediate

Effect of NULL Values on Counts

🤔Before reading on: Do you think COUNT(column) includes rows where the column is NULL? Commit to yes or no.

Concept: NULL values are ignored by COUNT(column) but included by COUNT(*).

If a column has NULLs, COUNT(*) still counts those rows, but COUNT(column) does not. This difference can cause counts to differ when NULLs exist.

Result

COUNT(*) >= COUNT(column) always; they are equal only if the column has no NULLs.

Understanding how NULLs affect counts prevents mistakes in data analysis and reporting.

4

Intermediate

Using COUNT with WHERE Clauses

🤔Before reading on: Will COUNT(*) and COUNT(column) behave the same if a WHERE clause filters out NULLs? Commit to yes or no.

Concept: Filtering rows with WHERE can change how COUNT(*) and COUNT(column) behave.

If you filter rows to exclude NULLs in the column, then COUNT(*) and COUNT(column) will return the same number because all counted rows have non-NULL values.

Result

Filtered counts can match if NULLs are removed before counting.

Knowing how WHERE affects counts helps you control which rows are counted and avoid surprises.

5

Advanced

COUNT(column) with Multiple Columns and Expressions

🤔Before reading on: Does COUNT(column1, column2) count rows where either column is NULL? Commit to yes or no.

Concept: COUNT only accepts one column or expression; counting multiple columns requires other methods.

COUNT(column1, column2) is invalid SQL. To count rows where multiple columns are not NULL, you use conditions like COUNT(*) with WHERE or COUNT with CASE expressions.

Result

You must write queries carefully to count rows based on multiple columns' NULL status.

Understanding COUNT's syntax limits helps you write correct queries for complex counting needs.

6

Expert

Performance Differences Between COUNT(*) and COUNT(column)

🤔Before reading on: Do you think COUNT(*) is always slower than COUNT(column)? Commit to yes or no.

Concept: COUNT(*) can be optimized by databases differently than COUNT(column), depending on indexes and storage.

Some databases optimize COUNT(*) by using metadata or indexes without scanning all rows. COUNT(column) may require scanning the column data to check for NULLs, which can be slower.

Result

COUNT(*) is often faster, but exact performance depends on database engine and schema.

Knowing performance differences guides you to write efficient queries in large databases.

Under the Hood

COUNT(*) counts rows by scanning the table or index entries without checking column values. COUNT(column) scans the specified column's data to check for NULLs and counts only non-NULL entries. Internally, NULL is a special marker meaning 'no value', so COUNT(column) excludes these during counting.

Why designed this way?

SQL was designed to distinguish between counting all rows and counting meaningful data entries. COUNT(*) provides total row counts quickly, while COUNT(column) allows ignoring missing data. This design supports flexible data analysis and accurate reporting.

┌───────────────┐       ┌───────────────┐
│   Table Rows  │       │  Column Data  │
├───────────────┤       ├───────────────┤
│ Row 1: col=5  │       │ 5             │
│ Row 2: col=NULL│─────▶│ NULL (ignored)│
│ Row 3: col=10 │       │ 10            │
└───────────────┘       └───────────────┘

COUNT(*) counts all rows → 3
COUNT(column) counts non-NULL → 2

Myth Busters - 3 Common Misconceptions

Quick: Does COUNT(column) count rows where the column is NULL? Commit to yes or no.

Common Belief: COUNT(column) counts all rows, just like COUNT(*).

Reality: COUNT(column) counts only rows where the column is NOT NULL; it skips NULLs.

Why it matters: Misunderstanding this leads to undercounting or overcounting data, causing wrong conclusions.

Quick: Is COUNT(*) slower than COUNT(column) because it counts more? Commit to yes or no.

Common Belief: COUNT(*) is always slower because it counts every row.

Reality: COUNT(*) can be faster because databases optimize it using metadata or indexes, while COUNT(column) may scan data to check NULLs.

Why it matters: Assuming COUNT(*) is slower can lead to inefficient query design and missed optimization opportunities.

Quick: Does COUNT(column1, column2) count rows where both columns are non-NULL? Commit to yes or no.

Common Belief: You can use COUNT with multiple columns to count rows with non-NULL values in all those columns.

Reality: COUNT only accepts one column or expression; counting multiple columns requires other SQL constructs like CASE or WHERE.

Why it matters: Trying to use COUNT with multiple columns causes syntax errors or wrong counts, confusing beginners.

Expert Zone

1

COUNT(*) can use index-only scans in some databases, returning counts without accessing full row data.

2

COUNT(column) behavior changes if the column is an expression or function, counting non-NULL results of that expression.

3

In some SQL dialects, COUNT(1) behaves like COUNT(*), but this is a convention, not a standard guarantee.

When NOT to use

Avoid COUNT(column) when you want total row counts regardless of NULLs; use COUNT(*) instead. For counting distinct values, use COUNT(DISTINCT column). When performance is critical, test which COUNT variant is faster on your database.

Production Patterns

In real systems, COUNT(*) is used for total record counts like total users. COUNT(column) is used to count valid entries, like number of users with email addresses. Combining COUNT with WHERE filters and GROUP BY clauses helps generate detailed reports.

Connections

NULL Handling in SQL

COUNT(column) behavior depends on how NULL values are treated in SQL.

Understanding NULLs is essential to correctly interpret COUNT(column) results and avoid counting missing data.

Indexing in Databases

COUNT(*) performance can be improved by indexes that allow counting rows without full scans.

Knowing how indexes work helps optimize COUNT queries for large datasets.

Set Theory in Mathematics

COUNT(*) corresponds to counting all elements in a set, while COUNT(column) counts elements with a property (non-NULL).

This connection clarifies why COUNT(column) excludes NULLs, similar to filtering elements in a set.

Common Pitfalls

#1 Counting rows with COUNT(column) expecting total rows including NULLs.

Wrong approach:

SELECT COUNT(phone_number) FROM customers;

Correct approach:

SELECT COUNT(*) FROM customers;

Root cause:

Misunderstanding that COUNT(column) excludes NULL values, leading to undercounting.

#2 Using COUNT with multiple columns directly.

Wrong approach:

SELECT COUNT(first_name, last_name) FROM employees;

Correct approach:

SELECT COUNT(*) FROM employees WHERE first_name IS NOT NULL AND last_name IS NOT NULL;

Root cause:

Incorrect syntax and misunderstanding of COUNT's single-argument requirement.

#3 Assuming COUNT(*) is always slower than COUNT(column).

Wrong approach:

Avoid using COUNT(*) for performance reasons without testing.

Correct approach:

Test both COUNT(*) and COUNT(column) on your database to choose the faster option.

Root cause:

Lack of knowledge about database optimizations for COUNT(*) queries.

Key Takeaways

COUNT(*) counts every row in the result, including those with NULL values in any column.

COUNT(column) counts only rows where the specified column is not NULL, ignoring NULLs.

NULL values affect COUNT(column) but not COUNT(*), so their results can differ.

Performance of COUNT(*) and COUNT(column) depends on database optimizations and indexing.

Understanding these differences helps write accurate and efficient SQL queries.

Practice

1. What is the main difference between COUNT(*) and COUNT(column_name) in SQL?

easy

A. COUNT(*) counts only rows where all columns are NOT NULL, COUNT(column_name) counts all rows.

B. COUNT(*) and COUNT(column_name) always return the same result.

C. COUNT(*) counts only NULL values, COUNT(column_name) counts non-NULL values.

D. COUNT(*) counts all rows, while COUNT(column_name) counts only rows where the column is NOT NULL.

Solution

1. Step 1: Understand COUNT(*)

   COUNT(*) counts every row in the table, including those with NULL values in any column.

2. Step 2: Understand COUNT(column_name)

   COUNT(column_name) counts only rows where the specified column is NOT NULL, ignoring rows where that column is NULL.

3. Final Answer:

   COUNT(*) counts all rows; COUNT(column_name) counts only non-NULL values in that column. -> Option D

4. Quick Check:

   COUNT(*) counts all rows, COUNT(column) skips NULLs [OK]

Hint: Remember: * counts all rows, column counts non-NULL only [OK]

Common Mistakes:

• Thinking COUNT(column) counts NULL values
• Assuming COUNT(*) ignores NULLs
• Believing both always return same count

2. Which of the following SQL queries correctly counts the number of rows where the column email is NOT NULL?

easy

A. SELECT COUNT(email) FROM users;

B. SELECT COUNT(*) FROM users WHERE email IS NULL;

C. SELECT COUNT(email) FROM users WHERE email IS NULL;

D. SELECT COUNT(*) FROM users;

Solution

1. Step 1: Analyze COUNT(email)

   COUNT(email) counts only rows where email is NOT NULL, so it already filters NULLs.

2. Step 2: Check the WHERE clause necessity

   Adding WHERE email IS NOT NULL is redundant with COUNT(email), so SELECT COUNT(email) FROM users; is correct and simpler.

3. Final Answer:

   SELECT COUNT(email) FROM users; -> Option A

4. Quick Check:

   COUNT(column) counts non-NULL rows without WHERE [OK]

Hint: COUNT(column) counts non-NULL rows without extra WHERE [OK]

Common Mistakes:

• Adding unnecessary WHERE clause with COUNT(column)
• Using COUNT(*) with wrong WHERE condition
• Confusing NULL and NOT NULL filters

3. Given the table orders with 5 rows where the discount column has values: 10, NULL, 5, NULL, 0, what will be the result of SELECT COUNT(*) AS total, COUNT(discount) AS discount_count FROM orders;?

medium

A. total = 5, discount_count = 3

B. total = 5, discount_count = 5

C. total = 3, discount_count = 3

D. total = 3, discount_count = 5

Solution

1. Step 1: Count total rows with COUNT(*)

   COUNT(*) counts all 5 rows regardless of NULLs.

2. Step 2: Count non-NULL discount values with COUNT(discount)

   Only 3 rows have non-NULL discount values (10, 5, 0), so discount_count is 3.

3. Final Answer:

   total = 5, discount_count = 3 -> Option A

4. Quick Check:

   COUNT(*) = all rows, COUNT(column) = non-NULL rows [OK]

Hint: COUNT(*) counts all rows; COUNT(column) skips NULLs [OK]

Common Mistakes:

• Counting NULLs in COUNT(column)
• Confusing total rows with non-NULL counts
• Assuming 0 is NULL

4. You wrote this query: SELECT COUNT(column_name) FROM table_name; but it returns 0. The column has some NULL and some non-NULL values. What is the most likely problem?

medium

A. COUNT(column_name) counts NULL values, so it should not return 0.

B. The column_name is misspelled or does not exist in the table.

C. COUNT(*) should be used instead of COUNT(column_name) to count non-NULL values.

D. The table is empty, so COUNT(column_name) returns 0.

Solution

1. Step 1: Check column existence

   If COUNT(column_name) returns 0 but column has non-NULL values, likely the column name is wrong or missing.

2. Step 2: Understand COUNT behavior

   COUNT(column_name) counts non-NULL values; if column exists and has non-NULLs, result won't be zero.

3. Final Answer:

   Column name is misspelled or does not exist. -> Option B

4. Quick Check:

   Wrong column name causes zero count [OK]

Hint: Check column spelling if COUNT(column) returns zero unexpectedly [OK]

Common Mistakes:

• Assuming COUNT(column) counts NULLs
• Using COUNT(*) when column is misspelled
• Ignoring empty table possibility without checking

5. You have a table employees with 100 rows. The phone_number column has 80 non-NULL values and 20 NULLs. You want to find how many employees have a phone number and how many total employees there are. Which query gives both counts correctly?

hard

A. SELECT COUNT(phone_number) + COUNT(*) AS total FROM employees;

B. SELECT COUNT(*) AS with_phone, COUNT(phone_number) AS total_employees FROM employees;

C. SELECT COUNT(phone_number) AS with_phone, COUNT(*) AS total_employees FROM employees;

D. SELECT COUNT(phone_number) AS total_employees, COUNT(*) AS with_phone FROM employees;

Solution

1. Step 1: Count employees with phone numbers

   COUNT(phone_number) counts only non-NULL phone numbers, so it returns 80.

2. Step 2: Count total employees

   COUNT(*) counts all rows, so it returns 100.

3. Final Answer:

   SELECT COUNT(phone_number) AS with_phone, COUNT(*) AS total_employees FROM employees; -> Option C

4. Quick Check:

   COUNT(phone_number) = 80, COUNT(*) = 100 [OK]

Hint: Use COUNT(column) for non-NULL, COUNT(*) for total rows [OK]

Common Mistakes:

• Swapping counts in SELECT clause
• Adding counts instead of separate columns
• Confusing which count counts NULLs