Exportar (0) Imprimir
Expandir todo
Este artículo se tradujo de forma manual. Mueva el puntero sobre las frases del artículo para ver el texto original. Más información.
Traducción
Original
Personas que lo han encontrado útil: 4 de 7 - Valorar este tema

COALESCE (Transact-SQL)

Evalúa los argumentos por orden y devuelve el valor actual de la primera expresión que no se evalúa inicialmente como NULL.

Se aplica a: SQL Server (SQL Server 2008 a versión actual), Windows Azure SQL Database (Versión inicial a versión actual).

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

COALESCE ( expression [ ,...n ] ) 
expression

Es una expresión de cualquier tipo.

Devuelve el tipo de datos de expression con la precedencia de tipo de datos más alta. Si ninguna de las expresiones admiten valores NULL, el resultado tiene un tipo que no admite valores NULL.

Si todos los argumentos son NULL, COALESCE devuelve NULL. Al menos uno de los valores NULL debe ser NULL con tipo.

Comparación entre COALESCE y CASE

La expresión COALESCE es un método abreviado sintáctico para la expresión CASE. Es decir, el optimizador de consultas vuelve a escribir el código COALESCE(expression1,...n) como la siguiente expresión CASE:

CASE

   WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

Esto significa que los valores de entrada (expression1, expression2, expressionN, etc.) se evalúan varias veces. Además, de acuerdo con el estándar SQL, una expresión de valor que contiene una subconsulta se considera no determinista y la subconsulta se evalúa dos veces. En cualquier caso, se pueden devolver resultados diferentes entre la primera evaluación y las evaluaciones posteriores.

Por ejemplo, cuando se ejecuta el código COALESCE((subquery), 1), la subconsulta se evalúa dos veces. Por tanto, se pueden obtener resultados diferentes en función del nivel de aislamiento de la consulta. Por ejemplo, el código puede devolver NULL con el nivel de aislamiento de lectura confirmada (READ COMMITTED) en un entorno multiusuario. Para asegurarse de que se devuelven resultados estables, use el nivel de aislamiento SNAPSHOT ISOLATION o reemplace COALESE con la función ISNULL. También puede volver a escribir la consulta para insertar la subconsulta en una subselección como se muestra en el ejemplo siguiente.

SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END
FROM
(
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x
) AS T;

Comparación entre COALESCE e ISNULL

La función ISNULL y la expresión COALESCE tienen un propósito similar pero pueden comportarse de forma diferente.

  1. Puesto que ISNULL es una función, se evalúa solo una vez. Como se ha descrito anteriormente, los valores de entrada para la expresión COALESCE se pueden evaluar varias veces.

  2. La determinación del tipo de datos de la expresión resultante es diferente. ISNULL emplea el tipo de datos del primer parámetro, mientras que COALESCE sigue las reglas de la expresión CASE y devuelve el tipo de datos del valor con la prioridad más alta.

  3. La nulabilidad de la expresión de resultado es diferente para ISNULL que para COALESCE. El valor devuelto de ISNULL siempre se considera NOT NULL (suponiendo que el valor devuelto no admita NULL), mientras que COALESCE con parámetros no NULL se considera NULL. Por tanto, las expresiones ISNULL(NULL, 1) y COALESCE(NULL, 1), aunque son equivalentes, tienen valores diferentes de nulabilidad. Esto establece diferencias entre si usa estas expresiones en columnas calculadas, crea restricciones de clave o hace que el valor devuelto de una UDF escalar sea determinista para que se pueda indizar como se muestra en el ejemplo siguiente.

    USE tempdb;
    GO
    -- This statement fails because the PRIMARY KEY cannot accept NULL values
    -- and the nullability of the COALESCE expression for col2 
    -- evaluates to NULL.
    CREATE TABLE #Demo 
    ( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0) PRIMARY KEY, 
    col3 AS ISNULL(col1, 0) 
    ); 
    
    -- This statement succeeds because the nullability of the 
    -- ISNULL function evaluates AS NOT NULL.
    
    CREATE TABLE #Demo 
    ( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0), 
    col3 AS ISNULL(col1, 0) PRIMARY KEY 
    );
    
  4. Las validaciones para ISNULL y COALESCE también son diferentes. Por ejemplo, un valor NULL para ISNULL se convierte en int mientras que para COALESCE, debe proporcionar un tipo de datos.

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

A.Ejecutar un ejemplo sencillo

En el ejemplo siguiente se muestra cómo COALESCE selecciona los datos de la primera columna que tiene un valor distinto de NULL. Este ejemplo usa la base de datos AdventureWorks2012.

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;

B.Ejecutar un ejemplo complejo

En el ejemplo siguiente, la tabla wages incluye tres columnas con información acerca del sueldo anual de los empleados: la tarifa por hora, el salario y la comisión. No obstante, un empleado recibe solo un tipo de sueldo. Para determinar el importe total pagado a todos los empleados, utilice COALESCE para obtener solo los valores no NULL que se encuentran en hourly_wage, salary y commission.

SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
    DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    commission    decimal   NULL,
    num_sales     tinyint   NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
    (10.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (30.00, NULL, NULL, NULL),
    (40.00, NULL, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, 20000.00, NULL, NULL),
    (NULL, 30000.00, NULL, NULL),
    (NULL, 40000.00, NULL, NULL),
    (NULL, NULL, 15000, 3),
    (NULL, NULL, 25000, 2),
    (NULL, NULL, 20000, 6),
    (NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52, 
   salary, 
   commission * num_sales) AS money) AS 'Total Salary' 
FROM dbo.wages
ORDER BY 'Total Salary';
GO

El conjunto de resultados es el siguiente.

Total Salary

------------

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

(12 row(s) affected)

¿Te ha resultado útil?
(Caracteres restantes: 1500)
Gracias por sus comentarios

Adiciones de comunidad

AGREGAR
Mostrar:
© 2014 Microsoft. Reservados todos los derechos.