Why do I need “OR NULL” in MySQL when counting rows with a condition

by

This should reveal all

SELECT 4=4, 3=4, 1 or null, 0 or null

Output

1   |   0   |   1   |   NULL

Facts

  1. COUNT adds up the columns / expressions that evaluate to NOT NULL. Anything will increment by 1, as long as it is not null. Exception is COUNT(DISTINCT) where it increments only if it is not already counted.

  2. When a BOOLEAN expression is used on its own, it returns either 1 or 0.

  3. When a boolean is OR-ed with NULL, it is NULL only when it is 0 (false)

To others

Yes if the count is the ONLY column desired, one could use WHERE value=4 but if it is a query that wants to count the 4’s as well as retrieving other counts/aggregates, then the filter doesn’t work. An alternative would have been SUM(value=4), e.g.

SELECT sum(value=4)
  FROM test

More Related Contents:

  1. How to get a ranking result for special record in mysql [closed]
  2. SQL Query needed for fetch value from Two identical table but not fully identical table
  3. MySQL ERROR 1045 (28000): Access denied for user ‘bill’@’localhost’ (using password: YES)
  4. How to reset AUTO_INCREMENT in MySQL
  5. MySQL: Insert record if not exists in table [duplicate]
  6. MySQL Query GROUP BY day / month / year
  7. How do I escape special characters in MySQL?
  8. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  9. SQL counting all rows instead of counting individual rows
  10. MySQL – ERROR 1045 – Access denied
  11. Remote Connections Mysql Ubuntu
  12. MySQL InnoDB not releasing disk space after deleting data rows from table
  13. In MySQL, should I quote numbers or not?
  14. Dump all tables in CSV format using ‘mysqldump’
  15. MySQL add days to a date
  16. How can I loop through all rows of a table? (MySQL)
  17. Select columns across different databases
  18. Check for x consecutive days – given timestamps in database
  19. LOAD DATA INFILE Error Code : 13
  20. MySql update two tables at once
  21. Returning query results in predefined order
  22. Django MySQL full text search
  23. How to force MySQL to take 0 as a valid auto-increment value
  24. update a column by subtracting a value
  25. Does MySQL foreign_key_checks affect the entire database?
  26. MySQL: Many tables or many databases?
  27. Select only last value using group by at mysql
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. mysql double-quoted table names

Leave a Comment