MySQL
Omgaan met schaarse of ontbrekende gegevens

Werken met kolommen met NULL-waarden

In MySQL en andere SQL-dialecten hebben NULL waarden speciale eigenschappen.

Overweeg de volgende tabel met sollicitanten, de bedrijven waarvoor ze werkten en de datum waarop ze het bedrijf verlieten. NULL geeft aan dat een aanvrager nog steeds bij het bedrijf werkt:

CREATE TABLE example
(`applicant_id` INT, `company_name` VARCHAR(255), `end_date` DATE);

+--------------+-----------------+------------+
| applicant_id | company_name    | end_date   |
+--------------+-----------------+------------+
|            1 | Google          | NULL       |
|            1 | Initech         | 2013-01-31 |
|            2 | Woodworking.com | 2016-08-25 |
|            2 | NY Times        | 2013-11-10 |
|            3 | NFL.com         | 2014-04-13 |
+--------------+-----------------+------------+

Het is uw taak om een query samen te stellen die alle rijen na 2016-01-01 retourneert, inclusief alle werknemers die nog steeds bij een bedrijf werken (degenen met NULL einddatums). Deze selecte verklaring:

SELECT * FROM example WHERE end_date > '2016-01-01';

bevat geen rijen met NULL waarden:

+--------------+-----------------+------------+
| applicant_id | company_name    | end_date   |
+--------------+-----------------+------------+
|            2 | Woodworking.com | 2016-08-25 |
+--------------+-----------------+------------+

Volgens de MySQL-documentatie retourneren vergelijkingen met de rekenkundige operatoren <,>, = en <> zelf NULL plaats van een boolean TRUE of FALSE . Een rij met een NULL einddatum is dus niet groter dan 2016-01-01 en niet kleiner dan 2016-01-01.

Dit kan worden opgelost met de sleutelwoorden IS NULL:

SELECT * FROM example WHERE end_date > '2016-01-01' OR end_date IS NULL;

+--------------+-----------------+------------+
| applicant_id | company_name    | end_date   |
+--------------+-----------------+------------+
|            1 | Google          | NULL       |
|            2 | Woodworking.com | 2016-08-25 |
+--------------+-----------------+------------+

Het werken met NULL's wordt complexer wanneer de taak aggregatiefuncties zoals MAX() en een GROUP BY clausule omvat. Als het uw taak is om de meest recente tewerkstellingsdatum voor elke sollicitant-id te selecteren, lijkt de volgende zoekopdracht een logische eerste poging:

SELECT applicant_id, MAX(end_date) FROM example GROUP BY applicant_id;

+--------------+---------------+
| applicant_id | MAX(end_date) |
+--------------+---------------+
|            1 | 2013-01-31    |
|            2 | 2016-08-25    |
|            3 | 2014-04-13    |
+--------------+---------------+

Wetende dat NULL aangeeft dat een sollicitant nog steeds in dienst is bij een bedrijf, is de eerste rij van het resultaat onnauwkeurig. CASE WHEN biedt een oplossing voor het NULL probleem:

 SELECT
    applicant_id,
    CASE WHEN MAX(end_date is null) = 1 THEN 'present' ELSE MAX(end_date) END
    max_date
FROM example
GROUP BY applicant_id;

+--------------+------------+
| applicant_id | max_date   |
+--------------+------------+
|            1 | present    |
|            2 | 2016-08-25 |
|            3 | 2014-04-13 |
+--------------+------------+

Dit resultaat kan worden samengevoegd met de oorspronkelijke example om het bedrijf te bepalen waar een aanvrager voor het laatst heeft gewerkt:

SELECT 
  data.applicant_id,
  data.company_name,
  data.max_date
FROM (
  SELECT 
    *,
    CASE WHEN end_date is null THEN 'present' ELSE end_date END max_date
  FROM example
) data
INNER JOIN (
 SELECT
   applicant_id,
   CASE WHEN MAX(end_date is null) = 1 THEN 'present' ELSE MAX(end_date) END max_date
 FROM
   example
 GROUP BY applicant_id
) j
ON data.applicant_id = j.applicant_id AND data.max_date = j.max_date;

+--------------+-----------------+------------+
| applicant_id | company_name    | max_date   |
+--------------+-----------------+------------+
|            1 | Google          | present    |
|            2 | Woodworking.com | 2016-08-25 |
|            3 | NFL.com         | 2014-04-13 |
+--------------+-----------------+------------+

Dit zijn slechts enkele voorbeelden van het werken met NULL waarden in MySQL.