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.