COUNT(*) vs COUNT(column) difference in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding COUNT(*) vs COUNT(column)

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

A COUNT(*) counts all rows including those with NULLs in any column, while COUNT(column_name) counts only rows where the specified column is NOT NULL.

B COUNT(*) and COUNT(column_name) always return the same result regardless of NULL values.

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

D COUNT(*) counts only rows where all columns are NOT NULL, while COUNT(column_name) counts all rows regardless of NULLs.

💡 Hint

Think about how NULL values affect counting in SQL.

COUNT(*) counts every row in the table, including those with NULL values in any column. COUNT(column_name) counts only rows where the specified column has a non-NULL value.

❓ query_result

intermediate

Result of COUNT(*) vs COUNT(column) with NULLs

Given the table Employees with 5 rows where the ManagerID column has 2 NULL values, what will the following query return?

SELECT COUNT(*) AS total_rows, COUNT(ManagerID) AS non_null_managers FROM Employees;

A total_rows = 5, non_null_managers = 3

B total_rows = 3, non_null_managers = 5

C total_rows = 5, non_null_managers = 5

D total_rows = 3, non_null_managers = 3

💡 Hint

COUNT(*) counts all rows; COUNT(column) skips NULLs.

There are 5 rows total, so COUNT(*) returns 5. Since 2 ManagerID values are NULL, COUNT(ManagerID) returns 3.

📝 Syntax

advanced

Identify the syntax error in COUNT usage

Which of the following SQL queries will cause a syntax error?

A SELECT COUNT(OrderID) FROM Orders;

B SELECT COUNT(OrderID, CustomerID) FROM Orders;

C SELECT COUNT(*) FROM Orders;

D SELECT COUNT(DISTINCT CustomerID) FROM Orders;

💡 Hint

COUNT accepts only one argument or *.

COUNT only accepts a single column or *. Using two columns inside COUNT causes a syntax error.

❓ optimization

advanced

Optimizing COUNT queries with NULL columns

You want to count all rows in a large table Sales. Which query is generally faster and why? Options: A) SELECT COUNT(*) FROM Sales; B) SELECT COUNT(SaleID) FROM Sales; -- SaleID is NOT NULL C) SELECT COUNT(Discount) FROM Sales; -- Discount can be NULL D) SELECT COUNT(DISTINCT CustomerID) FROM Sales;

A SELECT COUNT(Discount) FROM Sales; because it skips NULLs and is faster.

B SELECT COUNT(SaleID) FROM Sales; because SaleID is NOT NULL and can be optimized.

C SELECT COUNT(*) FROM Sales; because it counts all rows without checking column values.

D SELECT COUNT(DISTINCT CustomerID) FROM Sales; because distinct counts are faster.

💡 Hint

COUNT(*) is optimized by most databases to count rows quickly.

COUNT(*) is usually the fastest way to count rows because it does not need to check any column values. Counting a specific column requires checking for NULLs, which can be slower.

🔧 Debug

expert

Why does COUNT(column) return zero unexpectedly?

You run this query:

SELECT COUNT(Email) FROM Users;

But it returns 0, even though the table has 10 rows. What is the most likely reason?

A The table Users is empty.

B COUNT(Email) counts only rows where Email is an empty string.

C COUNT(Email) counts only distinct emails, and there are none.

D All values in the Email column are NULL.

💡 Hint

Remember COUNT(column) ignores NULL values.

COUNT(column) counts only rows where the column is NOT NULL. If all Email values are NULL, the count is zero even if rows exist.

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