coalesce mysql para que sirve

¿Cuál es la diferencia entre ifnull y coalesce en mysql?

Question

select ifnull(null,''replaces the null'')
-->replaces the null
select coalesce(null,null,''replaces the null'')
-->replaces the null

En ambas cláusulas, la diferencia principal es el argumento que pasa. Para ifnull son dos parámetros y se ifnull 2 o 3, podemos pasar, excepto que ¿tenemos alguna otra diferencia entre estos dos? Y cómo difiere en MSSql.

Answer 1

Pros de COALESCE

• COALESCE es una función estándar de SQL .

  Mientras que IFNULL es específico de MySQL y su equivalente en MSSQL ( ISNULL ) es específico de MSSQL.

• COALESCE puede funcionar con dos o más argumentos (de hecho, puede funcionar con un solo argumento, pero es bastante inútil en este caso: COALESCE(a) ≡ a ).

  Mientras que IFNULL de MySQL y IFNULL MSSQL son versiones limitadas de COALESCE que pueden funcionar solo con dos argumentos.

Contras de COALESCE

• Según la documentación de Transact SQL , COALESCE es solo un COALESCE sintaxis para CASE y puede evaluar sus argumentos más de una vez . Más detalladamente: COALESCE(a1, a2, …, aN) ≡ CASE WHEN (a1 IS NOT NULL) THEN a1 WHEN (a2 IS NOT NULL) THEN a2 ELSE aN END . Esto reduce en gran medida la utilidad de COALESCE en MSSQL.

  Por otro lado, ISNULL en MSSQL es una función normal y nunca evalúa sus argumentos más de una vez. COALESCE en MySQL y PostgreSQL no evalúa sus argumentos más de una vez.

• En este punto del tiempo, no sé exactamente cómo los estándares de SQL definen COALESCE .

  Como vemos en el punto anterior, las implementaciones reales en RDBMS varían: algunas (por ejemplo, MSSQL) hacen que COALESCE evalúe sus argumentos más de una vez, algunas (p. Ej., MySQL, PostgreSQL): no.

  c-treeACE, que afirma que su implementación COALESCE es compatible con SQL-92 , dice: "Esta función no está permitida en una cláusula GROUP BY. Los argumentos para esta función no pueden ser expresiones de consulta". No sé si estas restricciones están realmente dentro del estándar SQL; la mayoría de las implementaciones actuales de COALESCE (por ejemplo, MySQL, PostgreSQL) no tienen tales restricciones. IFNULL / ISNULL , como funciones normales, tampoco tienen tales restricciones.

Currículum

A menos que enfrente restricciones específicas de COALESCE en RDBMS específicos, le recomendaría usar siempre COALESCE como más estándar y más genérico.

Las excepciones son:

• Expresiones calculadas largas o expresiones con efectos secundarios en MSSQL (como, por documentación, COALESCE(expr1, …) puede evaluar expr1 dos veces).
• Uso dentro de GROUP BY o con expresiones de consulta en c-treeACE.
• Etc.

Answer 2

Diferencias en SQL-Server :

• No hay función IFNULL() pero ISNULL() similar ISNULL()

• ISNULL toma solo 2 parámetros mientras que COALESCE toma un número variable de parámetros

• COALESCE se basa en el estándar ANSI SQL mientras que ISNULL es una función TSQL patentada

• Las validaciones para ISNULL y COALESCE también son diferentes. Por ejemplo, el valor NULL para ISNULL se convierte a int, mientras que para COAELSCE debe proporcionar un tipo. Ex:

  • ISNULL(NULL,NULL) : es int.

  • COALESCE(NULL,NULL) : lanzará un error.

  • COALESCE(CAST(NULL as int),NULL) : es válido y devuelve int.

• Determinación del tipo de datos de la expresión resultante: ISNULL utiliza el primer tipo de parámetro, COALESCE sigue las reglas de expresión CASE y devuelve el tipo de valor con mayor precedencia.

Answer 3

Este db2 SQL no funcionará con COALESE, no veré ninguna fila recuperada. Como utilicé IFNULL, funciona como se esperaba

select a.mbitno ,a.mbstqt,ifnull(b.apr,0)
from
(
select mmstcd,mbstat,mbfaci,mbwhlo,mbitno,mbstqt,MBALQT from libl.mitbal inner join libl.mitmas on
mmcono=mbcono and mmitno=mbitno
where mbcono=200 and mbstat in (''20'',''50'') and mmstcd>0
)
as a left join
(
select mlfaci,mlwhlo,mlitno,mlstas,sum(mlstqt) as APR from libl.mitloc where mlcono=200 and mlstas=''2''
group by mlfaci,mlwhlo,mlitno,mlstas
)
b on b.mlfaci=a.mbfaci and b.mlwhlo=a.mbwhlo and b.mlitno=a.mbitno
where a.mbitno in ''GWF0240XPEC'' and a.mbstqt>0 and a.mbstqt<>ifnull(b.apr,0)

Answer 4

La principal diferencia entre los dos es que la función IFNULL toma dos argumentos y devuelve el primero si no es NULL o el segundo si el primero es NULL .

COALESCE función COALESCE puede tomar dos o más parámetros y devuelve el primer parámetro que no sea NULL, o NULL si todos los parámetros son nulos, por ejemplo:

SELECT IFNULL(''some value'', ''some other value'');
-> returns ''some value''
SELECT IFNULL(NULL,''some other value'');
-> returns ''some other value''
SELECT COALESCE(NULL, ''some other value'');
-> returns ''some other value'' - equivalent of the IFNULL function
SELECT COALESCE(NULL, ''some value'', ''some other value'');
-> returns ''some value''
SELECT COALESCE(NULL, NULL, NULL, NULL, ''first non-null value'');
-> returns ''first non-null value''

ACTUALIZACIÓN: MSSQL hace un tipo más estricto y la comprobación de parámetros. Además, no tiene la función IFNULL sino la función ISNULL , que necesita conocer los tipos de argumentos. Por lo tanto:

SELECT ISNULL(NULL, NULL);
-> results in an error
SELECT ISNULL(NULL, CAST(NULL as VARCHAR));
-> returns NULL

También la función COALESCE en MSSQL requiere que al menos un parámetro sea no nulo, por lo tanto:

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
-> results in an error
SELECT COALESCE(NULL, NULL, NULL, NULL, ''first non-null value'');
-> returns ''first non-null value''

Answer 5

ifnull solo puede reemplazar un valor nulo del primer parámetro. Mientras que coalesce puede reemplazar cualquier valor con otro valor. Con la coalesce en SQL estándar, puede tener muchos parámetros que transforman muchos valores.

EDITA el ejemplo de acuerdo a los comentarios a continuación.

Ejemplo: coalesce(null, null, null, ''b*'', null, ''null*'') devuelve ''b *'' y no es posible hacer con ifnull .