Ennek a résznek talán jobb cím lenne az elmélet helyett a gyakorlat – a gyakorlati problémák és megoldási módjaik állnak a középpontban.

A legalapvetőbb témákat pirossal, az eggyel haladóbbakat sárgával jelöltem. A témák és témacsoportok kattintással csukhatók-nyithatók.

Sok hűhó (a) semmiért: A NULL használata – és elkerülése
A NULL lehetséges jelentései (rendszertana)

Elhatároztam, hogy készítek egy tudományos igényű NULL kategorizálást a botanikusok babérjaira (Laurus) törve s bár tudom, hogy nagy fába (Sequoiadendron giganteum) vágtam a fejszémet, bízom benne, hogy ez az osztályozás meghozza majd nekem a világhírnevet.

Alapvetően két fő családja létezik a NULL-nak:

  • az adatbázismezőkben előforduló (ha növény lenne, ezt hívnánk szabadon termőnek) és
  • művelet eredményeként keletkező (tenyésztett).

A szabadon termő NULL család számos fajból tevődik össze:

A második család is két csoportra osztható: véletlenül és szándékosan tenyésztett fajra. Mindkettő több alfajt foglal magában.

Mivel a NULL-lal az adatbázis tervezője különféle dolgokat akarhatott kifejezni, fontos, hogy mindig elgondolkozzunk, adott esetben mit is jelent a NULL.

Szabadon termő NULL fajták

A NULL az adatbázisban van tárolva.

A mező nem értelmezhető az adott rekordnál

Példa: Minden pénzfelvételről a bank adatbázisába bekerül egy rekord. Az ugyintezo_azonosito mező nem értelmezhető akkor, amikor a pénzfelvétel automatából történt.

Az érték még nem létezik

Példa: egy szerződést leíró rekordban a teljesites_datuma nyilván nem lehet kitöltve, míg nem tudjuk, hogy mikor lesz a teljesítés. Itt nem arról van szó, hogy az adatbevivő személy nem ismeri ezt a dátumot, hanem azt még senki nem ismerheti, tehát joggal mondhatjuk, hogy ez az adat még nem létezik, egyelőre definiálatlan.

Másik példa: egy hallgató vizsgajegye a félév elején – illetve bármikor a vizsga befejeződése előtt.

Az érték létezik, de még nem ismerjük

Az adatbevivő személy nem ismeri az értéket a rekord kitöltésekor, bár az érték létezhet.

Példa: egy vállalati ügyintéző személyi rekordot hoz létre Valaki Jánosról, aki holnap fog munkába állni a cégnél. Az ügyintéző János jónéhány adatát, például az ideiglenes lakhelyét nem ismeri, majd személyesen fogja megkérdezni tőle, amikor találkozik vele; addig is az ideiglenes_lakhely mezőbe NULL kerül.

Másik példa: A hajszálaim száma. Amíg meg nem számoljuk, addig ismeretlen ez az érték. Nem írhatunk ebbe a mezőbe nullát, mert nem vagyok kopasz – NULL-t kell írnunk, mert nem tudjuk a választ.

Az érték egyáltalán nem létezik

Példa: A fenti, Valaki Jánosról szóló történetet folytatjuk – a személyes adategyeztetés során kiderül, hogy Valaki kollégának nincs ideiglenes lakhelye. Ekkor már a NULL nem tükröz semmilyen bizonytalanságot, tudjuk, hogy nem létezik ilyen érték. És itt látszik a NULL használatával kapcsolatos legnagyobb probléma, tudniillik az, hogy a NULL többféle dolgot is jelenthet és segédinformáció feljegyzése nélkül ilyen esetben nem tudjuk, hogy melyik jelentése érvényes. A segédinformációs módszerről részletesebben itt olvashatunk.

Másik példa: Van olyan eset, amikor nagyon egyértelmű a NULL-nak a 'nincs' jelentése. Vegyünk például egy alkalmazottakat leíró táblát:

CREATE TABLE alkalmazottak
(
  azonosito INT PRIMARY KEY,
  fonok_azonosito INT NULL,
  ...
)

Azt feltételeztük persze, hogy mindenkinek legfeljebb egy közvetlen főnöke van. Akinél a főnök-azonosító NULL, az a legnagyobb főnök:

azonositofonok_azonosito
12
23
3NULL

Itt az 1 jelű kollágának 2 a főnöke, annak 3, akinek már viszont nincs főnöke.

Az érték 'végtelennek', 'lezáratlannak' tekintendő

Dátumok esetében gyakran előfordul, hogy a mező tartalma valamilyen időtartam határát jelöli ki, például azt, hogy Valaki Jánosnak meddig van joga egy adott szolgáltatást használni. Ha az időtartam korlátlan, akkor célszerűen NULL-t írhatunk ide.

