I takt med att tekniken avancerar är det viktigt för utvecklare att hålla sig ajour med de senaste trenderna. Oavsett erfarenhetsnivå är en god förståelse för hantering av strängar avgörande. Det gör det möjligt att bearbeta data, exempelvis genom att omvandla den till användbara format för organisationen, och att hantera information effektivt med hjälp av SQL:s inbyggda funktioner.
Förutom datamanipulation kan man med stränghantering granska och utvärdera data, samt koda eller avkoda den för att skapa mer insiktsfull information. Detta underlättar hanteringen av saknade värden i datamängder, förståelsen av deras inverkan på beräkningar och effektiviseringen av dataprocessen, vilket minskar risken för nollvärden som kan påverka resultaten negativt.
Denna guide presenterar funktionen COALESCE i SQL, ett kraftfullt verktyg för att skapa avancerade program. Artikeln utgår ifrån att du har viss erfarenhet av SQL och vill fördjupa din kunskap om just denna funktion. Om du är nybörjare kan våra SQL-guider ge dig en snabbstart.
Vad är COALESCE() i SQL och hur används den?
SQL-funktionen COALESCE utvärderar argument (parametrar) i en angiven ordning, som i en lista, och returnerar det första värdet som inte är NULL. Enkelt förklarat går funktionen igenom listan stegvis och avslutas så snart den hittar ett icke-NULL-värde. Om samtliga argument i listan är NULL, returnerar funktionen NULL.
Funktionen är allmänt tillgänglig och stöds i andra databassystem såsom MySQL, Azure SQL Database, Oracle och PostgreSQL.
COALESCE kan användas i följande fall:
- Hantering av NULL-värden.
- Utförande av flera frågor som en enhet.
- Undvikande av långa och komplicerade CASE-uttryck.
Till skillnad från CASE (eller ISNULL-funktionen) kan COALESCE hantera flera parametrar, vilket leder till kortare och mer lättläst kod.
Syntaxen ser ut så här:
COALESCE(värdeEtt, värdeTvå, värdeTre, …, värdeX);
I SQL Server har COALESCE flera egenskaper. Den accepterar argument av samma datatyp, kan hantera många parametrar och kan kaskadkopplas med heltalstyp för att returnera ett heltal som resultat.
Läs även: Ultimativ SQL-fusklapp.
Innan vi går djupare in på användningen av COALESCE, låt oss undersöka vad NULL innebär.
Vad är ett NULL-värde i SQL?
I SQL används markören NULL för att ange att ett värde saknas i databasen. Det kan betraktas som ett odefinierat eller okänt värde. Det är viktigt att inte förväxla det med en tom sträng eller noll; det representerar frånvaron av ett värde. NULL-förekomster i tabellkolumner indikerar information som saknas.
Ett praktiskt exempel kan vara en e-handelswebbplats där en kund inte angett sitt ID, vilket resulterar i ett NULL-värde i databaskolumnen för det ID:t. NULL i SQL är unikt; det är ett tillstånd snarare än, som i många andra programmeringsspråk, ”att inte peka på ett objekt”.
NULL-värden har en betydande inverkan på relationsdatabaser. För det första möjliggör de uteslutning av specifika värden vid arbete med interna funktioner. Till exempel kan du generera en lista över totala order, där vissa order fortfarande är under behandling. Genom att använda NULL som platshållare kan SUM-funktionen hantera detta korrekt.
Tänk också på scenarier där du behöver generera medelvärdet med AVG-funktionen. Om du arbetar med nollvärden kommer resultaten att bli felaktiga. Genom att databasen kan ignorera dessa fält och istället använda NULL, erhålls korrekta resultat.
NULL-värden har dock nackdelar. De ses som värden med variabel längd, motsvarande bytes eller flera bytes. Om utrymmet som reserveras för dessa bytes överstiger vad som lagras i databasen, kan detta leda till att databasen tar upp mer utrymme på hårddisken.
Dessutom, vid användning av vissa funktioner, kan det krävas anpassning för att exkludera NULL-värden, vilket kan göra SQL-procedurer längre.
Hantering av NULL-värden med COALESCE()
NULL-värden uppstår när information saknas, och du är omedveten om vad det faktiska värdet ska vara. Dessa värden kan vara problematiska tills du fyller i dem med korrekta data.
Även om NULL-värden kan användas för olika datatyper, inklusive decimaltal, strängar och heltal, är det god praxis att undvika dem vid hantering av numeriska data.
Nackdelen med numeriska NULL-värden är att de ofta kräver specialhantering vid utveckling av kod som använder dessa data.
Här följer några exempel på hur COALESCE() kan användas för att hantera NULL-värden:
Ersätta NULL-värden med ett specifikt värde
COALESCE() kan användas för att ersätta alla NULL-värden med ett specificerat värde. Exempelvis, om tabellen ”anställda” har en kolumn ”lön” som kan innehålla NULL-värden om en anställds lön inte har betalats ut, kan du ersätta NULL-värden med noll för beräkningar.
SELECT COALESCE(lön, 0) AS justerad_lön FROM anställda;
Välja det första icke-NULL-värdet från flera alternativ
Ibland är det önskvärt att välja det första icke-NULL-värdet från en lista med uttryck. Det kan handla om kolumner med relaterade data, där du vill prioritera deras icke-NULL-värden. Syntaxen är densamma.
COALESCE (uttryck1, uttryck2, …)
Ett praktiskt exempel är en kontakttabell med kolumnerna ”föredraget_namn” och ”fullständigt_namn”. Om du vill visa en lista med kontakter med deras föredragna namn (om tillgängligt), annars med deras fullständiga namn, kan du använda följande:
SELECT COALESCE(föredraget_namn, fullständigt_namn) AS visningsnamn FROM kontakter.
Om ”föredraget_namn” inte är NULL kommer detta namn att användas. Annars visas det fullständiga namnet som visningsnamn.
Strängsammanfogning med SQL COALESCE
Vid sammanfogning av strängar i SQL kan NULL-värden leda till oönskade resultat. I sådana fall returneras ofta NULL. Detta problem kan lösas med hjälp av COALESCE-funktionen. Här är ett exempel.
En enkel strängsammanfogning ser ut så här:
SELECT 'Hej, var är du, '|| 'John '||'?' AS exempel
Koden returnerar:
ExempelHej, var är du, John?
Men om du använder ett NULL-värde, som i exemplet nedan:
SELECT 'Hej, var är du, ' || null || '?' AS exempel
Utdata blir nu:
NULL
Eftersom varje textsträngssammansättning med ett NULL-värde returnerar NULL, är resultatet ovan NULL. COALESCE() löser detta genom att returnera en tom sträng eller ett mellanslag istället för NULL. Exempelvis, om du listar bilnamn med deras tillverkare kan du använda följande fråga:
SELECT car || ', manufacturer: ' || COALESCE(manufacturer, '—') AS car_brand FROM stock
Om tillverkaren är NULL, kommer ’—’ att visas istället för NULL. Här är de förväntade resultaten.
car_brandoutlander, tillverkare: —flygande sporrar, tillverkare: Bentleyroyal atlet, tillverkare: —royal saloon, tillverkare: Crown
Som du ser elimineras NULL-resultaten, och du har möjlighet att infoga ditt eget ersättningsvärde.
SQL COALESCE-funktion och pivotering
SQL-pivotering används för att omvandla rader till kolumner, vilket transformerar data från en ”normaliserad” form med många rader och få kolumner till en ”denormaliserad” form med färre rader och fler kolumner. COALESCE kan användas för att hantera NULL-värden i det pivoterade resultatet.
När du använder PIVOT i SQL, och kolumnerna är aggregerade funktioner för viss data, kan en aggregering ge NULL för en viss cell. I detta fall kan du använda `COALESCE` för att ersätta NULL-värden med ett standardvärde eller någon annan lämplig representation. Här är ett exempel.
Om du har en tabell ”försäljning” med kolumnerna år, kvartal och intäkter, och du vill pivotera data så att du har år som kolumner och summan av intäkter för varje kvartal som värden, kan NULL-värden uppstå i det pivoterade resultatet om ett kvartal saknar intäktsdata. COALESCE kan användas för att hantera dessa NULL-värden.
SELECT
year,
COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Intäkt,
COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Intäkt,
COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Intäkt,
COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Intäkt
FROM sales
GROUP BY year;
Skalär användardefinierad funktion och SQL COALESCE-funktion
Skalära användardefinierade funktioner (UDF) och COALESCE kan kombineras för att utföra komplex logik som hanterar NULL-värden, vilket möjliggör mer avancerade datatransformationer och beräkningar i SQL-frågor. Ta till exempel tabellen ”Anställda” med följande struktur:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
För att beräkna varje anställds totala inkomst (lön plus bonus), kan skalär UDF hantera additionen, medan COALESCE hanterar NULL-värden. Här är den skalära UDF för totala intäkter.
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 med SQL COALESCE
Vid hantering av databaser kan validering av numeriska värden vara nödvändigt. I tabellen ”produkter”, som innehåller kolumnerna ”produktnamn”, ”pris” och ”rabatt”, kan COALESCE användas för att hantera alla NULL-rabattvärden och behandla dem som 0.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL COALESCE och Beräknade Kolumner
Beräknade kolumner är virtuella kolumner vars värden beräknas baserat på uttryck eller andra kolumner i en tabell. Dessa kolumner lagras inte fysiskt i databasen, men kan användas tillsammans med COALESCE för komplexa scenarier och transformationer.
I tabellen ”produkter”, som innehåller kolumnerna ”pris”, ”rabatt” och ”skattesats”, kan en beräknad kolumn ”total_pris” skapas för att representera det slutliga priset efter tillämpning av rabatt och moms. Om rabatt eller skatt är NULL kan COALESCE användas för att fortsätta beräkningarna genom att ersätta NULL med noll.
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) );
Här är vad koden gör:
Denna inställning gör det möjligt att generera ”total_pris”, trots att information saknas.
SQL COALESCE och CASE-uttryck
COALESCE kan användas syntaktiskt via CASE-uttrycket. Här är ett exempel:
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 ovanstående scenario fungerar CASE-frågan som COALESCE-funktionen.
Det är även möjligt att använda COALESCE och CASE i samma fråga. De två teknikerna kan hantera NULL-värden och villkorlig logik samtidigt. Låt oss belysa detta med ett exempel.
Om tabellen ”produkter” har kolumnerna produkt_id, produktnamn, pris och rabatt, kan du med hjälp av CASE och COALESCE visa det rabatterade priset om rabatt finns, annars visa det ordinarie priset.
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 ovan kontrollerar `CASE` om ”rabatt” är större än noll och beräknar det rabatterade priset, annars returneras NULL. Funktionen `COALESCE` tar resultatet från `CASE` och `price` som sina parametrar och returnerar det första icke-NULL-värdet, vilket ger det rabatterade priset om det finns eller det ordinarie priset om det inte finns någon rabatt.
Slutord
I den här artikeln har vi undersökt olika sätt att använda funktionen `COALESCE` i SQL. Genom att utvärdera parametrar i en specificerad ordning och returnera det första icke-NULL-värdet, förenklar och effektiviserar COALESCE frågor.
COALESCE är en mångsidig funktion som kan hantera NULL-värden, strängsammanfogning, datapivotering, validering eller arbete med beräknade kolumner. Genom att behärska COALESCE kan utvecklare hantera saknad information och skapa tillförlitliga databasdesigner. Det kan krävas ytterligare träning för att fullt ut behärska tekniken.
Du kan nu undersöka hur du skapar begränsningar för sekundärnycklar i SQL.