Med teknologi som vokser og utvikler seg, er det viktig å holde seg oppdatert med de siste trendene som utvikler. Enten du er nybegynner eller ekspert, en solid forståelse av strengmanipulasjon hjelper deg med å forberede data (for eksempel generere en annen form enn den eksisterende, slik at den kan brukes for virksomheten din) og administrere dem ved hjelp av innebygde SQL-serverfunksjoner.
I tillegg til datamanipulering kan du undersøke datasett, evaluere dataverdier og kode eller dekode dem for å generere mer meningsfylte data. Som et resultat hjelper dette deg med å navigere gjennom manglende verdier i datasett, forstå deres innvirkning på beregninger og effektivisere det generelle arbeidet med dataprosessen for å unngå nullverdier som kan ødelegge driftsresultatene.
Denne guiden leder deg gjennom sammenslåingsfunksjonen i SQL, som hjelper deg med å bygge komplekse programmer. Innlegget forutsetter at du har støtt på og jobbet med SQL og bare ønsker å styrke forståelsen din av denne funksjonen. Vår serie med SQL-guider kan hjelpe deg å komme raskt i gang.
Innholdsfortegnelse
Hva er COALESCE () i SQL og dets bruk?
Koalesceringsfunksjonen i SQL evaluerer parametere (argumenter) i en spesifisert rekkefølge, som lister, og returnerer den første ikke-nullverdien. Enkelt sagt, funksjonen vurderer listen sekvensielt og avsluttes ved forekomsten av den første ikke-nullverdien. Hvis alle argumentene i listen er null, returnerer funksjonen NULL.
I tillegg er funksjonen inkluderende og støttes i andre databaser som MYSQL, Azure SQL Database, Oracle og PostgreSQL.
Du kan bruke Coalesce i følgende tilfeller når:
- Håndtering av NULL-verdier.
- Kjører flere spørringer som ett.
- Unngå lange, tidkrevende CASE-uttalelser.
Når den brukes i stedet for CASE-setninger (eller ISNULL-funksjonen), tar koalesce mange parametere, i motsetning til CASE, som bare tar to. Denne tilnærmingen lar deg skrive mindre kode og letter skriveprosessen.
Her er syntaksen:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Sammenslåing i SQL-serveren har flere egenskaper, inkludert argumenter av samme datatype, som aksepterer mange parametere, og argumenter av heltallstypen som skal kaskades av en avkastningsfunksjon for å returnere et heltall som utdata.
Les også: Ultimate SQL Cheat Sheet to Bookmark for Later
Men før vi går inn på hvordan du bruker coalesce, la oss forstå NULL.
Hva er en NULL-verdi i SQL?
Den unike markøren NULL i SQL indikerer at en verdi ikke eksisterer i databasen. Du kan tenke på det som en udefinert eller ukjent verdi. Vennligst ikke gå inn i fallgruven å tenke på det som en tom streng eller en nullverdi; det er fraværet av en verdi. Null-forekomst i tabellkolonner representerer manglende informasjon.
I et praktisk brukstilfelle kan datakolonnen i en databasekolonne for e-handelsnettsteder fylles med en NULL-verdi hvis en kunde ikke oppgir ID-en sin. Null i SQL er unikt; det er en tilstand, i motsetning til andre programmeringsspråk der det betyr «ikke peke på et bestemt objekt».
NULL-verdiene i SQL har en betydelig innvirkning på relasjonsdatabaser. For det første lar de deg ekskludere bestemte verdier mens du arbeider med andre interne funksjoner. Du kan for eksempel generere en liste over totale ordrer i et produksjonsmiljø, men andre må fortsatt fullføres. Ved å bruke NULL som plassholder kan den interne SUM-funksjonen legge til totalene.
Vurder i tillegg tilfeller der du trenger å generere gjennomsnittet ved hjelp av AVG-funksjonen. Hvis du jobber med nullverdier, blir resultatene skjeve. I stedet kan databasen fjerne slike felt og bruke NULL, noe som resulterer i nøyaktige utdata.
NULL-verdier er ikke med ulemper. De betraktes som verdier med variabel lengde, og er byte eller flere av dem. Siden databasen gir plass til disse bytene hvis de overskrider det som er lagret i databasen, er resultatet at databasen tar mer plass på harddisken i motsetning til å bruke vanlige verdier.
I tillegg, når du arbeider med enkelte funksjoner, må du tilpasse dem for å eliminere NULLER. Dette gjør SQL-prosedyrene dine lengre.
Håndtering av NULL-verdier med COALESCE ()
Nullverdier innebærer at du kan ha en verdi, men du er ikke klar over hva verdien skal være. Inntil du samler inn data som fyller feltene dine med reelle verdier, er NULL-verdier prokuratorene.
Selv om du kan bruke NULL-verdier for flere datatyper i databasen, inkludert desimaler, strenger, blobs og heltall, er det en god praksis å unngå dem når du arbeider med numeriske data.
Ulempen er at når den brukes for numeriske verdier, vil du sannsynligvis trenge avklaring når du utvikler koden som fungerer med data. Mer om det senere.
De forskjellige måtene COALESCE () kan brukes til å håndtere NULL-verdien:
Bruk av COALESCE () for å erstatte nullverdier med en spesifikk verdi
Du kan bruke COALESCE () for å returnere spesifikke verdier for alle nullverdier. For eksempel kan du ha en tabell kalt «ansatte» med kolonnen «lønn», som kan inneholde nullverdier hvis ansattes lønn ikke er kreditert. Så når du gjør noen beregninger, kan det være lurt å jobbe med en bestemt verdi, null i dette tilfellet, for alle NULL-oppføringer. Slik gjør du det.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Bruke COALESCE () for å velge den første ikke-nullverdien fra flere alternativer
Noen ganger kan det være lurt å jobbe med de første ikke-NULL-verdiene i en liste med uttrykk. I slike tilfeller har du ofte flere kolonner med relaterte data, og du vil prioritere deres verdier som ikke er NULL. Syntaksen forblir.
COALESCE (expression1, expression2, …)
I et praktisk tilfelle, anta at du har en kontakttabell med kolonnene foretrukket_navn og fullt_navn. Og du vil generere en liste over kontakter side ved side med deres foretrukne navn (hvis tilgjengelig) eller deres fulle navn. Her er hvordan du takler det.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Hvis foretrukket_navn ikke er NULL for dette testtilfellet, vil det bli returnert. Ellers returneres hele navnet som visningsnavn.
Stringsammenkobling med SQL Coalesce
Du kan støte på problemer med SQL når du setter sammen strenger hvis nullverdier er involvert. I slike tilfeller returneres NULL som et uønsket resultat. Nå som NULL ikke er vårt ønskede resultat, kan du fikse problemet ved å bruke koalesceringsfunksjonen. Nedenfor er et eksempel.
En enkel strengsammenkobling gjøres ved:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Koden returnerer:
Eksempel Hei, hvor er du, John?
Men hvis du bruker en NULL-verdi, som vist nedenfor:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
Utgangen er nå.
Siden hver tekststrengsammenkobling som involverer en NULL-verdi returnerer NULL, er utfallet ovenfor NULL. Problemet er imidlertid løst ved å bruke koalesceringen (). Ved å bruke denne funksjonen returnerer du en tom streng (eller et mellomrom) i stedet for NULL. Anta for eksempel at du fører bilnavn med produsentene deres; her er spørsmålet ditt.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Hvis produsenten er NULL, vil du ha «–» i stedet for NULL. Her er de forventede resultatene.
car_brandoutlander, produsent: —flying spurs, produsent: Bentleyroyal atlet, produsent: —royal saloon, produsent: Crown
Som du kan se, er NULL-resultatene eliminert, med muligheten til å sette inn erstatningsstrengverdien.
SQL Coalesce-funksjon og pivoting
SQL-pivoting er en teknikk som brukes for å transformere rader til kolonner. Den lar deg transponere (rotere) data fra den «normaliserte» formen (med mange rader og færre kolonner) til den «denormaliserte» (færre rader og flere kolonner). Koalesceringsfunksjonen kan brukes med SQL-pivoting for å håndtere nullverdier i pivoterte resultater.
Når du PIVOT i SQL, transformer rader til kolonner; resulterende kolonner er aggregerte funksjoner av enkelte data. Hvis, i alle fall, en aggregering resulterer i null for en bestemt celle, kan du bruke «COALESCE» for å erstatte nullverdiene med en standardverdi eller meningsfull representasjon. Nedenfor er et eksempel.
Vurder en tabell, salg, med kolonnene år, kvartal og inntekt, og du vil pivotere data; slik at du har år som kolonner og summen av inntekter for hvert kvartal som verdier. Men noen kvartaler har ingen inntektsdata, noe som gir nullverdier i det pivoterte resultatet. I dette tilfellet kan du bruke COALESCE til å erstatte nullverdier i det pivoterte resultatet med en null (0).
SELECT
year,
COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;
Skalær brukerdefinert funksjon og SQL-sammenslåingsfunksjon
Du kan bruke skalære UDF-er og koalesere for å utføre kompleks logikk som håndterer nullverdier. Å kombinere disse funksjonene vil hjelpe deg med å oppnå mer sofistikerte datatransformasjoner og beregninger i SQL-spørringer. Tenk på en tabell, ansatte, med denne strukturen.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Det kan være lurt å beregne hver ansatts totale inntjening (lønn pluss bonus). Det er imidlertid noen verdier som mangler. I dette tilfellet kan din skalar UDF håndtere tillegg av lønn og bonus, mens coalesce håndterer nullverdiene. Her er den skalære UDF for total inntekt.
CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
DECLARE @totalEarnings INT;
SET @totalEarnings = @salary + COALESCE(@bonus, 0);
RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;
Datavalidering ved hjelp av SQL Coalesce
Når du arbeider med databaser, kan det være lurt å validere numeriske verdier. La oss for eksempel si at du har kolonnene produktnavn, pris og rabatt i en tabell, produkter. Du ønsker å hente hver vares produktnavn, priser og rabatter. Men du vil gjerne behandle alle NULL rabattverdier som 0. Koalesceringsfunksjonen kan være nyttig. Slik bruker du den.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL Coalesce og Computed Columns
Beregnede kolonner er virtuelle kolonner som beregnes basert på uttrykk eller andre kolonner i en tabell. Fordi beregnede kolonner ikke lagres fysisk i databasen, kan du utnytte dem med koalesceringsfunksjonen når du håndterer komplekse scenarier og transformasjoner. Her er et praktisk brukseksempel.
Tenk på en «produkter»-tabell med kolonnene «pris», «rabatt» og «avgiftssats». I dette tilfellet vil du opprette en beregnet kolonne, «total_price», for å representere den endelige produktprisen etter å ha brukt rabatt og avgift. Hvis enten rabatt eller avgift ikke er spesifisert (NULL), vil du fortsette med beregningene ved å bruke en null. Her er hvordan du utnytter sammenslåing for å passe operasjonen.
CREATE TABLE products( price DECIMAL(10, 2), discount DECIMAL(10, 2), tax_rate DECIMAL(5, 2), total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) );
Her er hva som skjer i koden ovenfor.
Oppsettet ovenfor lar deg generere total_price, en beregnet kolonne, med den faktiske sluttprisen, til tross for manglende eller har NULL-verdier.
SQL Coalesce og CASE Expression
Du kan syntaktisk bruke coalesce gjennom CASE-uttrykket. Her er et eksempel:
SELECT Productname + ‘ ’+ deliverydate productdetails, dealer, CASE WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE ‘NA’ END EmergencyContactNumber FROM dbo.tb_EmergencyContact
I oppsettet ovenfor, spør CASE som COALESCE-funksjonen.
I tillegg er det mulig å bruke COALESCE- og CASE-uttrykkene i samme spørring. De to teknikkene kan håndtere NULL-verdier og bruke betinget logikk samtidig. La oss illustrere dette med et eksempel.
Tenk på et tilfelle der du har en tabell, produkter med kolonnene produkt-id, produktnavn, pris og rabatt. Noen av produktene dine har en spesifikk rabatt, mens andre ikke har det. Hvis et produkt har rabatt, ønsker du å vise den rabatterte prisen, ellers skal den vanlige vises.
SELECT
product_id,
product_name,
price,
COALESCE(
CASE
WHEN discount > 0 THEN price - (price * discount / 100)
ELSE NULL
END,
price
) AS discounted_price
FROM products;
I koden ovenfor sjekker «CASE» om «rabatten» er større enn null, og beregner rabattert pris, ellers returnerer den en NULL. `COALESCE`-funksjonen tar resultatet fra `CASE` og `price` som sine parametere. Den returnerer den første ikke-NULL-verdien, og returnerer effektivt den rabatterte prisen hvis tilgjengelig eller den vanlige prisen hvis det ikke er noen.
Siste ord
Dette innlegget har vist ulike måter å bruke «COALESCE»-funksjonen i databasespørringene dine. Ved å evaluere parametere i en spesifisert rekkefølge og returnere den første ikke-NULL-verdien, forenkler koalesceringsfunksjonen spørringer og gjør dem effektive.
Coalesce er en allsidig funksjon enten du håndterer nullverdier, strengsammenkobling, datapivotering, validering eller arbeider med beregnede kolonner. Ved å mestre koalesceringsfunksjonen kan utviklere navigere gjennom manglende data og lage feilfrie databasedesign. Husk å mestre teknikken; du kan trenge litt mer inngående øvelse.
Du kan nå sjekke ut hvordan du oppretter fremmednøkkelbegrensninger i SQL.