MySQL
Работа с разреженными или отсутствующими данными

Работа со столбцами, содержащими значения NULL

В MySQL и других диалектах SQL значения NULL имеют специальные свойства.

Рассмотрим следующую таблицу, в которой указаны кандидаты на работу, компании, в которых они работали, и дату, когда они покинули компанию. NULL указывает, что заявитель все еще работает в компании:

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

Ваша задача состоит в том, чтобы составить запрос, который возвращает все строки после 2016-01-01 , включая всех сотрудников, которые все еще работают в компании (с датами окончания NULL ). Этот оператор select:

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

не содержит никаких строк со значениями NULL :

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

В документации по MySQL сравнения с использованием арифметических операторов <,>, = и <> сами возвращают NULL вместо логического TRUE или FALSE . Таким образом, строка с NULL end_date не превышает 2016-01-01 или не меньше 2016-01-01.

Это можно решить, используя ключевые слова 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 |
+--------------+-----------------+------------+

Работа с NULL становится более сложной, когда задача включает в себя функции агрегации, такие как MAX() и предложение GROUP BY . Если ваша задача состояла в том, чтобы выбрать самую последнюю использованную дату для каждого заявителя, следующий запрос может показаться логической первой попыткой:

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

Однако, зная, что NULL указывает, что заявитель по-прежнему работает в компании, первая строка результата является неточной. Использование CASE WHEN обеспечивает обходной путь для проблемы 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 |
+--------------+------------+

Этот результат можно соединить обратно к исходному example таблице , чтобы определить компанию , в которой последний работал заявитель:

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

Это всего лишь несколько примеров работы со значениями NULL в MySQL.