Förstå COALESCE ()-funktionen i SQL

Med teknik som växer och utvecklas är det viktigt att hålla sig uppdaterad med de senaste trenderna som utvecklare. Oavsett om du är nybörjare eller expert, en gedigen förståelse för strängmanipulation hjälper dig att förbereda data (till exempel generera en annan form än den befintliga, vilket gör den användbar för ditt företag) och hantera den med hjälp av inbyggda SQL-serverfunktioner.

Förutom datamanipulation kan du undersöka datamängder, utvärdera datavärden och koda eller avkoda dem för att driva mer meningsfull data. Som ett resultat hjälper detta dig att navigera genom saknade värden i datamängder, förstå deras inverkan på beräkningar och effektivisera det övergripande arbetet med dataprocessen för att undvika nollvärden som kan förstöra driftresultat.

Den här guiden leder dig genom sammansmältningsfunktionen i SQL, som hjälper dig att bygga komplexa program. Inlägget förutsätter att du har stött på och arbetat med SQL och bara vill förstärka din förståelse för just denna funktion. Vår serie SQL-guider kan hjälpa dig att snabbt komma igång.

Vad är COALESCE () i SQL och dess användningsområden?

Koalesceringsfunktionen i SQL utvärderar parametrar (argument) i en specificerad ordning, som listor, och returnerar det första icke-nullvärdet. Enkelt uttryckt bedömer funktionen din lista sekventiellt och avslutas vid tillfället av det första icke-nullvärdet. Om alla argument i listan är null, returnerar funktionen NULL.

Dessutom är funktionen inkluderande och stöds i andra databaser som MYSQL, Azure SQL Database, Oracle och PostgreSQL.

Du kan använda Coalesce i följande fall när:

  • Hanterar NULL-värden.
  • Kör flera frågor som en.
  • Undvik långa, tidskrävande CASE-uttalanden.

När det används i stället för CASE-satser (eller ISNULL-funktionen), tar koalesce många parametrar, till skillnad från CASE, som bara tar två. Detta tillvägagångssätt låter dig skriva mindre kod och underlättar skrivprocessen.

Här är syntaxen:

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

Koalesce i SQL-servern har flera egenskaper, inklusive argument av samma datatyp, som accepterar många parametrar, och argument av heltalstyp som ska kaskadkopplas av en avkastningsfunktion för att returnera ett heltal som utdata.

Läs också: Ultimate SQL Cheat Sheet to Bookmark for Later

Men innan vi går in på hur man använder coalesce, låt oss förstå NULL.

Vad är ett NULL-värde i SQL?

Den unika markören NULL i SQL indikerar att ett värde inte finns i databasen. Du kan se det som ett odefinierat eller okänt värde. Snälla gå inte i fallgropen att tänka på det som en tom sträng eller ett nollvärde; det är frånvaron av ett värde. Null förekomst i tabellkolumner representerar saknad information.

I ett praktiskt fall kan datakolumnen i en databaskolumn för e-handelswebbplatser fyllas med ett NULL-värde om en kund inte uppger sitt ID. Null i SQL är unikt; det är ett tillstånd, till skillnad från andra programmeringsspråk där det betyder ”att inte peka på ett visst objekt”.

NULL-värdena i SQL har en betydande inverkan på relationsdatabaser. För det första låter de dig utesluta särskilda värden medan du arbetar med andra interna funktioner. Du kan till exempel generera en lista över totala order i en produktionsmiljö, men andra måste fortfarande slutföras. Genom att använda NULL som platshållare kan den interna SUM-funktionen lägga till summorna.

Tänk också på fall då du behöver generera medelvärdet med AVG-funktionen. Om du arbetar med nollvärden blir resultaten snedställda. Istället kan databasen ta bort sådana fält och använda NULL, vilket resulterar i korrekta utdata.

NULL-värden är inte med nackdelar. De anses vara värden med variabel längd, som bytes eller flera av dem. Eftersom databasen lämnar utrymme för dessa bytes om de överskrider vad som lagras i databasen, blir resultatet att din databas tar mer plats på hårddisken i motsats till att använda vanliga värden.

När du arbetar med vissa funktioner måste du dessutom anpassa dem för att eliminera NULLS. Detta gör att dina SQL-procedurer blir längre.

