Coalesce SQL Server

Usando la función Coalesce

En una base de datos es una buena práctica que no existan valores Null, esto se puede evitar usando la restricción de tipo Default y desde la aplicación el programador puede enviar un dato predeterminado en los campos que el usuario no ingrese valor. (Ver Crear tablas)

Por otro lado, debería realizarse tareas para revisar que la base de datos
no tenga valores Null, pero si los hubiera, es necesario en muchos casos
evitarlos o tenerlos en cuenta en las consultas.

Como usar Coalesce en SQL Server

La función Coalesce evalua un grupo de valores y retorna el primer valor de la lista de argumentos que no tiene valor Null.
Sintaxis
Coalesce(Expresión1 [, Expresión2, Expresión3, …])
Donde:
Expresión1 [, Expresión2, Expresión3, …] son las expresiones que pueden contener valores Null.

Ejemplos

Ejercicio 1

Reporte del primer valor no Null
select coalesce(Null, Null, ‘valor válido’, ‘Otro dato’, Null) As ‘Dato’
go
El resultado se muestra en la siguiente imagen

Ejercicio 2

Crear una tabla con dos campos que pueden tener valores Null
En este ejercicio se va a crear una tabla con algunos campos que permiten valores Null, luego se va a comparar los listados sin usar y usando la función Coalesce

Usando la base de datos Northwind
use Northwind
go
Create Table PruebaCoalesce
(
PruebaCodigo nchar(4),
PruebaDescripcion nvarchar(100),
PruebaDato1 nvarchar(50),
PruebaDato2 nvarchar(50),
PruebaDato3 nvarchar(50),
constraint PruebaPK primary key (PruebaCodigo)
)
go
insert into PruebaCoalesce values
(‘8521′,’Trainer SQL Server’,Null, ‘Valor insertado’,’Dato Válido’),
(‘1598′,’Guía profesional SQL’,’Primer valor’, Null,’Servicios’),
(‘3654′,’Datos para SQL’,Null, Null,’SQL Server’),
(‘2596′,’Valor de Coalesce’,Null, Null,’Programando SQL’),
(‘7533′,’Conviertete en DBA’,Null, ‘Recuerdo’,’Dato Válido’),
(‘4569′,’DBA por accidente, es válido’,’Casino SQL’, ‘Valor insertado’,’Dato Válido’),
(‘8547′,’Funciones de SQL Server’,Null, Null,’Fin de Prueba’)
go
El listado de todos los campos
select * from PruebaCoalesce
go
El resultado se muestra en la siguiente imagen

Usando Coalesce, para obtener el primer resultado válido de una de las columnas con nombre PruebaDato
select [PruebaCodigo], [PruebaDescripcion], [PruebaDato1],
[PruebaDato2], [PruebaDato3],
Coalesce([PruebaDato1], [PruebaDato2], [PruebaDato3]) As ‘Resultado’
from PruebaCoalesce
go
El resultado se muestra en la siguiente imagen
Note la última columna, muestra el primer valor válido diferente de Null.

El listado anterior podría usarse de la siguiente forma
select [PruebaCodigo], [PruebaDescripcion],
Coalesce([PruebaDato1], [PruebaDato2], [PruebaDato3]) As ‘Resultado’
from PruebaCoalesce
go
El resultado se muestra en la siguiente imagen

La columna Resultado tiene el primer valor no Null de los campos PruebaDato1, PruebaDato2 y PruebaDato4

Ejercicio 3

En el departamento de personal se ha diseñado una tabla que permite tres formas de pago diferentes, una teniendo en cuenta la cantidad de horas trabajadas, otra con un salario asignado y una tercera teniendo en cuenta una comisión por ventas.

