Este artículo proporciona un resumen de las funciones disponibles para manejar valores nulos. Para una descripción más detallada, siga los enlaces que se encuentran al final del artículo.
- Antecedentes
- NVL
- DECODE
- NVL2
- COALESCE
- NULLIF
- LNNVL
- NANVL
- SYS_OP_MAP_NONNULL
Antecedentes
La mayoría de los ejemplos de este artículo requieren la siguiente tabla.
DROP TABLE null_test_tab;
CREATE TABLE null_test_tab (
id NUMBER,
col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10)
);
INSERT INTO null_test_tab values (1, 'ONE', 'TWO', 'THREE', 'FOUR');
INSERT INTO null_test_tab values (2, NULL, 'TWO', 'THREE', 'FOUR');
INSERT INTO null_test_tab values (3, NULL, NULL, 'THREE', 'FOUR');
INSERT INTO null_test_tab values (4, NULL, NULL, 'THREE', 'THREE');
COMMIT;
Si consultamos los datos en la tabla, vemos el siguiente resultado.
SELECT * FROM null_test_tab ORDER BY id;
ID COL1 COL2 COL3 COL4
---------- ---------- ---------- ---------- ----------
1 ONE TWO THREE FOUR
2 TWO THREE FOUR
3 THREE FOUR
4 THREE THREE
4 rows selected.
SQL>
Recuerde, las comparaciones con nulos siempre dan como resultado nulos, por lo que las consultas no pueden usar operadores de comparación normales como «=» o «! =».
SELECT * FROM null_test_tab WHERE col1 = NULL ORDER BY id;
no rows selected
SQL>
En su lugar, deben utilizar los operadores IS NULL o IS NOT NULL.
SELECT * FROM null_test_tab WHERE col1 IS NULL ORDER BY id;
ID COL1 COL2 COL3 COL4
---------- ---------- ---------- ---------- ----------
2 TWO THREE FOUR
3 THREE FOUR
4 THREE THREE
3 rows selected.
SQL>
NVL
La función NVL le permite reemplazar los valores nulos con un valor predeterminado. Si el valor del primer parámetro es nulo, la función devuelve el valor del segundo parámetro. Si el primer parámetro es cualquier valor que no sea nulo, se devuelve sin cambios.
Sabemos que COL1 en la tabla de prueba contiene nulos en todas las filas excepto en la primera. Usando la función NVL reemplazamos los valores nulos con ‘CERO’.
SELECT id, NVL(col1, 'ZERO') AS output
FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 ONE
2 ZERO
3 ZERO
4 ZERO
4 rows selected.
SQL>
DESCODE
La función DECODE no es específicamente para manejar valores nulos, pero puede usarse de manera similar a la función NVL, como se muestra en el siguiente ejemplo.
SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS output
FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 ONE
2 ZERO
3 ZERO
4 ZERO
4 rows selected.
SQL>
NVL2
La función NVL2 acepta tres parámetros. Si el valor del primer parámetro no es nulo, devuelve el valor del segundo parámetro. Si el valor del primer parámetro es nulo, devuelve el tercer parámetro.
La siguiente consulta muestra NVL2 en acción.
SELECT id, NVL2(col1, col2, col3) AS output
FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 TWO
2 THREE
3 THREE
4 THREE
4 rows selected.
SQL>
La primera fila de la tabla de prueba tiene un valor no nulo en COL1, por lo que se devuelve el valor de COL2. Todas las demás filas contienen nulo en COL1, por lo que se devuelve el valor de COL3.
COALESCE
La función COALESCE se introdujo en Oracle 9i. Acepta dos o más parámetros y devuelve el primer valor no nulo de una lista. Si todos los parámetros contienen valores nulos, devuelve nulos.
SELECT id, COALESCE(col1, col2, col3) AS output
FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 ONE
2 TWO
3 THREE
4 THREE
4 rows selected.
SQL>
NULLIF
La función NULLIF se introdujo en Oracle 9i. Acepta dos parámetros y devuelve nulo si ambos parámetros son iguales. Si no son iguales, se devuelve el primer valor del parámetro.
En nuestra tabla de prueba, los valores de COL3 y COL4 son iguales en la fila 4, por lo que solo esperaríamos que se devolviera un valor nulo para esa fila utilizando la siguiente consulta.
SELECT id, NULLIF(col3, col4) AS output
FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 THREE
2 THREE
3 THREE
4
4 rows selected.
SQL>
LNNVL
La función LNNVL ha estado disponible desde al menos Oracle 9i, pero no estaba documentada hasta Oracle 11g. Se utiliza en una cláusula where para evaluar una condición. Si esta condición se evalúa como falsa o desconocida, devuelve verdadera. Si la condición se evalúa como verdadera, devuelve falso.
SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id; ID COL3 ---------- ---------- 1 THREE 1 row selected. SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id; ID COL3 ---------- ---------- 3 THREE 4 THREE 2 rows selected. SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id; ID COL3 ---------- ---------- 1 THREE 2 THREE 3 THREE 4 THREE 4 rows selected. SQL>
NANVL
La función NANVL se introdujo en Oracle 10g para su uso con los tipos de datos BINARY_FLOAT y BINARY_DOUBLE, que pueden contener un valor especial «Not a Number » o «NaN». La función es similar a NVL, pero en lugar de probar nulos, prueba los valores «NaN». La siguiente tabla se utilizará para demostrarlo.
DROP TABLE nanvl_test_tab;
CREATE TABLE nanvl_test_tab (
id NUMBER,
col1 BINARY_DOUBLE
);
INSERT INTO nanvl_test_tab VALUES (1, 1234.5678);
INSERT INTO nanvl_test_tab VALUES (2, 'INF');
INSERT INTO nanvl_test_tab VALUES (3, '-INF');
INSERT INTO nanvl_test_tab VALUES (4, 'NaN');
COMMIT;
Si consultamos la tabla vemos los siguientes datos.
SELECT * FROM nanvl_test_tab ORDER BY id;
ID COL1
---------- ----------
1 1.235E+003
2 Inf
3 -Inf
4 Nan
4 rows selected.
SQL>
A continuación, volvemos a consultar los datos, pero convertimos los valores «NaN» a «0» mediante la función NANVL.
SELECT id, col1, NANVL(col1, 0) AS output
FROM nanvl_test_tab;
ID COL1 OUTPUT
---------- ---------- ----------
1 1.235E+003 1.235E+003
2 Inf Inf
3 -Inf -Inf
4 Nan 0
4 rows selected.
SQL>
SYS_OP_MAP_NONNULL
Hemos visto que una comparación de «NULL = NULL» siempre devolverá falso, pero a veces querrá que devuelva verdadero. Es posible hacer que esto suceda usando las funciones NVL y DECODE, pero dependiendo de cómo las use, esto depende de que convierta el valor nulo en otro valor que espera que nunca esté presente en la columna o variable.
SELECT id, 'col1=col2' FROM null_test_tab WHERE NVL(col1, '!null!') = NVL(col2, '!null!'); ID 'COL1=COL ---------- --------- 3 col1=col2 4 col1=col2 2 rows selected. SQL> SELECT id, 'col1=col2' FROM null_test_tab WHERE DECODE(col1, NULL, '!null!', col1) = DECODE(col2, NULL, '!null!', col2); ID 'COL1=COL ---------- --------- 3 col1=col2 4 col1=col2 2 rows selected. SQL> SELECT id, 'col1=col2' FROM null_test_tab WHERE DECODE(col1, col2, '!match!', col1) = '!match!'; ID 'COL1=COL ---------- --------- 3 col1=col2 4 col1=col2 2 rows selected. SQL>
Una alternativa es utilizar la función SYS_OP_MAP_NONNULL no documentada para permitir coincidencias nulas.
SELECT id, 'col1=col2'
FROM null_test_tab
WHERE SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);
ID 'COL1=COL
---------- ---------
3 col1=col2
4 col1=col2
2 rows selected.
SQL>
Nota: Recuerde, SYS_OP_MAP_NONNULL es una función no documentada, por lo que, estrictamente hablando, no debería usarse en una aplicación de producción.
