MySQL
Att hantera glesa eller saknade data

Arbeta med kolumner som innehåller NULL-värden

I MySQL och andra SQL-dialekter har NULL värden speciella egenskaper.

Tänk på följande tabell som innehåller arbetssökande, företag de arbetade för och datum då de lämnade företaget. NULL anger att en sökande fortfarande arbetar i företaget:

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

Din uppgift är att komponera en fråga som returnerar alla rader efter 2016-01-01 , inklusive alla anställda som fortfarande arbetar på ett företag (de med NULL slutdatum). Detta valda uttalande:

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

misslyckas med att inkludera några rader med NULL värden:

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

Enligt MySQL-dokumentationen returnerar jämförelser med aritmetiska operatörer <,>, = och <> själva NULL istället för en boolesk TRUE eller FALSE . Således är en rad med NULL slutdatum varken större än 2016-01-01 eller mindre än 2016-01-01.

Detta kan lösas med hjälp av nyckelorden 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 |
+--------------+-----------------+------------+

Att arbeta med NULL blir mer komplicerat när uppgiften innebär aggregeringsfunktioner som MAX() och en GROUP BY klausul. Om din uppgift var att välja det senaste anställda datumet för varje sökande_ID verkar följande fråga vara ett logiskt första försök:

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

Men att veta att NULL indikerar att en sökande fortfarande är anställd i ett företag, är den första raden i resultatet felaktig. Att använda CASE WHEN ger en lösning på NULL frågan:

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

Detta resultat kan förenas tillbaka till den ursprungliga example tabellen för att bestämma bolaget vid vilken en sökande senast arbetade:

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

Det här är bara några exempel på att arbeta med NULL värden i MySQL.