Create table Pagos
(
EmpleadoCodigo nchar(4),
EmpleadoNombre nvarchar(100),
PagoHora Numeric(9,2),
Salario Numeric(9,2),
Comision Numeric(9,2),
NumeroVentas Numeric(9,2)
constraint PagosPK primary key (EmpleadoCodigo)
)
go
INSERT INTO Pagos (EmpleadoCodigo, EmpleadoNombre, PagoHora, Salario,
Comision, NumeroVentas) VALUES
(‘F698’, ‘FERNANDO LUQUE’, 120.00, NULL, NULL, NULL),
(‘T673’, ‘INGRID CHAVEZ’, 80.00, NULL, NULL, NULL),
(‘M345’, ‘ROXANA VALLEJO’, 90.00, NULL, NULL, NULL),
(‘E150’, ‘JOSE ALCANTARA’, 60.00, NULL, NULL, NULL),
(‘V970’, ‘VALENTINO CARRASCO’,NULL, 8000.00, NULL, NULL),
(‘R456’, ‘CARLOS MENDIOLA’,NULL, 4000.00, NULL, NULL),
(‘C689’, ‘RENATO CAMPOS’,NULL, 3000.00, NULL, NULL),
(‘S324’, ‘PEDRO CUBA’,NULL, 7500.00, NULL, NULL),
(‘H893’, ‘ESMERALDA TERRANOVA’,NULL, NULL, 1500, 5),
(‘W437’, ‘ARACELY WONG’,NULL, NULL, 3200, 4),
(‘N045’, ‘VICTOR SANCHEZ’,NULL, NULL, 720, 12),
(‘D996’, ‘MARIO LIZARZABURO’,NULL, NULL, 1400, 3)
go
El listado de los Empleados se muestra como sigue:
select * from Pagos
go
El resultado se muestra en la siguiente imagen

Se desea mostrar el listado de los empleados y sus pagos. El que tiene asignado un pago por hora se calcula en base a 22 días y a 8 horas diarias. Los que tienen asignado una comisión se calcula el pago multiplicando la comisión por el número de ventas.
SELECT P.EmpleadoCodigo As ‘Código’, P.EmpleadoNombre As ‘Empleado’,
Cast(COALESCE(PagoHora* 8 * 22, Salario, Comision * NumeroVentas)
As Numeric(10,2)) As ‘Pago Mensual’
From Pagos As P
order by [Pago Mensual] desc
go
El resultado se muestra en la siguiente imagen

Usando Case en lugar de Coalesce (Vea Estructura Case)

La estructura Case se puede usar en lugar de la función Coalesce.
En el ejemplo anterior, el resultado del listado usando Case es como sigue

SELECT P.EmpleadoCodigo As ‘Código’, P.EmpleadoNombre As ‘Empleado’,
(Case
When P.PagoHora is not null then PagoHora* 8 * 22
when P.Salario is not null then Salario
When P.Comision is not null then Comision * NumeroVentas
End
) As ‘Pago Mensual’
From Pagos As P
order by [Pago Mensual] desc
go
El resultado se muestra en la siguiente imagen

Analizando el plan estimado de ejecución
Para la instrucción usando la función Coalesce:

SELECT P.EmpleadoCodigo As ‘Código’, P.EmpleadoNombre As ‘Empleado’,
Cast(COALESCE(PagoHora* 8 * 22, Salario, Comision * NumeroVentas)
As Numeric(10,2)) As ‘Pago Mensual’
From Pagos As P
order by [Pago Mensual] desc
go
Costo: 0.0147249
El resultado se muestra en la siguiente imagen

Para la instrucción usando Case:
SELECT P.EmpleadoCodigo As ‘Código’, P.EmpleadoNombre As ‘Empleado’,
(Case
When P.PagoHora is not null then PagoHora* 8 * 22
when P.Salario is not null then Salario
When P.Comision is not null then Comision * NumeroVentas
End
) As ‘Pago Mensual’
From Pagos As P
order by [Pago Mensual] desc
go
Costo: 0.0147249
Note que son exactamente iguales, posiblemente la elección sea de acuerdo a la escritura de la instrucción, Coalesce parece ser mas sencilla.
El resultado se muestra en la siguiente imagen

