Kā SQL serverī izmantot funkciju COALESCE

Vai esat kādreiz saskāries ar situāciju, kad vaicājumos ir jāapstrādā NULL vērtības? Piemēram, iespējams, vēlēsities parādīt noklusējuma vērtību, ja kolonna ir NULL, vai apvienot vairākas kolonnas vienā un izvēlēties pirmo vērtību, kas nav NULL. Šajā emuāra ierakstā mēs jums parādīsim, kā izmantot COALESCE funkciju SQL Server, lai veiktu šos un citus uzdevumus.

Kas ir COALESCE funkcija?

Funkcija COALESCE ir SQL Server iebūvēta funkcija, kas novērtē izteiksmju sarakstu un atgriež pirmo, kas nav NULL. Ja visas izteiksmes ir NULL, tad funkcija atgriež NULL.

Funkcijas COALESCE sintakse ir:

COALESCE (expression1, expression2, ..., expressionN)

Izteicieni var būt jebkura veida, taču tiem jābūt savstarpēji saderīgiem. Rezultāta datu tipu nosaka izteiksme ar augstāko prioritāti.

Kā lietot funkciju COALESCE

Apskatīsim dažus piemērus, kā SQL serverī izmantot funkciju COALESCE.

1. piemērs. Parādiet noklusējuma vērtību, ja kolonna ir NULL

Pieņemsim, ka mums ir tabula ar nosaukumu Products, kurā tiek glabāta informācija par dažiem produktiem, piemēram, to nosaukums, cena un kategorija. Dažiem produktiem nav piešķirta kategorija, tāpēc sleja Category tiem ir NULL.

Ja mēs vēlamies parādīt produktus ar to kategorijām, bet aizstāt NULL vērtības ar noklusējuma vērtību, piemēram, “Nezināms”, mēs varam izmantot funkciju COALESCE šādi:

SELECT Name, Price, COALESCE(Category, 'Unknown') AS Category
FROM Products;

Rezultāts ir:

Kā redzat, funkcija COALESCE atgriež vērtību Category, ja ​​tā nav NULL, pretējā gadījumā tā atgriež “Nezināms”.

2. piemērs. Apvienojiet vairākas kolonnas vienā un izvēlieties pirmo vērtību, kas nav NULL

Pieņemsim, ka mums ir cita tabula ar nosaukumu Contacts, kurā tiek glabāta informācija par dažām kontaktpersonām, piemēram, viņu vārds, e-pasts, tālrunis un adrese. Dažām kontaktpersonām nav aizpildīta visa šī informācija, tāpēc dažas ailes tām ir NULL.

Ja mēs vēlamies parādīt kontaktpersonas ar vēlamo saziņas metodi, kas ir pirmā vērtība, kas nav NULL starp e-pastu, tālruni un adresi, mēs varam izmantot COALESCE funkciju šādi:

SELECT Name, COALESCE(Email, Phone, Address) AS PreferredContact
FROM Contacts;

Rezultāts ir:

Kā redzat, funkcija COALESCE katrai kontaktpersonai atgriež pirmo vērtību, kas nav NULL starp e-pastu, tālruni un adresi.

Kā to risināt vecajā veidā un ko nodrošina COALESCE

Pirms COALESCE funkcijas ieviešanas SQL Server mums bija jāizmanto citas metodes, lai mūsu vaicājumos apstrādātu NULL vērtības. Piemēram, mēs varētu izmantot funkciju ISNULL vai izteiksmi CASE.

Funkcija ISNULL izmanto divus argumentus un atgriež pirmo, ja tā nav NULL, pretējā gadījumā tā atgriež otro. Piemēram:

SELECT Name, ISNULL(Category, 'Unknown') AS Category
FROM Products;

Šis vaicājums rada tādu pašu rezultātu kā COALESCE izmantošana ar diviem argumentiem. Tomēr pastāv dažas atšķirības starp ISNULL un COALESCE:

  • ISNULL pieņem tikai divus argumentus, savukārt COALESCE var pieņemt jebkuru argumentu skaitu.
  • ISNULL rezultātam izmanto pirmā argumenta datu tipu, savukārt COALESCE ievēro CASE izteiksmes noteikumus un atgriež vērtības datu tipu ar augstāko prioritāti.
  • ISNULL tiek novērtēts tikai vienu reizi, savukārt COALESCE var novērtēt vairākas reizes. Tas var ietekmēt veiktspēju un rezultātu konsekvenci, īpaši, ja tiek izmantoti apakšvaicājumi vai nedeterministiskas funkcijas.

Izteiksme CASE ir nosacījuma izteiksme, kas ļauj veikt dažādas darbības, pamatojoties uz dažādiem nosacījumiem. Piemēram:

SELECT Name, CASE WHEN Category IS NOT NULL THEN Category ELSE 'Unknown' END AS Category
FROM Products;w

Šis vaicājums rada tādu pašu rezultātu kā COALESCE izmantošana ar diviem argumentiem. Tomēr starp CASE un COALESCE ir dažas atšķirības:

  • CASE ir daudz detalizētāks un sarežģītāks nekā COALESCE, it īpaši, ja runa ir par vairākiem izteicieniem.
  • CASE var apstrādāt sarežģītāku loģiku un nosacījumus nekā COALESCE, kas pārbauda tikai NULL vērtības.
  • CASE un COALESCE var ietekmēt veiktspēju atšķirīgi atkarībā no vaicājuma optimizētāja un izpildes plāna.

Kā redzat, funkcija COALESCE nodrošina vienkāršāku un konsekventāku veidu, kā apstrādāt NULL vērtības SQL Server. Tas ir arī elastīgāks un saderīgāks nekā funkcija ISNULL vai CASE izteiksme.

Secinājums

Šajā emuāra ziņojumā mēs uzzinājām, kā SQL Server izmantot funkciju COALESCE, lai mūsu vaicājumos apstrādātu NULL vērtības. Mēs redzējām dažus piemērus, kā parādīt noklusējuma vērtību, ja kolonna ir NULL, vai apvienot vairākas kolonnas vienā un izvēlēties pirmo vērtību, kas nav NULL. Mēs arī salīdzinājām COALESCE funkciju ar citām metodēm, piemēram, ISNULL un CASE, un apspriedām to priekšrocības un trūkumus.