Egyszerű tervezési trehányságból keletkező NULL

A NULL jelenléte sajnos jelezhet tervezési felületességet is: a tervező elfeledkezett az illető mezőnél kikötni, hogy nem tartalmazhat NULL-t:

CREATE TABLE munkatarsak
(
  csaladi_nev VARCHAR(30) NOT NULL,
  ...
)

és az adatbeviteli program készítője sem gondoskodott arról, hogy ez a mező mindig ki legyen töltve. Valójában nagyon sok mező van, ahol nem indokolható a NULL jelenléte! A NULL bevitelének megakadályozásáról részletesebben itt olvashatunk.

Véletlenül tenyésztett NULL fajták

A NULL nem egy adatbázismezőből jön, hanem valamely művelet eredményeként jön létre azért, mert a művelet valamelyik operandusa NULL vagy üres halmaz.

Egy aritmetikai vagy sztring művelet valamelyik paramétere NULL

Ha egy aritmetikai vagy sztring művelet bármelyik paramétere NULL, az eredmény is NULL lesz:

SELECT 10 + 20 + NULL
SELECT 1 / NULL
SELECT CONCAT('A','B',NULL) <-- MySQL sztring összefűzés

Egy logikai művelet valamelyik operandusa NULL

Nem minden esetben teszi az eredményt meghatározatlanná, ha egy logikai művelet operandusai között NULL szerepel, de TRUE AND NULL és FALSE OR NULL eredménye mindig NULL. Erről részletesebben itt olvashatunk.

Egy aggregátor függvényt üres halmazon kellene végrehajtani

Ha a SUM, MIN, MAX, AVG aggregátor függvényeket üres halmazra hívjuk meg, tehát egyetlen rekord sem felelt meg a leválogatási feltételeknek, akkor ezek a függvények NULL-t adnak vissza. Ez teljesen logikus működés, mert a semmi összege, átlaga, stb. nem nulla, hanem semmi. Logikus a működés, de ha nem számítunk rá, váratlan és/vagy hibás eredményeket tud produkálni!

Meg kell itt még jegyezni, hogy a COUNT függvény persze nem ilyen, mert az üres halmaz számossága értelmezhető és nullával egyenlő.

Szándékosan tenyésztett NULL fajták

Itt a NULL nem azért keletkezik, mert nem sikerült elvégezni egy műveletet, hanem valamilyen eset jelzésére szolgál.

Fontos megjegyezni, hogy mivel a NULL szándékosan jön létre, ezért ha magában az adattáblában van NULL adott helyeken, az abszolút összezavarja, meghamisítja a jelzés-funkciót.

OUTER JOIN-nál nincs párja egy rekordnak

Az OUTER JOIN lényege, hogy azon rekordok is szerepelni fognak a kimeneten, melyeknek nincs párja a másik táblában, ezekben a kimeneti sorokban a másik tábla mezői helyett NULL-ok fognak állni. Vegyük például a következő két táblázatot – az egyikben számlák adatai vannak:

szamla_idcim
1tegnapi
2mai

a másikban számlasoroké:

szamla_idsor_idmegnevezes
11cucc 1
12cucc 2

Ezt a lekérdezést hajtjuk végre:

SELECT szamlak.szamla_id,cim,sor_id,megnevezes
FROM szamlak
LEFT JOIN szamlasorok ON szamlak.szamla_id = szamlasorok.szamla_id

Az eredmény ez lesz:

szamla_idcimsor_idmegnevezes
1tegnapi1cucc 1
1tegnapi2cucc 2
2maiNULLNULL

A 2 sorszámú számlához nem tartoznak még számla-sorok, ezért jelennek meg a NULL-ok. A NULL most is – mint mindig – azt jelenti, hogy 'NEM LÉTEZIK', csak most nem a mező tartalma nem létezik, hanem már maga a mező sem: NINCS olyan rekord, ahol szamlasorok.szamla_id tartalma 2, ezért aztán a benne lévő sor_id és megnevezes mezők sem léteznek.

A JOIN ezen tulajdonságára alapozva lekérdezhetjük azon számlákat, amelyekhez nem tartozik egyetlen számla-sor sem:

SELECT szamlak.szamla_id,cim
FROM szamlak
LEFT JOIN szamlasorok ON szamlak.szamla_id = szamlasorok.szamla_id
WHERE szamlasorok.szamla_id IS NULL

A kimenet ez lesz:

szamla_idcim
2mai

