alt-text

SQL COUNT(*) vs COUNT(1) - What’s the Difference?

If you have ever worked with SQL for analytics or data engineering tasks you might have noticed people’s preference for using either COUNT(*) vs COUNT(1). So what is the difference between these approaches?

Both COUNT(*) and COUNT(1) will both count the number of rows in a table or query. But how they achieve this is slightly different.

How COUNT(*) Works

COUNT(*) is essentially special SQL syntax that indicates to count the number of rows. COUNT(*) will count rows regardless of null values. Even if every field in a row contains a null value, the row will still be counted.

COUNT(expression)

Outside of the COUNT(*) syntax, the count function expects an "expression" argument. A SQL expression is something that can be evaluated by the SQL engine resulting in a single value. SQL expressions can include columns, constants, variables and functions. This syntax will only count non-null values. Be aware that expression support can vary between DB/SQL engines.

COUNT(1) is using the COUNT(expression) syntax. In this case 1 is being used as an expression (constant) that will never be null, so all rows will be counted.

Here are some examples similar to COUNT(1) that will count all rows because the expression result value will always be non-null.

Performance Difference between COUNT(*) and COUNT(1)

COUNT(*) can be optimized at a lower level during execution, making it a better default choice for performance. COUNT(1) uses an expression argument, which might be evaluated for every row (depending on the DB/SQL Engine). For most modern databases there will be no performance difference, but if you are unsure about the optimizations built into a specific SQL engine, COUNT(*) is generally preferred.

Should I use COUNT(*) or COUNT(1)?

When counting records we recommend using COUNT(*). COUNT(*) seems to be more common in practice, and will more often be optimized for performance.