¿Por qué necesito “O NULO” en MySQL al contar filas con una condición

Hay una pregunta sobre la función agregada de COUNT () de MySQL que me sigue apareciendo en mi cabeza de vez en cuando. Me gustaría obtener una explicación de por qué está funcionando como está.

Cuando comencé a trabajar con MySQL, aprendí rápidamente que su COUNT (condición) solo parece funcionar correctamente si la condición también contiene un O NULL al final. En el caso de condiciones COUNT más complicadas, era un proceso empírico averiguar dónde ponerlo exactamente. En MSSQL no necesita esto O NULO para obtener los resultados adecuados, por lo que me gustaría saber la explicación. Entonces, aquí hay un ejemplo.

Permite tener una tabla muy básica con la siguiente estructura y datos:

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); 

Escenario: Me gustaría contar cuántas filas tengo donde el valor = 4. Una solución obvia sería filtrar por él utilizando un DÓNDE y hacer un RECUENTO (*), pero estoy interesado en una solución basada en COUNT (condición).

Entonces, la solución que viene a mi mente es:

 SELECT COUNT(value=4) FROM test 

El resultado es 10. Esto es obviamente incorrecto.

Segundo bash con O NULO:

 SELECT COUNT(value=4 OR NULL) FROM test 

El resultado es 3. Es correcto.

¿Alguien puede explicar la lógica detrás de esto? ¿Es esto un error en MySQL o hay una explicación lógica de por qué tengo que agregar ese aspecto extraño O NULO al final de la condición COUNT para obtener el resultado correcto?

    Esto debería revelar todo

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

    Salida

     1 | 0 | 1 | NULL 

    Hechos

    1. COUNT sum las columnas / expresiones que evalúan a NOT NULL. Cualquier cosa boostá en 1, siempre que no sea nulo. La excepción es COUNT (DISTINCT) donde aumenta solo si no está contada.

    2. Cuando una expresión BOOLEAN se usa por sí misma, devuelve 1 o 0.

    3. Cuando un booleano está OR -ed con NULL, es NULL solo cuando es 0 (falso)

    A otros

    Sí, si el recuento es la ÚNICA columna deseada, se podría usar WHERE value=4 pero si se trata de una consulta que desea contar los 4 así como recuperar otros recuentos / agregados, entonces el filtro no funciona. Una alternativa habría sido SUM(value=4) , por ejemplo

     SELECT sum(value=4) FROM test 

    COUNT() acepta un argumento, que se trata como NULL o NOT NULL . Si NOT NULL es NOT NULL , entonces incrementa el valor y no hace nada de lo contrario.

    En su caso, el value=4 expresión value=4 es TRUE o FALSE , obviamente tanto true como false no son nulos, por eso obtienes 10.

    pero estoy interesado en una solución basada en COUNT (condición).

    La solución basada en el count siempre será más lenta (mucho más lenta), ya que causará un recorrido completo de la tabla y una comparación iterativa de cada valor.

    COUNT(expression) cuenta el número de filas para las cuales la expresión no es NULL. El value=4 expresión value=4 es solo NULL si el valor es NULL; de lo contrario, es TRUE (1) o FALSE (0), y ambos se cuentan.

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

    Puede usar SUM en su lugar:

     SELECT SUM(value=4) FROM test 

    Esto no es particularmente útil en su ejemplo específico, pero puede ser útil si desea contar filas que satisfagan múltiples predicados diferentes usando un escaneo de tabla única como en la siguiente consulta:

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

    Sugeriría que encuentre que la syntax más estándar se mueve mejor entre diferentes motores de base de datos y siempre dará el resultado correcto.

      select count(*) from test where value = 4 

    ¿La syntax que utilizó es una variante Mysql?

    Es porque COUNT (expresión) cuenta VALORES. En teoría de SQL, NULL es un ESTADO, no un VALOR y por lo tanto no se cuenta. NULL es un estado que significa que el valor del campo es desconocido.

    Ahora, cuando escribe “valor = 4”, se evalúa como booleano VERDADERO o FALSO. Como tanto TRUE como FALSE son VALUES, el resultado es 10.

    Cuando agrega “O NULO”, en realidad tiene “VERDADERO O NULO” y “FALSO O NULO”. Ahora, “VERDADERO O NULO” se evalúa como VERDADERO, mientras que “FALSO O NULO” se evalúa como NULO. Por lo tanto, el resultado es 3, porque solo tiene 3 valores (y siete estados NULL).