Gotcha: SQL COUNT with NULL values

COUNT(*) and COUNT(column) return different results when NULL values are present.

COUNT behavior with NULLs:

CREATE TABLE t (id INT, name TEXT, email TEXT);
INSERT INTO t VALUES (1, 'Alice', 'a@b.com');
INSERT INTO t VALUES (2, 'Bob', NULL);
INSERT INTO t VALUES (3, NULL, NULL);

-- COUNT(*) counts ALL rows (including NULLs):
SELECT COUNT(*) FROM t; -- 3

-- COUNT(column) counts NON-NULL values only:
SELECT COUNT(name) FROM t; -- 2 (skips row 3)
SELECT COUNT(email) FROM t; -- 1 (skips rows 2 and 3)

-- COUNT(DISTINCT column) counts unique non-null values:
SELECT COUNT(DISTINCT name) FROM t; -- 2

-- Common mistake in LEFT JOIN:
SELECT u.name, COUNT(o.id) AS order_count -- Correct!
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- COUNT(o.id) = 0 for users with no orders (NULLs not counted)
-- COUNT(*) would give 1 (counts the joined NULL row!)

-- SUM also ignores NULLs:
SELECT SUM(amount) FROM orders; -- NULLs ignored
-- But SUM of all NULLs = NULL, not 0:
SELECT COALESCE(SUM(amount), 0) FROM empty_table; -- 0

SQL aggregate functions (except COUNT(*)) ignore NULL values. This is consistent but often surprising, especially with LEFT JOINs.