Een zoekopdracht wordt zonder fouten uitgevoerd en retourneert een redelijk ogend getal, maar er ontbreken rijen en het gemiddelde is verkeerd. SQL NULL-waarden zijn vaak de oorzaak. NULL gedraagt ​​zich niet als een normale waarde, en dat valt zowel beginners als ervaren ontwikkelaars op.

Deze handleiding laat precies zien hoe NULL zich gedraagt. We hebben elke query uitgevoerd in SQLite 3.50 en de weergegeven resultaten zijn afkomstig van die run. Het grootste deel van dit gedrag is standaard-SQL, en we merken op waar databases verschillen, zoals de NULL-sorteervolgorde en de manier waarop Oracle met lege tekenreeksen omgaat.

Wat is NULL?

NULL is een markering die betekent dat een waarde ontbreekt of onbekend is. Het is geen waarde in de gebruikelijke zin, dus dit zijn allemaal verschillende dingen:

Het verschil is vooral van belang voor de bedragen. Een bonus van 0 betekent dat de persoon niets heeft ontvangen. Een NULL-bonus betekent dat niemand het nog weet.

De voorbeeldtafel

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Merk op dat Sara een lege string voor de telefoon heeft, terwijl Arjun en Ravi NULL hebben. De twee worden heel verschillend behandeld.

Testen op NULL

Dit is de fout die bijna iedereen in eerste instantie maakt:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

De eerste query is geldige SQL en geeft geen fout. Het retourneert eenvoudigweg niets, omdat het vergelijken van iets met NULL nooit waar is. Sara verschijnt onder IS NOT NULL, omdat een lege string een echte waarde is. In Oracle wordt een lege string echter behandeld als NULL, wat een opmerkelijke uitzondering is.

Driewaardige logica

In de meeste programmeertalen is een voorwaarde waar of onwaar. SQL voegt een derde resultaat toe: onbekend. Als je een waarde niet kent, kun je niet zeggen of deze gelijk is aan 5. En van twee onbekende waarden is niet bekend dat ze gelijk zijn, en daarom is NULL = NULL niet waar. In onze test retourneerden al deze NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Het combineren van voorwaarden volgt zorgvuldige regels. Hier zijn de resultaten van onze run, waarbij 1 waar is en 0 onwaar:

Rijen die verdwijnen van WAAR

WHERE behoudt een rij alleen als de voorwaarde waar is. Rijen waarvan het resultaat onbekend is, worden geschrapt, samen met de rijen die onwaar zijn. Dit leidt tot verrassende resultaten:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Je zou verwachten dat Arjun en Ravi verschijnen, aangezien hun telefoon zeker niet dat nummer is. Maar hun telefoon is onbekend, dus het resultaat is onbekend, en ze verdwijnen. Om ze op te nemen, moet je dat expliciet zeggen: WHERE phone <> '555-0101' OR phone IS NULL. Negeren helpt ook niet:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara en Ravi hebben geen bonus, dus verschijnen ze niet in bonus > 400 en ook niet in de tegenovergestelde film. Dit is een belangrijk ding om te onthouden: een voorwaarde en de ontkenning ervan bestrijken niet altijd elke rij.

COALESCE: zorg voor een terugval

COALESCE retourneert de eerste niet-NULL-waarde in de lijst en werkt in alle grote databases:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara lijkt nog steeds leeg, omdat een lege string niet NULL is. Soortgelijke functies bestaan, zoals IFNULL in MySQL en SQLite, ISNULL in SQL Server en NVL in Oracle. COALESCE is de draagbare keuze. Ons eerdere punt over berekeningen is hier ook van toepassing. bonus + 100 geeft NULL voor iedereen zonder bonus, dus schrijf COALESCE(bonus, 0) + 100 als het behandelen van een ontbrekende bonus als nul correct is voor uw bedrijf.

NULLIF: verander een waarde in NULL

NULLIF(a, b) retourneert NULL als de twee waarden gelijk zijn, en retourneert anders a. Twee toepassingen zijn bijzonder nuttig. Het kan tijdelijke waarden opschonen:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Die tweede query is een handige manier om waarden te vinden die “ontbrekend of leeg” zijn. Het kan ook beschermen tegen delen door nul, wat in veel databases een fout veroorzaakt:

SELECT total / NULLIF(quantity, 0) FROM sales;

Wanneer quantity 0 is, levert dit NULL op in plaats van dat het mislukt. In onze test retourneerde SELECT 100 / NULLIF(0, 0) NULL.

Aggregaten slaan NULL over

Dit is waar NULL de meeste schade aanricht aan rapporten:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Dus wat is de gemiddelde bonus, 400 of 200? Het hangt af van wat NULL betekent in uw gegevens. Als NULL betekent dat er geen bonus is betaald, dan is 200 juist. Als het betekent ‘nog niet besloten’, dan heeft 400 wellicht gelijk. De database kan deze beslissing niet voor u nemen, dus de vraag moet naar iemand gaan die de business kent.

Een gerelateerde valkuil: SUM zonder waarden is NULL in plaats van 0. Toen we alleen rijen optelden zonder bonus, kregen we NULL, terwijl COUNT 0 retourneerde. Applicatiecode die een getal verwacht, kan hierop crashen, dus wrap it: COALESCE(SUM(bonus), 0).

GROUP BY, sorteren en DISTINCT

Hoewel NULL’s in vergelijkingen “niet gelijk” zijn, behandelen deze bewerkingen ze als één groep:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Zie SQL GROUP BY en HAVING voor meer informatie over groeperen. Sorteervolgorde is waar databases echt van verschillen. SQLite en MySQL behandelen NULL als de kleinste waarde, terwijl PostgreSQL en Oracle deze als de grootste behandelen. Dus dezelfde ORDER BY bonus DESC kan NULL’s als laatste in de ene en als eerste in de andere plaatsen. Met PostgreSQL, Oracle en moderne SQLite kunt u dit rechtstreeks aangeven met behulp van NULLS FIRST of NULLS LAST.

Sluit zich aan

Een join-voorwaarde die gebruik maakt van = komt nooit overeen met NULL, dus rijen met een NULL-sleutel vallen weg uit een inner join:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera heeft geen manager, dus een inner join laat stilletjes dat rijtje vallen. A LEFT JOIN behoudt het, en creëert ook eigen NULL’s voor de ontbrekende zijde. Lees meer in SQL-joins uitgelegd.

De NIET IN-val

Eén enkele NULL in een NOT IN lijst wist elk resultaat:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

De tweede vorm betekent manager_id <> 1 AND manager_id <> NULL, en dat laatste kan nooit waar zijn. Dit is vooral gevaarlijk bij subquery’s, waarbij een NULL onopgemerkt kan binnenkomen. Gebruik in plaats daarvan NOT EXISTS, zoals uitgelegd in onze tutorial over SQL-subquery’s. Meera ontbreekt ook in het eerste resultaat, omdat manager_id van die rij NULL is.

Ontwerpen met NULL

• Gebruik standaard NOT NULL. Sta NULL alleen toe als er echt een waarde kan ontbreken.
• Voeg zinvolle standaardwaarden toe. Bijvoorbeeld quantity INT NOT NULL DEFAULT 0.
• Gebruik geen magische waarden. Een datum van 01-01-1900 of een leeftijd van -1 die in de plaats komt van ‘onbekend’ zal uw berekeningen verpesten.
• Ken het UNIEKE gedrag. De meeste databases staan ​​meerdere NULL’s toe in een UNIQUE kolom, en onze test heeft er twee zonder fouten ingevoegd. SQL Server is een uitzondering, omdat dit normaal gesproken toegestaan ​​is.
• Houd er rekening mee dat een primaire sleutel niet NULL kan zijn. Zie primaire sleutel versus externe sleutel.
• Test met NULL’s. Testgegevens zonder NULL’s verbergen deze bugs. Voeg ze expres toe, net als bij andere randgevallen.

Tot slot

Behandel NULL als “onbekend”, en het meeste gedrag begint logisch te worden: onbekend plus één is onbekend, en onbekend is gelijk aan onbekend waarvan niet bekend is dat het waar is. Controleer deze week een van uw eigen rapporten. Controleer null-kolommen met behulp van COUNT(*) tegen COUNT(column), zoek naar NOT IN en beslis wat NULL in elk gemiddelde zou moeten betekenen.

Veelgestelde vragen

Blijf leren