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

Question

There is a question about MySQL's COUNT() aggregate function that keeps popping into my head time to time. I would like to get some explanation to why it is working the way it is.

When I started working with MySQL I quickly learned that its COUNT(condition) seems only to work properly if condition also contains an OR NULL in the end. In case of more complicated COUNT conditions it was an empirical process to find out where to put it exactly. In MSSQL you do not need this OR NULL to get proper results, so I would like to know the explanation for it. So, here is an example.

Lets have a very basic table with the following structure and data:

CREATE TABLE test (
  `value` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO test (value) VALUES(1);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(5);
INSERT INTO test (value) VALUES(6);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(5);
INSERT INTO test (value) VALUES(2);
INSERT INTO test (value) VALUES(8);
INSERT INTO test (value) VALUES(1);

Scenario: I would like to count how many rows I have where the value = 4. An obvious solution would be to filter for it using a WHERE and do a COUNT(*) but I am interested in a COUNT(condition) based solution.

So, the solution that comes to my mind is:

SELECT COUNT(value=4) 
  FROM test

The result is 10. This is obviously wrong.

Second attempt with OR NULL:

SELECT COUNT(value=4 OR NULL) 
  FROM test

The result is 3. It is correct.

Can someone explain the logic behind this? Is this some bug in MySQL or is there a logical explanation why I need to add that strange-looking OR NULL to the end of the COUNT condition to get the correct result?

Reuter

Actually, it looks like a bug in MSSQL. – Nightie

@Milan - It would be if we were discussing MSSQL – Magnetometer

@Richard, did you even bother to read the question? It does speak about MSSQL being right and MySQL presumably being wrong. – Nightie

Answer 1

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

Magnetometer

+1 for spelling out the truth table of boolean operations with NULL – Sidetrack

And +1 for this one for the explanation as to why it behaves as it does, and the use cases where just using where is inadequate. – Biostatics

Great answer, it explains it all. I would be interesting to know why they decided to implement it this way, e.g. count based on IS NULL / IS NOT NULL instead of the much more common true / false approach. – Reuter

@Reuter you may have it about face. COUNT ( boolean ) is actually less common. SQL Server doesn't even let you use boolean expressions alone (bit != boolean). More people count columns (existence of value == [not] null) rather than evaluate a condition. – Magnetometer

Answer 2

COUNT() function accepts an argument, that is treated as NULL or NOT NULL. If it is NOT NULL - then it increments the value, and doesn't do anything otherwise.

In your case expression value=4 is either TRUE or FALSE, obviously both true and false are not null, that is why you get 10.

but I am interested in a COUNT(condition) based solution.

The count-based solution will be always slower (much slower), because it will cause table fullscan and iterative comparison of each value.

Arhna

You answered half of the question.. incomplete was the word I was after – Magnetometer

Answer 3

COUNT(expression) counts the number of rows for which the expression is not NULL. The expression value=4 is only NULL if value is NULL, otherwise it is either TRUE (1) or FALSE (0), both of which are counted.

1 = 4         | FALSE
4 = 4         | TRUE
1 = 4 OR NULL | NULL
4 = 4 OR NULL | TRUE

You could use SUM instead:

SELECT SUM(value=4) FROM test

This is not particularly useful in your specific example but it can be useful if you want to count rows satisfying multiple different predicates using a single table scan such as in the following query:

SELECT
    SUM(a>b) AS foo,
    SUM(b>c) AS bar,
    COUNT(*) AS total_rows
FROM test

Sternberg

Um, SUM(a>b) can still be replaced with COUNT(a>b OR NULL), can it not? So I do not really see how SUM() better suits here than in the OP's example. – Plantagenet

Well for one thing, sum is less to type. As I said, it doesn't give much in the specific example the op gave - a where clause can be used instead. But it can be useful in other situations. – Sternberg

Answer 4

I would suggest that you will find the more standard syntax moves better between different database engines and will always give the correct result.

 select count(*)
 from test
 where value = 4

Is the syntax you used a Mysql variant?

Illjudged

Answer 5

It's because COUNT(expression) counts VALUES. In SQL theory, NULL is a STATE, not a VALUE and thus is it not counted. NULL is a state that means that field's value is unknown.

Now, when you write "value=4" this evaluates to boolean TRUE or FALSE. Since both TRUE and FALSE are VALUES, the result is 10.

When you add "OR NULL", you actually have "TRUE OR NULL" and "FALSE OR NULL". Now, "TRUE OR NULL" evaluates to TRUE, while "FALSE OR NULL" evaluates to NULL. Thus the result is 3, because you only have 3 values (and seven NULL states).

Ground

Answer 6

Here is a intuitive picture after I test it:

Amaryl