Hantera NULL-värden med COALESCE ()

Nullvärden innebär att du kan ha ett värde, men du är inte medveten om vad värdet ska vara. Tills du samlar in data som fyller dina fält med verkliga värden, är NULL-värden åklagare.

Även om du kan använda NULL-värden för flera datatyper i din databas, inklusive decimaler, strängar, blobbar och heltal, är det en god praxis att undvika dem när du hanterar numeriska data.

Nackdelen är att när det används för numeriska värden, kommer du troligen att behöva förtydligas när du utvecklar koden som fungerar med data. Mer om det senare.

De olika sätten COALESCE () kan användas för att hantera NULL-värdet:

Använda COALESCE () för att ersätta nollvärden med ett specifikt värde

Du kan använda COALESCE () för att returnera specifika värden för alla nollvärden. Till exempel kan du ha en tabell som heter ”anställda” med kolumnen ”lön”, som kan innehålla nollvärden om anställdas lön inte har krediterats. Så när du gör vissa beräkningar kanske du vill arbeta med ett specifikt värde, noll i det här fallet, för alla NULL-poster. Så här gör du.

SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees;

Använd COALESCE () för att välja det första icke-nullvärdet från flera alternativ

Ibland kanske du vill arbeta med de första icke-NULL-värdena i en lista med uttryck. I sådana fall har du ofta flera kolumner med relaterade data, och du skulle vilja prioritera deras icke-NULL-värden. Syntaxen finns kvar.

COALESCE (expression1, expression2, …)

I ett praktiskt fall, anta att du har en kontakttabell med kolumnerna föredraget_namn och fullständigt_namn. Och du vill skapa en lista med kontakter sida vid sida med deras föredragna namn (om tillgängligt) eller deras fullständiga namn. Så här löser du det.

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

Om föredraget_namn inte är NULL för detta testfall kommer det att returneras. Annars returneras det fullständiga namnet som visningsnamn.

Strängkonkatenering med SQL Coalesce

Du kan stöta på problem med SQL när du sammanfogar strängar om null-värden är inblandade. I sådana fall returneras NULL som ett oönskat resultat. Nu när NULL inte är vårt önskade resultat kan du åtgärda problemet med hjälp av koalesce-funktionen. Nedan följer ett exempel.

En enkel strängsammansättning görs genom:

SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example

Koden returnerar:

ExempelHej, var är du, John?

Men om du använder ett NULL-värde, som visas nedan:

SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example

Utgången är nu.

Eftersom varje textsträngssammansättning som involverar ett NULL-värde returnerar NULL, är resultatet ovan NULL. Problemet löses dock med hjälp av koalesceren (). Med den här funktionen returnerar du en tom sträng (eller ett mellanslag) istället för NULL. Anta till exempel att du listar bilnamn med deras tillverkare; här är din fråga.

SELECT 
car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

Om tillverkaren är NULL, kommer du att ha ’–’ 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 kan se elimineras NULL-resultaten, med möjligheten att infoga ditt ersättningssträngvärde.

SQL Coalesce-funktion och pivotering

SQL-pivotering är en teknik som används för att omvandla rader till kolumner. Det låter dig transponera (rotera) data från den ”normaliserade” formen (med många rader och färre kolumner) till den ”denormaliserade” (färre rader och fler kolumner). Koalesceringsfunktionen kan användas med SQL-pivotering för att hantera nollvärden i pivoterade resultat.

När du PIVOT i SQL, omvandla rader till kolumner; resulterande kolumner är aggregerade funktioner för vissa data. Om, i vilket fall som helst, en aggregering resulterar i null för en viss cell, kan du använda `COALESCE` för att ersätta nollvärdena med ett standardvärde eller meningsfull representation. Nedan följer ett exempel.

