🔢COUNT()

Three common spellings, three different questions

COUNT(*) counts how many rows survive FROM/WHERE/JOIN. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values, pairs need a row constructor in standard SQL. Picking the wrong variant is a frequent source of “the dashboard does not match the CSV” bugs.

SQL

COUNT(*) vs COUNT(column)

SELECT COUNT(*) AS all_rows,
       COUNT(qc_note) AS rows_with_note
FROM plates;

✓Illustrative result set

all_rows	rows_with_note
1200	88

SQL

COUNT(DISTINCT …)

SELECT COUNT(DISTINCT species) AS species_count
FROM specimens;

✓Illustrative result set

species_count
37

SELECT COUNT(*) FROM orders o JOIN order_lines l …

Joining to children multiplies parent rows; COUNT(*) counts joined rows, not distinct orders.

Know whether you need rows, lines, or orders

Use COUNT(DISTINCT o.order_id), subquery counts, or EXISTS when you only need to test presence.

📌Quick reference

COUNT(1) is not magic, optimizers treat it like COUNT(*) on most engines.

Form	Counts
COUNT(*)	All rows after joins/filters
COUNT(1)	Same intent as COUNT(*) in practice
COUNT(DISTINCT a, b)	Distinct pairs (syntax varies slightly by engine)

Existence checks

For “is there any row matching …?”, EXISTS (SELECT 1 …) often reads clearer than COUNT(*) > 0 and can short-circuit once a match is found.

TL;DR

COUNT(*) counts rows (including those where every column is NULL). COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values, often more expensive because it needs deduplication. For existence, prefer EXISTS over counting when you only need a yes/no.