Persze vigyázni kell, hogy legalább a szamlasorok.szamla_id és sor_id mezőkbe soha ne kerülhessen NULL, mert az megváltoztatná a kimenet jelentését.

GROUP BY záradék ROLLUP módosítóval

A ROLLUP módosító (egy kicsit most leegyszerűsítve) arra szolgál, hogy az összes csoport végén a megfelelő aggregátor-függvénnyel a teljes részre kiszámítsa a megfelelő értéket. Ha például rendelés-adatokat tárolunk a rendelesek táblában:

aru_nevemennyiseg
szög1200
kalapács40
csavar700
szög800
kalapács10

és ezt a lekérdezést futtatjuk:

# MySQL szintakszis
SELECT aru_neve,SUM(mennyiseg)
FROM rendelesek
GROUP BY aru_neve WITH ROLLUP

akkor ezt kapjuk a kimeneten:

aru_neveSUM(mennyiseg)
csavar700
kalapács50
szög2000
NULL2750

Az utolsó sorban a NULL azt jelzi, hogy ez az összes csoportra vonatkozó összeg (tehát az összes rendelt mennyiség). Persze a NULL helyett kiírathatunk valami értelmes szöveget is:

# MySQL szintakszis
SELECT COALESCE(aru_neve,'ÖSSZESEN') AS aru,SUM(mennyiseg) AS mennyiseg
FROM rendelesek
GROUP BY aru_neve WITH ROLLUP

Ekkor ez lesz az eredmény:

arumennyiseg
csavar700
kalapács50
szög2000
ÖSSZESEN2750

Persze vigyázni kell, hogy az aru_neve oszlopba ne kerülhessen NULL, mert az teljesen összezavarja a kimutatást.

Megjegyzés: A CUBE függvénynél a NULL hasonló jelző-szerepet játszik, mint a ROLLUP-nál.

A NULL és az üres sztring

Fentebb láttunk egy példát, amelyben Valaki János munkába áll és az ideiglenes lakhelyét megadó mezőben lévő NULL jelentése megváltozik: Először azt jelentette, hogy nem tudjuk, van-e neki ideiglenes lakhelye, később pedig már azt, hogy tudjuk, hogy nincs.

Nem nehéz belátni, hogy miért baj ez: amikor ki akarnánk keresni, hogy hol vannak az adatbázisunkban még kitöltetlen mezők, nincs eszközünk ennek a vizsgálatára. Hiába keressük ki azokat a rekordokat, ahol ideiglenes_lakhely NULL-t tartalmaz – nem fogjuk tudni, hogy egy adott NULL éppen azt jelenti-e, hogy még kitöltetlen a mező.

Logikus ötletnek tűnhet az üres sztring használata (tehát '', két aposztrof egymás mellett, köztük nincs semmi) annak jelzésére, hogy tudott módon nincs ideiglenes lakhely. Ez a megoldás logikailag valóban korrekt, de a gyakorlatban van egy óriási buktatója: az adatbáziskezelők egyes esetekben önkényesen átkonvertálják az üres sztringeket NULL-ba, illetve az adatkezelő programokban előfordul az ellenkező irányú konverzió is. Kijelenthetjük, hogy sérülékeny az a megoldás, amikor a NULL és az üres sztring jelentése különböző.

A fenti gondolatból kiindulva megállapíthatjuk, hogy amennyiben egy szöveges mezőben NULL és nulla hosszúságú sztring is előfordulhat, akkor növeli a programok robusztusságát, hibatűrőképességét, ha ehhez a két dologhoz logikailag ugyanazt a jelentést rendeljük hozzá.

Ha azon rekordokra van szükségünk, ahol az ideiglenes lakhely üres (mindenféle értelemben), akkor írjuk ezt:

SELECT ... WHERE ideiglenes_lakhely IS NULL OR ideiglenes_lakhely = ''

ha pedig a nem üresekre vagyunk kíváncsiak, akkor ezt:

SELECT ... WHERE NOT (ideiglenes_lakhely IS NULL OR ideiglenes_lakhely = '')

amit persze megfogalmazhatunk másként is:

SELECT ... WHERE ideiglenes_lakhely IS NOT NULL AND ideiglenes_lakhely <> ''

sőt, az első feltételt el is hagyhatjuk, írhatjuk akár ezt is:

SELECT ... WHERE ideiglenes_lakhely <> ''

mert ez a feltétel nem teljesül a NULL-t tartalmazó mezőkre, hiszen minden összehasonlítás HAMIS értéket szolgáltat, amelyben valamelyik operandus NULL (részletesebben ld. itt).