Tänk på 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. Men vissa kvartal har inga intäktsdata, vilket ger nollvärden i det pivoterade resultatet. I det här fallet kan du använda COALESCE för att ersätta nollvärden i det pivoterade resultatet med en noll (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 användardefinierad funktion och SQL Coalesce-funktion

Du kan använda skalära UDF:er och koalescera för att utföra komplex logik som hanterar nollvärden. Att kombinera dessa funktioner hjälper dig att uppnå mer sofistikerade datatransformationer och beräkningar i SQL-frågor. Överväg en tabell, Anställda, med denna struktur.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

Du kanske vill beräkna varje anställds totala inkomst (lön plus bonus). Det saknas dock några värden. I det här fallet kan din skalära UDF hantera tilläggen av lön och bonus, medan coalesce hanterar nollvärdena. 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

När du arbetar med databaser kanske du vill validera numeriska värden. Låt oss till exempel säga att du har kolumnerna produktnamn, pris och rabatt i en tabell, produkter. Du vill hämta varje artikels produktnamn, priser och rabatter. Men du skulle vilja behandla alla NULL-rabattvärden som 0. Koalesceringsfunktionen kan vara till hjälp. Så här använder du den.

SELECT product_name, price, COALESCE(discount, 0) AS discount 
FROM products

SQL Coalesce och Computed Columns

Beräknade kolumner är virtuella kolumner som beräknas baserat på uttryck eller andra kolumner i en tabell. Eftersom beräknade kolumner inte lagras fysiskt i databasen kan du utnyttja dem med sammansmältningsfunktionen när du hanterar komplexa scenarier och transformationer. Här är ett praktiskt exempel.

Tänk på en ”produkter”-tabell med kolumnerna ”pris”, ”rabatt” och ”skattesats”. I det här fallet vill du skapa en beräknad kolumn, ”total_price”, för att representera det slutliga produktpriset efter applicering av rabatt och moms. Om antingen rabatt eller skatt inte anges (NULL), vill du fortsätta med dina beräkningar med en nolla. Så här utnyttjar du sammansmältning för att passa operationen.

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)
);

I koden ovan, här är vad som händer.

  • Den beräknade totala_priskolumnen definieras som (COALESCE(pris, 0) – COALESCE(pris*rabatt, 0))* COALESCE(1+tax_rate, 1).
  • Om priset är NULL, säkerställer COALESCE(pris*rabatt, 0) att det behandlas som 0.
  • Om rabatten är noll säkerställer COALESCE(pris*rabatt) att den behandlas som 0, och multiplikationen påverkar inte beräkningen.
  • Om tax_rate är NULL, säkerställer COALESCE(1 + tax_rate, 1) att den behandlas som 0, vilket betyder att ingen skatt tillämpas, och multiplikationen påverkar inte beräkningen.
  • Inställningen ovan låter dig generera total_price, en beräknad kolumn, med det faktiska slutpriset, trots att det saknas eller har NULL-värden.

    SQL Coalesce och CASE-uttryck

    Du kan syntaktiskt använda coalesce genom 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 inställning, CASE-frågor som COALESCE-funktionen.

    Dessutom är det möjligt att använda uttrycken COALESCE och CASE i samma fråga. De två teknikerna kan hantera NULL-värden och tillämpa villkorlig logik samtidigt. Låt oss illustrera detta med ett exempel.

    Tänk på ett fall där du har en tabell, produkter med kolumnerna produkt-id, produktnamn, pris och rabatt. Vissa av dina produkter har en specifik rabatt medan andra inte har det. Om en produkt har rabatt vill du visa det rabatterade priset, annars ska ordinarie visas.

    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 ovanstående kod kontrollerar ’CASE’ om ’rabatten’ är större än noll, och beräknar rabatterat pris, annars returnerar det en NULL. Funktionen `COALESCE` tar resultatet från `CASE` och `price` som sina parametrar. Den returnerar det första icke-NULL-värdet, vilket i praktiken returnerar det rabatterade priset om det är tillgängligt eller det ordinarie priset om det inte finns något.

    Slutord

    Det här inlägget har visat olika sätt att använda `COALESCE`-funktionen i dina databasfrågor. Genom att utvärdera parametrar i en specificerad ordning och returnera det första icke-NULL-värdet, förenklar koalesceringsfunktionen frågor och gör dem effektiva.

    Coalesce är en mångsidig funktion oavsett om du hanterar nollvärden, strängsammansättning, datapivotering, validering eller arbetar med beräknade kolumner. Genom att bemästra sammansmältningsfunktionen kan utvecklare navigera genom saknade data och skapa felfria databasdesigner. Kom ihåg att behärska tekniken; du kan behöva lite mer djupgående träning.

    Du kan nu kolla in hur man skapar begränsningar för främmande nyckel i SQL.