What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

The difference is how each one treats NULL and duplicates.

• COUNT(*) counts rows, whatever their values.
• COUNT(column) counts rows where the column is not NULL.
• COUNT(DISTINCT column) counts the distinct non-NULL values.

-- table t(x): 10, 10, NULL
SELECT COUNT(*)          AS all_rows,   -- 3
       COUNT(x)          AS non_null,   -- 2
       COUNT(DISTINCT x) AS distinct_x  -- 1
FROM t;

COUNT(1) and COUNT(*) give the same result, and modern optimizers run them identically, so choose COUNT(*) for clarity.

COUNT(DISTINCT ...) is more expensive, because it must deduplicate with a sort or a hash. On large tables it can be a slow part of a query, and approximate counting functions are sometimes used for dashboards.

After a LEFT JOIN, count a column from the joined table, as COUNT(*) would count the NULL-filled row as 1.

Choose the variant by deciding whether NULL and duplicates should count.