Great question—let’s break this down step by step to clear up your confusion about how the COUNT() function works in SQL.

1. How COUNT(*) Differs from COUNT(column) #

The core difference boils down to what each variant is designed to count:

• COUNT(*): This is a special case defined in the SQL standard to count every row in the table, regardless of whether columns contain NULL values. It doesn’t check individual column values at all—it simply counts the existence of the row itself. Even if a row has NULL in every single column, it’s still a valid row in the table, so COUNT(*) will include it.
• COUNT(column): When you pass a specific column name, COUNT() only counts rows where that column has a non-NULL value. NULL means "no value exists here," so those rows get excluded from the count.

For example, if you have a table with 5 rows, and 2 of them have NULL in the email column:

• COUNT(*) returns 5 (all rows)
• COUNT(email) returns 3 (only rows with a non-NULL email)

2. Why COUNT(DISTINCT *) Doesn’t Work #

The short answer: it’s invalid SQL syntax. Here’s why:
COUNT(DISTINCT) requires you to specify one or more columns (or expressions) to use for deduplication. The * wildcard works in SELECT * to mean "all columns," but it can’t be used directly after DISTINCT in a COUNT() call—databases don’t know how to interpret "distinct all columns" as a single parameter.

If your goal is to count the number of unique rows based on all columns, you have two valid options:

1. List all columns explicitly (works in most databases):

   SELECT COUNT(DISTINCT col1, col2, col3) FROM your_table;
   

2. Use a subquery to first get distinct rows, then count them (compatible with all databases):

   SELECT COUNT(*) FROM (
       SELECT DISTINCT * FROM your_table
   ) AS distinct_rows;
   

When you run SELECT COUNT(DISTINCT *) FROM table;, the database throws a syntax error (or returns no results) because it can’t parse the invalid parameter combination.

3. Quick Recap #

To sum up the key behaviors:

• COUNT(*): Counts every row in the table, including rows with all NULL columns.
• COUNT(column): Counts only rows where the specified column has a non-NULL value.
• COUNT(DISTINCT column): Counts the number of unique non-NULL values in the specified column.
• COUNT(DISTINCT *): Invalid syntax—you must specify the exact columns to use for deduplication.

内容的提问来源于stack exchange，提问作者coder19