count(*) and count(column_name), what’s the diff?

by
  • COUNT(*) counts all rows in the result set (or group if using GROUP BY).
  • COUNT(column_name) only counts those rows where column_name is NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable).
  • COUNT(1) is the same as COUNT(*) since 1 can never be NULL.

To see the difference in the results you can try this little experiment:

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
    COUNT(*) AS a,
    COUNT(x) AS b,
    COUNT(1) AS c
FROM table1;

Result:

a   b   c
3   2   3

More Related Contents:

  1. SQL split values to multiple rows
  2. How do I escape reserved words used as column names? MySQL/Create Table
  3. How to store multiple options in a single table?
  4. How to populate a table with a range of dates?
  5. Get the new record primary key ID from MySQL insert query?
  6. What is the opposite of GROUP_CONCAT in MySQL?
  7. How to Perform an UPSERT so that I can use both new and old values in update part
  8. brew install mysql on macOS
  9. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  10. MySQL: selecting rows where a column is null
  11. How do I check to see if a value is an integer in MySQL?
  12. Add 2 hours to current time in MySQL?
  13. MySQL/MariaDB – order by inside subquery
  14. How to run SQL script in MySQL?
  15. My MySQL trigger doesn’t work, simple syntax, not complicated
  16. Removing MySQL 5.7 Completely [closed]
  17. How to optimize COUNT(*) performance on InnoDB by using index
  18. script to convert mysql dump sql file into format that can be imported into sqlite3 db
  19. MySQL Stored Procedures not working with SELECT (basic question)
  20. Sum results of a few queries and then find top 5 in SQL
  21. ERROR 1148: The used command is not allowed with this MySQL version
  22. ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  23. Too many auto increments with ON DUPLICATE KEY UPDATE
  24. ALTER TABLE ADD COLUMN takes a long time
  25. MySql Query Replace NULL with Empty String in Select
  26. What are the max number of allowable parameters per database provider type?
  27. How to handle a query with special characters / (forward slash) and \ (backslash)
  28. Mysql server does not support 4-byte encoded utf8 characters
  29. Does MySQL support historical date (like 1200)?
  30. mySQL query to search all tables within a database for a string?

Leave a Comment