What is the difference between COUNT(*) and COUNT(column), and when does it actually matter?
They count different things. The star counts the rows, and the column name counts the rows where that column has a value.
So when does it actually matter? Only when the column can be missing a value. If the column is marked NOT NULL, then the two are always the same number.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
assigned_to INTEGER
);
INSERT INTO tickets (id, title, assigned_to) VALUES
(1, 'Login is broken', NULL),
(2, 'Slow export', 7),
(3, 'Typo on the pricing page', NULL);
-- Two tickets, and two assigned tickets.
SELECT
COUNT(*) AS all_tickets,
COUNT(assigned_to) AS assigned_tickets
FROM tickets;
So what is going on? The star counts every row, so there are two tickets. The column name counts only the rows where somebody has been assigned, so there is one of those. The row with the missing value is in the first count and not in the second.
There is one important detail about the star that is worth knowing. The function COUNT(*) counts the rows without looking at any column at all, so the engine never has to look at the values. The function COUNT(column) has to look at the value of that column in every row, and it has to check whether it is missing. On a wide table that is a real difference, and it is one of the reasons that the star is the faster of the two.
The last question in this topic shows the case where the difference really matters.