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.