Ejercicio 4

Listado de correos de un participante, puede ser que un participante no tenga correo, en ese caso se mostrará el mensaje «Sin correo», lo mismo para los celulares.
Drop Table if exists Participantes
go
Create table Participantes
(
ParticipantesCodigo nchar(5),
ParticipantesPaterno nvarchar(50) not null,
ParticipantesMaterno nvarchar(50) not null,
ParticipantesNombres nvarchar(50) not null,
ParticipantesCorreoHotmail nvarchar(50),
ParticipantesGmail nvarchar(50),
ParticipantesCorporativo nvarchar(50),
ParticipantesCelularPersonal nvarchar(50),
ParticipantesCelularCorporativo nvarchar(50),
constraint ParticipantesPK Primary key (ParticipantesCodigo)
)
go
Insert into Participantes values
( ‘P4562′,’Luque’,’Sanchez’,’Fernando’,NULL,Null,Null,Null, ‘987654321’),
( ‘P5852′,’Carranza’,’Mendoza’,’Manuel’,NULL,’mcarranza@gmail.com’,’mcarranza775@miempresa.com’,’963258741′, ‘951235746’),
( ‘P0235′,’Chavez’,’Alvarado’,’Ingrid’,’ichavez@hotmail.com’,’arachavez@gmail.com’,Null,Null, Null),
( ‘P8521′,’Sandoval’,’Palacios’,’Yeni’,’yenipal@hotmail.com’,Null,Null,’951357485′, ‘987654321’),
( ‘P6521′,’Terranova’,’Holguin’,’Marco’,’marcoth@hotmail.com’,Null,Null,’951357485′, ‘987654321’),
( ‘P1596′,’Villanueva’,’Llanos’,’Sergio’,Null,Null,’villaser@miempresa.com’,’987625426′, Null)
go

El listado sin el uso de Coalesce
Select P.ParticipantesCodigo As ‘Código’,
Participante = CONCAT_WS(space(1),P.ParticipantesNombres, P.ParticipantesPaterno, P.ParticipantesMaterno),
P.ParticipantesCorreoHotmail As ‘Hotmail’,
P.ParticipantesGmail As ‘Gmail’,
p.ParticipantesCorporativo As ‘Mail Corporativo’,
P.ParticipantesCelularPersonal As ‘Cel Personal’,
P.ParticipantesCelularCorporativo As ‘Cel Corporativo’
from Participantes As P
go
El resultado se muestra en la siguiente imagen

Usando Coalesce
Select
P.ParticipantesCodigo As ‘Código’,
Participante =
Concat_ws(Space(1),P.ParticipantesNombres, P.ParticipantesPaterno, P.ParticipantesMaterno),
Coalesce(ParticipantesCorreoHotmail, ParticipantesGmail, ParticipantesCorporativo, ‘No especificado’) As ‘Correo’,
Coalesce(ParticipantesCelularPersonal, ParticipantesCelularCorporativo, ‘No especificado’) As ‘Celular’
From Participantes As P
go
El resultado se muestra en la siguiente imagen

Ejercicio 5

