COUNT(column) vs COUNT(*) in SQL — With INNER, LEFT, RIGHT & FULL JOIN Explained

Source: Dev.to

COUNT(*) vs COUNT(column) Overview

• COUNT(*) – counts every row returned by the query.
• COUNT(column) – counts only the non‑NULL values in the specified column.

Understanding this distinction is crucial, especially when joins are involved.

INNER JOIN Example

Query

SELECT c.id,
       COUNT(*)        AS total_rows,
       COUNT(o.id)     AS orders_count
FROM   customers c
INNER JOIN orders o
        ON c.id = o.customer_id
GROUP BY c.id;

Result

Why?
INNER JOIN discards rows that have no matching counterpart in the joined table. Consequently, every row that appears in the result set also has a non‑NULL o.id, making COUNT(*) equal to COUNT(o.id).

LEFT JOIN Example (Where Most Bugs Happen)

Query

SELECT c.id,
       COUNT(*)        AS total_rows,
       COUNT(o.id)     AS orders_count
FROM   customers c
LEFT JOIN orders o
       ON c.id = o.customer_id
GROUP BY c.id;

Result

Why is Carol different?
LEFT JOIN retains all rows from the left table (customers). For customers without matching orders (e.g., Carol), the joined columns (o.id) are NULL.

• COUNT(*) counts the row itself, giving 1.
• COUNT(o.id) ignores the NULL value, resulting in 0.

Takeaways

• COUNT(*) always reflects the number of rows produced by the query, regardless of column values.
• COUNT(column) only counts rows where the specified column is not NULL.
• In outer joins (LEFT, RIGHT, FULL), NULL values introduced by the join can cause COUNT(column) to diverge from COUNT(*).
• Use the appropriate form of COUNT to avoid reporting errors and ensure dashboards display accurate metrics.