Ennek a résznek az elején azt az ötletet vetettük fel, hogy az ideiglenes_lakhely mező tartalmát tegyük úgy világossá, hogy NULL-lal jelöljük az egyik állapotot és üres sztringgel a másikat. Mostanra kiderült, hogy ez a megoldás csak elméletileg jó, a gyakorlatban nem javasolható.

A NULL magyarázása

Térjünk ismét vissza a fenti példához, amelyben Valaki János munkába áll és az ideiglenes lakhelyét megadó mezőben lévő NULL jelentése megváltozik: Először azt jelentette, hogy nem tudjuk, van-e neki ideiglenes lakhelye, később pedig már azt, hogy tudjuk, hogy nincs. Nagy szükség lenne arra, hogy tudjuk, adott pillanatban éppen mit jelent a NULL.

A jelentések megkülönböztetése céljából be kell vezetnünk egy másik mezőt, amelyben az ideiglenes lakhely létezését jelezzük (most az egyszerűség kedvéért feltesszük, hogy legfeljebb egy ideiglenes lakhely lehet). Ha az ideiglenes_lakhely_van mező értéke

  • 'X', akkor nem tudjuk, hogy van-e ideiglenes lakhely
  • 'i', akkor van
  • 'n', akkor nincs

és ennél a mezőnél nem engedjük meg, hogy NULL legyen (vagy bármi más a felsoroltakon kívül). A különböző állapotokat persze ízlés szerint másképp is kódolhatjuk, például számokkal.

A következő esetek állhatnak fenn:

ideiglenes_lakhely_vanideiglenes_lakhelyjelentés
X[bármi]nem tudjuk, van-e
iNULLvan, de nem tudjuk az értékét
inem NULLvan és tudjuk is az értékét
n[bármi]nincs

Az első sorban a [bármi] azt jelenti, hogy ha az ideiglenes_lakhely_van mezőben X van, akkor meg sem kell néznünk az ideiglenes_lakhely tartalmát, tudjuk, hogy egyelőre ismeretlen ez az adat. Itt a magyarázó mező teljesen át tudja venni a leírást – bár lehet precízkedni és azt mondani, hogy ilyenkor kötelező NULL-t írni az ideiglenes_lakhely oszlopba. Hogy kerülhet ide egyáltalán NULL-tól különböző érték? Hát például úgy, hogy egyszer már ki volt töltve a lakhely mező, de később rájöttünk, hogy hibásan, tehát vissza kell állítanunk a 'nem tudjuk' állapotot.

Ugyanez a gondolatmenet érvényes az utolsó sorra is.

Az Olvasó joggal vetheti fel, hogy egyszerűsíthetünk a szisztémán egy másik kódolási módszerrel. Ha az ideiglenes_lakhely_van mező értéke

  • 'X', akkor nem tudjuk, hogy van-e ideiglenes lakhely
  • 'Y', akkor van, de nem tudjuk az értékét
  • 'i', akkor van és tudjuk az értékét
  • 'n', akkor nincs

A következő esetek állhatnak fenn:

ideiglenes_lakhely_vanideiglenes_lakhelyjelentés
X[bármi]nem tudjuk, van-e
Y[bármi]van, de nem tudjuk az értékét
inem NULLvan és tudjuk is az értékét
n[bármi]nincs

A NULL-t teljesen száműztük, nem hordoz már semmilyen jelentést!

A NULL megakadályozása

A tábla definíciójában előírhatjuk, hogy egy mező nem tartalmazhat NULL-t:

CREATE TABLE munkatarsak
(
  csaladi_nev VARCHAR(30) NOT NULL,
  ...
)

(Mint látni fogjuk, a nulla hosszúságú mezők elkerülése már nehezebb feladat, technikai okok miatt.)

A fenti adatbázis-megkötés (CONSTRAINT) azt írja elő, hogy ebbe a mezőbe nem kerülhet NULL. A megkötések betartatása az adatbáziskezelő dolga – ez a jó hír. A rossz az, hogy adatbáziskezelőnként más és más megkötések lehetnek, sőt, előfordul az is, hogy a megkötés leírását megengedi az RDBMS, de nem hajtja végre. Tehát az aktuális rendelkezésre álló CONSTRAINT-eket ellenőrizni kell

  • az adattáblák megtervezésekor és
  • sajnos akkor is, ha az adatainkat migráljuk (másik adatbáziskezelő vagy verzió alá helyezzük át).

Szerencsére a szóban forgó, nagyon fontos megkötést (ne lehessen NULL a mezőben) gyakorlatilag minden RDBMS megvalósítja.

A megkötések mellett az adatbeviteli programnak is gondoskodnia kell arról, hogy ez a mező mindig ki legyen töltve. Ez a második dolog is fontos! Részletesebben a kettős védelem elvéről itt olvashatunk.