En este ejercicio se va a utilizar Coalesce para concatenar las provincias de cada departamento de Perú
Drop table If exists Departamentos
go
Create table Departamentos
(
DepartamentosCodigo int,
DepartamentosNombre nvarchar(50),
DepartamentosCapital nvarchar(50),
constraint DepartamentosPK Primary key (DepartamentosCodigo)
)
go
Los departamentos
insert into Departamentos values (1,’Amazonas’,’Chachapoyas’)
insert into Departamentos values (2,’Áncash’,’Huaraz’)
insert into Departamentos values (3,’Apurímac’,’Abancay’)
insert into Departamentos values (4,’Arequipa’,’Arequipa’)
insert into Departamentos values (5,’Ayacucho’,’Ayacucho’)
insert into Departamentos values (6,’Cajamarca’,’Cajamarca’)
insert into Departamentos values (7,’Callao​’,’Callao’)
insert into Departamentos values (8,’Cusco’,’Cusco’)
insert into Departamentos values (9,’Huancavelica’,’Huancavelica’)
insert into Departamentos values (10,’Huánuco’,’Huánuco’)
insert into Departamentos values (11,’Ica’,’Ica’)
insert into Departamentos values (12,’Junín’,’Huancayo’)
insert into Departamentos values (13,’La Libertad’,’Trujillo’)
insert into Departamentos values (14,’Lambayeque’,’Chiclayo’)
insert into Departamentos values (15,’Lima’,’Lima’)
insert into Departamentos values (16,’Loreto’,’Iquitos’)
insert into Departamentos values (17,’Madre de Dios’,’Puerto Maldonado’)
insert into Departamentos values (18,’Moquegua’,’Moquegua’)
insert into Departamentos values (19,’Pasco’,’Cerro de Pasco’)
insert into Departamentos values (20,’Piura’,’Piura’)
insert into Departamentos values (21,’Puno’,’Puno’)
insert into Departamentos values (22,’San Martín’,’Moyobamba’)
insert into Departamentos values (23,’Tacna’,’Tacna’)
insert into Departamentos values (24,’Tumbes’,’Tumbes’)
insert into Departamentos values (25,’Ucayali’,’Pucallpa’)
go
Provincias, se van a insertar en una columna separadas por coma, luego se usará la función String_Split para presentarlas cada una en una fila.
Drop table if exists Provincias
go
Create table Provincias
(
DepartamentosCodigo int,
ProvinciasCantidad int,
ProvinciasNombres nvarchar(1000),
constraint ProvinciasPK primary key (DepartamentosCodigo),
constraint ProvinciasDepartamentosPK foreign key (DepartamentosCodigo)
references Departamentos(DepartamentosCodigo)
)
go
Las provincias
insert into Provincias values (1,7,’ Chachapoyas , Bagua , Bongará , Condorcanqui , Luya , Rodríguez de Mendoza , Utcubamba ‘)
insert into Provincias values (2,20,’ Aija , Antonio Raymondi , Asunción , Bolognesi , Carhuaz , Carlos F. Fitzcarrald , Casma , Corongo , Huaraz , Huari , Huarmey , Huaylas , Mariscal Luzuriaga , Ocros , Pallasca , Pomabamba , Recuay , Santa , Sihuas , Yungay’)
insert into Provincias values (3,7,’ Abancay , Antabamba , Aymaraes , Cotabambas , Grau , Chincheros , Andahuaylas’)
insert into Provincias values (4,8,’ Arequipa , Camaná , Caravelí , Castilla , Caylloma , Condesuyos , Islay , La Unión’)
insert into Provincias values (5,11,’ Cangallo , Huanta , Huamanga , Huanca Sancos , La Mar , Lucanas , Parinacochas , Páucar del Sara Sara , Sucre , Víctor Fajardo , Vilcashuamán’)
insert into Provincias values (6,13,’ Cajamarca , Cajabamba , Celendín , Chota , Contumazá , Cutervo , Hualgayoc , Jaén , San Ignacio , San Marcos , San Miguel , San Pablo , Santa Cruz’)
insert into Provincias values (7,1,’ Callao’)
insert into Provincias values (8,13,’ Cuzco , Acomayo , Anta , Calca , Canas , Canchis , Chumbivilcas , Espinar , La Convención , Paruro , Paucartambo , Quispicanchi , Urubamba’)
insert into Provincias values (9,7,’ Huancavelica , Acobamba , Angaraes , Castrovirreyna , Churcampa , Huaytará , Tayacaja’)
insert into Provincias values (10,11,’ Huánuco , Ambo , Dos de Mayo , Huacaybamba , Huamalíes , Leoncio Prado , Marañón , Pachitea , Puerto Inca , Lauricocha , Yarowilca’)
insert into Provincias values (11,5,’ Ica , Chincha , Nazca , Palpa , Pisco’)
insert into Provincias values (12,9,’ Chanchamayo , Chupaca , Concepción , Huancayo , Jauja , Junín , Satipo , Tarma , Yauli’)
insert into Provincias values (13,12,’ Ascope , Bolívar , Chepén , Gran Chimú , Julcán , Otuzco , Pacasmayo , Pataz , Sánchez Carrión , Santiago de Chuco , Trujillo , Virú’)
insert into Provincias values (14,3,’ Chiclayo , Ferreñafe , Lambayeque’)
insert into Provincias values (15,10,’ Barranca , Cajatambo , Canta , Cañete , Huaral , Huarochirí , Huaura , Lima , Oyón , Yauyos’)
insert into Provincias values (16,8,’ Putumayo , Alto Amazonas , Datem del Marañón , Loreto , Mariscal Ramón Castilla , Maynas , Requena , Ucayali’)
insert into Provincias values (17,3,’ Tambopata , Manu , Tahuamanu’)
insert into Provincias values (18,3,’ General Sánchez Cerro , Ilo , Mariscal Nieto’)
insert into Provincias values (19,3,’ Pasco , Daniel A. Carrión , Oxapampa’)
insert into Provincias values (20,8,’ Ayabaca , Huancabamba , Morropón , Paita , Piura , Sechura , Sullana , Talara’)
insert into Provincias values (21,13,’ Puno , Azángaro , Carabaya , Chucuito , El Collao , Huancané , Lampa , Melgar , San Antonio de Putina , San Román , Sandia , Yunguyo , Moho’)
insert into Provincias values (22,10,’ Bellavista , El Dorado , Huallaga , Lamas , Mariscal Cáceres , Moyobamba , Picota , Rioja , San Martín , Tocache’)
insert into Provincias values (23,4,’ Tacna , Candarave , Jorge Basadre , Tarata’)
insert into Provincias values (24,3,’ Tumbes , Zarumilla , Contralmirante Villar ‘)
insert into Provincias values (25,4,’ Coronel Portillo , Atalaya , Padre Abad , Purús’)
go
El listado de los departamentos y sus provincias.
select D.DepartamentosCodigo As ‘Código’, D.DepartamentosNombre As ‘Departamento’,
P.ProvinciasNombres As ‘Provincias’
from Departamentos As D
join Provincias As P on D.DepartamentosCodigo = P.DepartamentosCodigo
go
El resultado se muestra en la siguiente imagen

