De meeste rapporten hebben enige logica nodig: markeer bestellingen als groot of klein, tel betaalde en openstaande bestellingen naast elkaar, of toon dringende artikelen eerst. SQL CASE WHEN verwerkt dit allemaal rechtstreeks in een query en werkt in alle grote databases.

We hebben elke query hier in SQLite 3.50 uitgevoerd en de weergegeven resultaten zijn afkomstig van die run. CASE maakt deel uit van standaard SQL, dus het gedraagt ​​zich voor deze voorbeelden op dezelfde manier in PostgreSQL, MySQL, SQL Server en Oracle.

Wat is GEVAL WANNEER?

CASE is een expressie, geen afzonderlijk commando. Het evalueert de voorwaarden op volgorde en retourneert een waarde, zodat het overal terecht kan waar een waarde is toegestaan: in SELECT, WHERE, GROUP BY, ORDER BY of binnen functies. Het eindigt altijd met END.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

De voorbeeldtafel

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Merk op dat bestelling 6 geen totaal heeft. Die rij is later van belang.

CASE gezocht

Dit is de meer flexibele vorm, aangezien elke WHEN een volledige eigen voorwaarde heeft:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Kijk naar de laatste rij. Bestelling 6 heeft geen totaal, maar is wel ‘klein’. Beide vergelijkingen met NULL waren niet waar, dus ging het niet door naar ELSE. In de NULL-sectie laten we zien hoe u dit kunt oplossen. Geef het resultaat altijd een naam met behulp van AS, anders krijgt u mogelijk een lastige kolomnaam.

Eenvoudig GEVAL

Wanneer u één kolom met vaste waarden vergelijkt, leest de kortere vorm beter:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

Orde is belangrijk

CASE stopt bij de eerste echte voorwaarde, en dit is de meest voorkomende logische bug. Verwissel de twee voorwaarden uit onze eerdere vraag:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Nu kan niets ooit ‘groot’ zijn. Een totaal van 2500 is ook minimaal 500, dus de eerste tak wint en de tweede wordt nooit bereikt. Er verschijnt geen fout, alleen verkeerde labels. Voor bereiken plaatst u de meest beperkende voorwaarde eerst, of schrijft u volledige bereiken, zoals total >= 500 AND total < 1000.

Wat gebeurt er zonder ANDERS?

In ons eenvoudige CASE-voorbeeld hierboven werd bestelling 5 geannuleerd en werd dat door geen enkel filiaal afgehandeld, dus het resultaat was NULL. Soms is dat precies wat je wilt, zoals de volgende sectie laat zien. Maar bij het maken van labels is een duidelijke ELSE 'other' meestal beter, omdat hierdoor onverwachte waarden zichtbaar worden in plaats van dat er lege plekken in een rapport achterblijven.

Rapporten in draaistijl met SUM

Dit is een van de nuttigste patronen in de dagelijkse SQL. Door CASE in een aggregatie te plaatsen, worden rijen in kolommen omgezet, met één rij per klant:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

Eén zoekopdracht geeft nu zowel de betaalde omzet als het aantal lopende bestellingen weer, zonder dat er afzonderlijke zoekopdrachten of joins nodig zijn. Het patroon werkt in bijna elke database, in tegenstelling tot de speciale PIVOT syntaxis die slechts enkele bieden. Als GROUP BY nieuw voor je is, lees dan SQL GROUP BY en HAVING.

Voorwaardelijke tellingen

COUNT slaat NULL’s over, dus een CASE zonder ELSE telt alleen de rijen die overeenkomen:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Let op: als je ELSE 0 binnen een COUNT optelt, wordt elke rij geteld, omdat 0 niet NULL is. Gebruik ELSE 0 bij SUM en laat het weg bij COUNT. PostgreSQL en SQLite ondersteunen ook COUNT(*) FILTER (WHERE ...), wat prettig leest, maar niet overal beschikbaar is.

Groeperen op basis van een CASE

U kunt uw eigen buckets maken en deze vervolgens samenvatten:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Groeperen op alias werkt in SQLite, PostgreSQL en MySQL. SQL Server staat dit niet toe, dus u zou de hele expressie moeten herhalen, of de query eerst in een CTE of subquery moeten plaatsen.

Aangepaste sortering met ORDER BY

Alfabetische volgorde is zelden wat een bedrijf wil. Met CASE kunt u uw eigen volgorde definiëren:

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Openstaande bestellingen komen op de eerste plaats, wat handig is voor een werkwachtrij. De tweede sorteerkolom, id, houdt de rijen binnen elke groep in een voorspelbare volgorde.

CASE in UPDATE

Met CASE kan één instructie verschillende wijzigingen op verschillende rijen toepassen. Hier worden kleine lopende bestellingen gemarkeerd ter beoordeling:

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

De ELSE status houdt andere rijen ongewijzigd. Het is gevaarlijk om dit weg te laten, omdat niet-overeenkomende rijen op NULL worden ingesteld. Zonder een WHERE-clausule heeft deze verklaring ook betrekking op elke rij, dus voeg bij grote tabellen een filter toe en overweeg de wijziging in een transactie te verpakken, zodat u het resultaat kunt controleren voordat u een commit uitvoert. Merk ook op dat volgorde 6 niet veranderde, omdat het NULL-totaal niet voldeed aan total < 500.

NULL vallen

NULL veroorzaakt twee afzonderlijke problemen met CASE. Ten eerste vergelijkt de eenvoudige vorm met behulp van gelijkheid, en niets is gelijk aan NULL, zelfs niet aan NULL zelf:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

Dus CASE col WHEN NULL komt nooit overeen. Gebruik in plaats daarvan het gezochte formulier met IS NULL. Ten tweede, zoals we bij bestelling 6 hebben gezien, glijdt een NULL-waarde voorbij vergelijkingen en belandt in ELSE. De oplossing is om er eerst naar te kijken:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Bestel 6 krijgt nu een eerlijk label. Onze gids voor SQL NULL-waarden behandelt dit gedrag volledig.

CASE versus andere opties

Die laatste optie verdient het om over na te denken. Als een CASE uitgroeit tot dertig vertakkingen en in vijf query’s voorkomt, verplaatst u de mapping naar een tabel en voegt daaraan toe. Dan is een wijziging een update van één rij, in plaats van een codewijziging op vijf plaatsen.

Veel voorkomende fouten

• Verkeerde voorwaardevolgorde. Een brede voorwaarde verbergt eerst de smallere voorwaarden.
• END vergeten. Dit is de meest voorkomende syntaxisfout.
• Gebruik WHEN NULL. Het komt nooit overeen, dus gebruik WHEN col IS NULL.
• ELSE 0 toevoegen binnen COUNT. Het telt alle rijen.
• We laten ELSE weg in een UPDATE. Het kan waarden met NULL wissen.
• Filteren op CASE in WHERE. Het kan voorkomen dat een index wordt gebruikt. Zie onze gids voor SQL-queryoptimalisatie.

Tot slot

CASE WHEN is een kleine functie die veel werk doet. Onthoud drie regels: de eerste echte tak wint, ongeëvenaarde rijen worden NULL zonder een ELSE, en NULL heeft zijn eigen controle nodig. Om te oefenen kunt u een rapport nemen dat u momenteel met meerdere query’s maakt, en proberen dit als één query te produceren met behulp van SUM(CASE ...).

Veelgestelde vragen

Blijf leren