NULL functies

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	NULL
Kochhar	NULL
De Haan	NULL
Hunold	NULL
Ernst	NULL
Lorentz	NULL
Mourgos	NULL
Rajs	NULL
Davies	NULL
Matos	NULL
Vargas	NULL
Zlotkey	0.2
Abel	0.3
Taylor	0.2
Grant	0.15
Whalen	NULL
Hartstein	NULL
Fay	NULL
Higgins	NULL
Gietz	NULL

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	NULL	NULL	NULL
Kochhar	17000	NULL	NULL	NULL
De Haan	17000	NULL	NULL	NULL
Hunold	9000	NULL	NULL	NULL
Ernst	6000	NULL	NULL	NULL
Lorentz	4200	NULL	NULL	NULL
Mourgos	5800	NULL	NULL	NULL
Rajs	3500	NULL	NULL	NULL
Davies	3100	NULL	NULL	NULL
Matos	2600	NULL	NULL	NULL
Vargas	2500	NULL	NULL	NULL
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	NULL	NULL	NULL
Hartstein	13000	NULL	NULL	NULL
Fay	6000	NULL	NULL	NULL
Higgins	12000	NULL	NULL	NULL
Gietz	8300	NULL	NULL	NULL

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

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	NULL	90	90
Kochhar	NULL	90	90
De Haan	NULL	90	90
Hunold	NULL	60	60
Ernst	NULL	60	60
Lorentz	NULL	60	60
Mourgos	NULL	50	50
Rajs	NULL	50	50
Davies	NULL	50	50
Matos	NULL	50	50
Vargas	NULL	50	50
Zlotkey	1500	80	1500
Abel	1700	80	1700
Taylor	1250	80	1250
Grant	NULL	NULL	geen
Whalen	NULL	10	10
Hartstein	NULL	20	20
Fay	NULL	20	20
Higgins	NULL	110	110
Gietz	NULL	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

Ifnull​

Coalesce​

Ifnull

Coalesce