Crear una vista con el resultado

Create or alter view vistaDepartamentosProvincias
As
select D.DepartamentosCodigo As ‘Código’, D.DepartamentosNombre As ‘Departamento’,
P.ProvinciasNombres As ‘Provincias’
from Departamentos As D
join Provincias As P on D.DepartamentosCodigo = P.DepartamentosCodigo
go

Para poder usar Coalesce se va a crear una consulta separando las provincias en filas, para esto se usará la función String_Split (Ver Función String_Split)
select V.Código As ‘Código’,
V.Departamento As ‘Departamento’,
value As Provincia
from vistaDepartamentosProvincias As V
cross apply string_split(V.Provincias,’,’)
go
El resultado se muestra en la siguiente imagen

Usando Coalesce para filtrar las provincias de una departamento.
Las provincias del departamento de La Libertad.
Declare @Columna nvarchar(max)
Select @Columna = Coalesce(@Columna,») + »»+ D.Provincia+»»+ ‘,’
from (select V.Código As ‘Cód. Departamento’,
V.Departamento As ‘Departamento’,
value As Provincia
from vistaDepartamentosProvincias As V
cross apply string_split(V.Provincias,’,’)) As D
where D.Departamento = ‘La Libertad’
SELECT substring(@Columna,1,len(@Columna)-1)
go
El resultado se muestra en la siguiente imagen