Count(*) vs. Count(Column)
On exercise number 40….. newbie question…… the solution used COUNT() , but I use COUNT(book_id) (because i think the question is about the book lol)… is there a rule when to use COUNT() or when to use COUNT(specific column) or they will be always the same?
And what if there is a NULL value in that specific column, so COUNT(*) will always be the better choice?
REPLIES
Hey @Kris 
The short ansewr
COUNT(*) will count all (!!) rows, NULL, non-NULL, everything.
COUNT(column_name) counts only non-NULL rows 
.
The long answer
My favourite tool to explore SQL syntax is using CTE-s (Common Table Expressions):
WITH temp_users AS (
SELECT 'foo@gmail.com' AS email
UNION ALL
SELECT 'bar@gmail.com'
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL
)
SELECT *
FROM temp_users
Here we created a simple temporary table (that’s the CTE) and we can query it to explore SQL syntax, functions, etc.
Here’s a query that compares COUNT(*) vs COUNT(column_name):
WITH temp_users AS (
SELECT 'foo@gmail.com' AS email
UNION ALL
SELECT 'bar@gmail.com'
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL
)
SELECT
COUNT(*) AS all_count,
COUNT(email) AS email_count
FROM temp_users
As you can see from the result, COUNT(email) didn’t count NULL rows.
Hope that helps 