MySQL
Radzenie sobie z rzadkimi lub brakującymi danymi

Praca z kolumnami zawierającymi wartości NULL

W MySQL i innych dialektach SQL wartości NULL mają specjalne właściwości.

Rozważ poniższą tabelę zawierającą kandydatów do pracy, firmy, dla których pracowali, i datę ich odejścia. NULL wskazuje, że wnioskodawca nadal pracuje w firmie:

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 |
+--------------+-----------------+------------+

Twoim zadaniem jest skomponowanie zapytania, które zwraca wszystkie wiersze po 2016-01-01 , w tym wszystkich pracowników, którzy nadal pracują w firmie (ci z datami końcowymi NULL ). Ta instrukcja wyboru:

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

nie zawiera żadnych wierszy o wartości NULL :

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

Zgodnie z dokumentacją MySQL porównania przy użyciu operatorów arytmetycznych <,>, = i <> zwracają NULL zamiast wartości logicznej TRUE lub FALSE . Zatem wiersz z NULL data_końcowa nie jest większy niż 01.01.2016 ani mniej niż 01.01.2016.

Można to rozwiązać za pomocą słów kluczowych 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 |
+--------------+-----------------+------------+

Praca z wartościami NULL staje się bardziej złożona, gdy zadanie obejmuje funkcje agregujące, takie jak MAX() i klauzula GROUP BY . Jeśli Twoim zadaniem byłoby wybranie ostatniej daty zatrudnienia dla każdego identyfikatora_kandydata, następujące zapytanie wydaje się logiczną pierwszą próbą:

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    |
+--------------+---------------+

Jednak wiedząc, że NULL wskazuje, że kandydat jest nadal zatrudniony w firmie, pierwszy wiersz wyniku jest niedokładny. Korzystanie z CASE WHEN zapewnia obejście problemu NULL :

 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 |
+--------------+------------+

Ten wynik można ponownie połączyć z pierwotną example tabelą, aby określić firmę, w której wnioskodawca ostatnio pracował:

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 |
+--------------+-----------------+------------+

To tylko kilka przykładów pracy z wartościami NULL w MySQL.