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.
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:
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.
A NULL az adatbázisban van tárolva.
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.
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 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.
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:
azonosito | fonok_azonosito |
---|---|
1 | 2 |
2 | 3 |
3 | NULL |
Itt az 1 jelű kollágának 2 a főnöke, annak 3, akinek már viszont nincs főnöke.
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.
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.
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.
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
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.
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ő.
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.
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_id | cim |
---|---|
1 | tegnapi |
2 | mai |
a másikban számlasoroké:
szamla_id | sor_id | megnevezes |
---|---|---|
1 | 1 | cucc 1 |
1 | 2 | cucc 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_id | cim | sor_id | megnevezes |
---|---|---|---|
1 | tegnapi | 1 | cucc 1 |
1 | tegnapi | 2 | cucc 2 |
2 | mai | NULL | NULL |
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_id | cim |
---|---|
2 | mai |
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.
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_neve | mennyiseg |
---|---|
szög | 1200 |
kalapács | 40 |
csavar | 700 |
szög | 800 |
kalapács | 10 |
é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_neve | SUM(mennyiseg) |
---|---|
csavar | 700 |
kalapács | 50 |
szög | 2000 |
NULL | 2750 |
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:
aru | mennyiseg |
---|---|
csavar | 700 |
kalapács | 50 |
szög | 2000 |
ÖSSZESEN | 2750 |
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.
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ó.
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
é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_van | ideiglenes_lakhely | jelentés |
---|---|---|
X | [bármi] | nem tudjuk, van-e |
i | NULL | van, de nem tudjuk az értékét |
i | nem NULL | van é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
A következő esetek állhatnak fenn:
ideiglenes_lakhely_van | ideiglenes_lakhely | jelentés |
---|---|---|
X | [bármi] | nem tudjuk, van-e |
Y | [bármi] | van, de nem tudjuk az értékét |
i | nem NULL | van é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 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
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.