NULL functies#
%LOAD ../data/employees.db
Als de waarde NULL is, is de inhoud onbeschikbaar, niet bekend of niet van toepassing. NULL is niet hetzelfde als 0 of een spatie, want een 0 is een getal en een spatie is een karakter. Maar soms weet je de inhoud van een kolom niet. We zeggen dan dat de inhoud NULL is.
Als je vermenigvuldigt met NULL of deelt door NULL, dan is het resultaat NULL. Je kunt dus wel delen door NULL maar niet door 0 want delen door 0 is verboden.
Soms wil je NULL vervangen door een andere waarde, bijvoorbeeld als je wil rekenen met de cellen die NULL zijn of als je met een bepaalde tekst aan wil geven dat de inhoud NULL is. Je kunt dan met een functie NULL vervangen door een andere inhoud, bijvoorbeeld door 0 of door een bepaalde tekst.
Hier zijn twee verschillende functies voor te gebruiken: IFNULL en COALESCE.
Ifnull#
Er zijn maar vier personeelsleden die een commission_pct hebben (commissie krijgen). Commissie is een bepaald percentage dat je extra krijgt boven op je loon. Dat percentage kan gaan over de verkopen die je hebt gedaan of gewoon als percentage boven op je loon. Hoe meer je dan verkoopt, hoe hoger je commissiepercentage dan kan worden.
SELECT last_name, commission_pct AS commissie
FROM employees;
| last_name | commissie |
|---|---|
| King | |
| Kochhar | |
| De Haan | |
| Hunold | |
| Ernst | |
| Lorentz | |
| Mourgos | |
| Rajs | |
| Davies | |
| Matos | |
| Vargas | |
| Zlotkey | 0.2 |
| Abel | 0.3 |
| Taylor | 0.2 |
| Grant | 0.15 |
| Whalen | |
| Hartstein | |
| Fay | |
| Higgins | |
| Gietz |
SELECT last_name, IFNULL(commission_pct, 'geen') AS commissie
FROM employees;
| last_name | commissie |
|---|---|
| King | geen |
| Kochhar | geen |
| De Haan | geen |
| Hunold | geen |
| Ernst | geen |
| Lorentz | geen |
| Mourgos | geen |
| Rajs | geen |
| Davies | geen |
| Matos | geen |
| Vargas | geen |
| Zlotkey | 0.2 |
| Abel | 0.3 |
| Taylor | 0.2 |
| Grant | 0.15 |
| Whalen | geen |
| Hartstein | geen |
| Fay | geen |
| Higgins | geen |
| Gietz | geen |
Als je wil rekenen met de waarden in een kolom die NULL kunnen zijn, dan gaat het mis met de berekeningen. Als je met NULL rekent, is het resultaat namelijk NULL.
SELECT last_name
, salary
, commission_pct AS commissiepercentage
, salary * commission_pct AS commissie
, salary + salary * commission_pct AS totaalsalaris
FROM employees;
| last_name | salary | commissiepercentage | commissie | totaalsalaris |
|---|---|---|---|---|
| King | 24000 | |||
| Kochhar | 17000 | |||
| De Haan | 17000 | |||
| Hunold | 9000 | |||
| Ernst | 6000 | |||
| Lorentz | 4200 | |||
| Mourgos | 5800 | |||
| Rajs | 3500 | |||
| Davies | 3100 | |||
| Matos | 2600 | |||
| Vargas | 2500 | |||
| Zlotkey | 10500 | 0.2 | 2100.0 | 12600.0 |
| Abel | 11000 | 0.3 | 3300.0 | 14300.0 |
| Taylor | 8600 | 0.2 | 1720.0 | 10320.0 |
| Grant | 7000 | 0.15 | 1050.0 | 8050.0 |
| Whalen | 4400 | |||
| Hartstein | 13000 | |||
| Fay | 6000 | |||
| Higgins | 12000 | |||
| Gietz | 8300 |
Als je NULL vervangt door 0 in de kolom commission_pct gaan de berekeningen wel goed.
SELECT last_name
, salary AS salaris
, IFNULL(commission_pct, 0) AS commissiepercentage
, salary * IFNULL(commission_pct, 0) AS commissie
, ROUND(salary + salary * IFNULL(commission_pct, 0)) AS totaalsalaris
FROM employees;
| last_name | salaris | commissiepercentage | commissie | totaalsalaris |
|---|---|---|---|---|
| King | 24000 | 0 | 0 | 24000.0 |
| Kochhar | 17000 | 0 | 0 | 17000.0 |
| De Haan | 17000 | 0 | 0 | 17000.0 |
| Hunold | 9000 | 0 | 0 | 9000.0 |
| Ernst | 6000 | 0 | 0 | 6000.0 |
| Lorentz | 4200 | 0 | 0 | 4200.0 |
| Mourgos | 5800 | 0 | 0 | 5800.0 |
| Rajs | 3500 | 0 | 0 | 3500.0 |
| Davies | 3100 | 0 | 0 | 3100.0 |
| Matos | 2600 | 0 | 0 | 2600.0 |
| Vargas | 2500 | 0 | 0 | 2500.0 |
| Zlotkey | 10500 | 0.2 | 2100.0 | 12600.0 |
| Abel | 11000 | 0.3 | 3300.0 | 14300.0 |
| Taylor | 8600 | 0.2 | 1720.0 | 10320.0 |
| Grant | 7000 | 0.15 | 1050.0 | 8050.0 |
| Whalen | 4400 | 0 | 0 | 4400.0 |
| Hartstein | 13000 | 0 | 0 | 13000.0 |
| Fay | 6000 | 0 | 0 | 6000.0 |
| Higgins | 12000 | 0 | 0 | 12000.0 |
| Gietz | 8300 | 0 | 0 | 8300.0 |
Tenslotte zetten we nog een procentteken en enkele dollartekens met CONCAT.
SELECT last_name
, ('$ ' || salary) AS basissalaris
, (IFNULL(commission_pct, 0) || ' %') AS commissiepercentage
, ('$ ' || (salary * IFNULL(commission_pct, 0))) AS commissie
, ('$ '|| ROUND(salary + salary * IFNULL(commission_pct, 0))) AS totaalsalaris
FROM employees;
| last_name | basissalaris | commissiepercentage | commissie | totaalsalaris |
|---|---|---|---|---|
| King | $ 24000 | 0 % | $ 0 | $ 24000.0 |
| Kochhar | $ 17000 | 0 % | $ 0 | $ 17000.0 |
| De Haan | $ 17000 | 0 % | $ 0 | $ 17000.0 |
| Hunold | $ 9000 | 0 % | $ 0 | $ 9000.0 |
| Ernst | $ 6000 | 0 % | $ 0 | $ 6000.0 |
| Lorentz | $ 4200 | 0 % | $ 0 | $ 4200.0 |
| Mourgos | $ 5800 | 0 % | $ 0 | $ 5800.0 |
| Rajs | $ 3500 | 0 % | $ 0 | $ 3500.0 |
| Davies | $ 3100 | 0 % | $ 0 | $ 3100.0 |
| Matos | $ 2600 | 0 % | $ 0 | $ 2600.0 |
| Vargas | $ 2500 | 0 % | $ 0 | $ 2500.0 |
| Zlotkey | $ 10500 | 0.2 % | $ 2100.0 | $ 12600.0 |
| Abel | $ 11000 | 0.3 % | $ 3300.0 | $ 14300.0 |
| Taylor | $ 8600 | 0.2 % | $ 1720.0 | $ 10320.0 |
| Grant | $ 7000 | 0.15 % | $ 1050.0 | $ 8050.0 |
| Whalen | $ 4400 | 0 % | $ 0 | $ 4400.0 |
| Hartstein | $ 13000 | 0 % | $ 0 | $ 13000.0 |
| Fay | $ 6000 | 0 % | $ 0 | $ 6000.0 |
| Higgins | $ 12000 | 0 % | $ 0 | $ 12000.0 |
| Gietz | $ 8300 | 0 % | $ 0 | $ 8300.0 |
Te doen
Eerder introduceren van de vooroplopende komma-notatie.
Verwijderen, omdat we deze notatie al eerder introduceren:
Om de query overzichtelijk te houden, wordt soms de volgende notatie gehanteerd:
SELECT last_name
, CONCAT('$ ',salary) AS basissalaris
, CONCAT(IFNULL(commission_pct, 0), ' %') AS commissiepercentage
, CONCAT('$ ', salary * IFNULL(commission_pct, 0)) AS commissie
, CONCAT('$ ', ROUND(salary + salary * IFNULL(commission_pct, 0))) AS totaalsalaris
FROM employees;
Coalesce#
COALESCE kan hetzelfde als IFNULL.
SELECT last_name, COALESCE(commission_pct, 'geen') AS commissie
FROM employees;
| last_name | commissie |
|---|---|
| King | geen |
| Kochhar | geen |
| De Haan | geen |
| Hunold | geen |
| Ernst | geen |
| Lorentz | geen |
| Mourgos | geen |
| Rajs | geen |
| Davies | geen |
| Matos | geen |
| Vargas | geen |
| Zlotkey | 0.2 |
| Abel | 0.3 |
| Taylor | 0.2 |
| Grant | 0.15 |
| Whalen | geen |
| Hartstein | geen |
| Fay | geen |
| Higgins | geen |
| Gietz | geen |
Maar COALESCE kan nog net iets meer. Bekijk onderstaand resultaat. Bij COALESCE geef je een rijtje op dat gecontroleerd moet worden. Het gaat er om wat er in de laatste kolom getoond moet worden. Als de eerste kolom (bonus) inhoud heeft, toon je die inhoud in de laatste kolom. Als de eerste kolom NULL is, kijk je naar de tweede kolom (department_id). Als die inhoud heeft, toon je die inhoud in de laatste kolom. Als zowel de eerste (bonus) als de tweede kolom (department_id) NULL zijn, toon je de tekst ‘geen’.
SELECT last_name
, bonus
, department_id
, COALESCE(bonus, department_id, 'geen') AS comm
FROM employees;
| last_name | bonus | department_id | comm |
|---|---|---|---|
| King | 90 | 90 | |
| Kochhar | 90 | 90 | |
| De Haan | 90 | 90 | |
| Hunold | 60 | 60 | |
| Ernst | 60 | 60 | |
| Lorentz | 60 | 60 | |
| Mourgos | 50 | 50 | |
| Rajs | 50 | 50 | |
| Davies | 50 | 50 | |
| Matos | 50 | 50 | |
| Vargas | 50 | 50 | |
| Zlotkey | 1500 | 80 | 1500 |
| Abel | 1700 | 80 | 1700 |
| Taylor | 1250 | 80 | 1250 |
| Grant | geen | ||
| Whalen | 10 | 10 | |
| Hartstein | 20 | 20 | |
| Fay | 20 | 20 | |
| Higgins | 110 | 110 | |
| Gietz | 110 | 110 |
Je kunt een onbeperkt aantal kolommen gebruiken bij COALESCE. Nog een voorbeeld:
Als de kolom bonus gevuld is, wordt bonus getoond. Zie Zlotkey, Abel en Taylor.
Als bonus NULL is en commission_pct is gevuld, dan wordt commission_pct getoond. Zie Grant.
Als bonus en commission_pct NULL zijn, dan wordt het manager_id getoond. Zie alle overige personeelsleden behalve King.
Als bonus, commission_pct en manager_id NULL zijn, dan wordt de tekst ‘geen’ getoond. Zie King.
SELECT last_name
, COALESCE(bonus, commission_pct, manager_id, 'geen') AS comm
FROM employees;
| last_name | comm |
|---|---|
| King | geen |
| Kochhar | 100 |
| De Haan | 100 |
| Hunold | 102 |
| Ernst | 103 |
| Lorentz | 103 |
| Mourgos | 100 |
| Rajs | 124 |
| Davies | 124 |
| Matos | 124 |
| Vargas | 124 |
| Zlotkey | 1500 |
| Abel | 1700 |
| Taylor | 1250 |
| Grant | 0.15 |
| Whalen | 101 |
| Hartstein | 100 |
| Fay | 201 |
| Higgins | 101 |
| Gietz | 205 |
Te doen
De bovenstaande voorbeelden zijn “onzin-voorbeelden”: alleen maar bedoeld om COALESCE te demonstreren.
Kunnen we hiervoor een beter voorbeeld verzinnen? Of, COALESCE beschrijven in termen van een geneste en herhaalde IFNULL?