COALESCE-toiminnon käyttäminen SQL Serverissä
Oletko koskaan törmännyt tilanteeseen, jossa sinun on käsiteltävä NULL-arvoja kyselyissäsi? Saatat esimerkiksi haluta näyttää oletusarvon, kun sarake on NULL, tai yhdistää useita sarakkeita yhdeksi ja valita ensimmäinen ei-NULL-arvo. Tässä blogiviestissä näytämme sinulle, kuinka voit käyttää COALESCE-toimintoa SQL Serverissä näiden ja muiden tehtävien suorittamiseen.
Mikä on COALESCE-toiminto?
COALESCE-funktio on SQL Serverin sisäänrakennettu funktio, joka arvioi lausekeluettelon ja palauttaa ensimmäisen, joka ei ole NULL. Jos kaikki lausekkeet ovat NULL-arvoja, funktio palauttaa NULL-arvon.
COALESCE-funktion syntaksi on:
COALESCE (expression1, expression2, ..., expressionN)
Lausekkeet voivat olla mitä tahansa tyyppiä, mutta niiden on oltava yhteensopivia keskenään. Tuloksen tietotyyppi määräytyy korkeimman tärkeysjärjestyksen omaavan lausekkeen mukaan.
COALESCE-toiminnon käyttäminen
Katsotaanpa joitain esimerkkejä COALESCE-toiminnon käyttämisestä SQL Serverissä.
Esimerkki 1: Näytä oletusarvo, kun sarake on NULL
Oletetaan, että meillä on taulukko nimeltä Products
, joka tallentaa tietoja joistakin tuotteista, kuten niiden nimen, hinnan ja kategorian. Joillekin tuotteille ei ole määritetty luokkaa, joten sarake Category
on NULL.
Jos haluamme näyttää tuotteet kategorioineen, mutta korvaamme NULL-arvot oletusarvolla, kuten Tuntematon, voimme käyttää COALESCE-toimintoa seuraavasti:
SELECT Name, Price, COALESCE(Category, 'Unknown') AS Category FROM Products;
Tulos on:
Kuten näet, COALESCE-funktio palauttaa arvon Category
, jos se ei ole NULL, muuten se palauttaa tuntemattoman.
Esimerkki 2: Yhdistä useita sarakkeita yhdeksi ja valitse ensimmäinen ei-NULL-arvo
Oletetaan, että meillä on toinen taulukko nimeltä Contacts
, joka tallentaa tietoja joistakin yhteystiedoista, kuten heidän nimensä, sähköpostiosoitteensa, puhelinnumeronsa ja osoitteensa. Joissakin yhteystiedoissa ei ole kaikkia näitä tietoja täytetty, joten jotkin sarakkeet ovat NULL-arvoja.
Jos haluamme näyttää yhteystiedot heidän haluamallaan yhteydenottotavalla, joka on ensimmäinen ei-NULL-arvo sähköpostin, puhelimen ja osoitteen joukossa, voimme käyttää COALESCE-toimintoa seuraavasti:
SELECT Name, COALESCE(Email, Phone, Address) AS PreferredContact FROM Contacts;
Tulos on:
Kuten näet, COALESCE-funktio palauttaa jokaisen yhteystiedon ensimmäisen ei-NULL-arvon sähköpostin, puhelimen ja osoitteen joukosta.
Kuinka hoitaa tämä vanhalla tavalla ja mitä COALESCE tarjoaa
Ennen kuin COALESCE-funktio otettiin käyttöön SQL Serverissä, meidän piti käyttää muita menetelmiä käsitelläksemme NULL-arvoja kyselyissämme. Voisimme käyttää esimerkiksi ISNULL-funktiota tai CASE-lauseketta.
ISNULL-funktio ottaa kaksi argumenttia ja palauttaa ensimmäisen, jos se ei ole NULL, muuten se palauttaa toisen. Esimerkiksi:
SELECT Name, ISNULL(Category, 'Unknown') AS Category FROM Products;
Tämä kysely tuottaa saman tuloksen kuin COALESCE:n käyttäminen kahdella argumentilla. ISNULL:n ja COALESCE:n välillä on kuitenkin joitain eroja:
- ISNULL hyväksyy vain kaksi argumenttia, kun taas COALESCE voi hyväksyä minkä tahansa määrän argumentteja.
- ISNULL käyttää tuloksen ensimmäisen argumentin tietotyyppiä, kun taas COALESCE noudattaa CASE-lausekesääntöjä ja palauttaa arvon tietotyypin, jolla on korkein prioriteetti.
- ISNULL arvioidaan vain kerran, kun taas COALESCE voidaan arvioida useita kertoja. Tämä voi vaikuttaa suorituskykyyn ja tulosten johdonmukaisuuteen, erityisesti käytettäessä alikyselyitä tai ei-deterministisiä funktioita.
CASE-lauseke on ehdollinen lauseke, jonka avulla voimme suorittaa erilaisia toimintoja eri ehtojen perusteella. Esimerkiksi:
SELECT Name, CASE WHEN Category IS NOT NULL THEN Category ELSE 'Unknown' END AS Category FROM Products;w
Tämä kysely tuottaa saman tuloksen kuin COALESCE:n käyttäminen kahdella argumentilla. CASE:n ja COALESCE:n välillä on kuitenkin joitain eroja:
- CASE on monimutkaisempi ja monimutkaisempi kuin COALESCE, varsinkin kun käsitellään useita ilmaisuja.
- CASE pystyy käsittelemään monimutkaisempaa logiikkaa ja ehtoja kuin COALESCE, joka tarkistaa vain NULL-arvot.
- CASE ja COALESCE voivat vaikuttaa erilaisiin suorituskykyyn kyselyn optimoijasta ja suoritussuunnitelmasta riippuen.
Kuten näet, COALESCE-toiminto tarjoaa yksinkertaisemman ja johdonmukaisemman tavan käsitellä NULL-arvoja SQL Serverissä. Se on myös joustavampi ja yhteensopivampi kuin ISNULL-funktio tai CASE-lauseke.
Johtopäätös
Tässä blogiviestissä opimme käyttämään SQL Serverin COALESCE-funktiota NULL-arvojen käsittelemiseen kyselyissämme. Näimme esimerkkejä oletusarvon näyttämisestä, kun sarake on NULL, tai yhdistää useita sarakkeita yhdeksi ja valita ensimmäinen ei-NULL-arvo. Vertasimme myös COALESCE-funktiota muihin menetelmiin, kuten ISNULL ja CASE, ja keskustelimme niiden eduista ja haitoista.