Tanuláshoz, a SELECT szintaktikai szabályainak ellenőrzéséhez hasznos az itt következő ötletke.

Mivel a SELECT mezőlistájában konstansok is szerepelhetnek, sokmindent ki tudunk próbálni nagyon gyorsan, anélkül, hogy bármilyen táblához fordulnánk. Tegyük fel például, hogy kíváncsiak vagyunk, mi történik, ha NULL-lal próbálunk meg osztani. Ehhez nem kell létrehoznunk egy táblát és abba betöltenünk valahová NULL-t, hanem elég, ha ezt írjuk:

Vagy például a MySQL CONCAT függvényét is kipróbálhatjuk:

Amikor először tesztelünk egy lekérdezést, akkor csináljunk kis méretű, áttekinthető adatokat, hogy jól látsszon, valóban a kívánt eredményt kapjuk-e. Szövegnek érdemes például olyant választani, amin jól látszik, hogy melyik kulcshoz tartozik:

MIELŐTT lefuttatnánk a lekérdezést, írjuk le magunknak, hogy milyen eredményt várunk. (Vagy mondjuk el hangosan két, nem megvesztegethető és velünk kíméletlen tanú előtt.) Ha először lefuttatjuk a lekérdezést és csak utána állunk neki tanulmányozni az eredményt, nagyon hajlamosak vagyunk azt helyesnek látni. Ez egy gonosz pszichológiai kelepce!

Onnan tudom, hogy sokszor belegyalogoltam.

Bármilyen szoftver – például egy adatbáziskezelő – kiismeréséhez jó módszer, ha szándékosan elkövetünk szintaktikai vagy tartalmi hibákat és megnézzük, hogy kapunk-e hibajelzést és ha igen, milyet. Kezdőknél ennek az az előnye is megvan, hogy kevésbé fognak megijedni a hibajelzésektől.

Szóval a szoftver olyan oroszlán, amelynek szabad cibálni a bajuszát, sőt érdemes is. Persze azért itt nem olyan kísérletre gondolok, hogy megpróbálunk egy fontos táblát kitörölni és meglepett sikkantással vesszük tudomásul, hogy sikerült...

Ha meg akarunk fejteni egy bonyolultabb lekérdezést, melyben táblák INNER JOIN-nal vagy implicit JOIN-nal vannak összekapcsolva, hogy lássunk hozzá? Hogyan olvassuk?

Hát a lényeg, hogy nem balról jobbra és föntről lefelé.

Vegyünk most két táblát, a t1-ben személyek azonosítója és neve van tárolva:

t2-ben pedig személyazonosítók és számlaszámok (a számlaszámok valójában alfanumerikus számla-azonosítók, nem számok):

Láthatólag Valakinek és Másnak van egy-egy számlája, a Nincsneki nevű embernek pedig egy sincs.

Adott egy lekérdezés kétféle formában, amely a Valaki nevű ember számlaszámát adja ki. Az első implicit JOIN-t használ, tehát egyszerűen a FROM záradékban felsorolja a két táblát:

SELECT t2.szamlaszam
FROM t1,t2
WHERE t1.id = t2.id
AND   t1.nev = 'Valaki'

a másik pedig INNER JOIN-nal fogalmazza meg ugyanazt:

SELECT t2.szamlaszam
FROM t1
INNER JOIN t2 ON t1.id = t2.id
WHERE t1.nev = 'Valaki'

Először is a FROM (és esetleges JOIN) záradékot nézzük meg, ebből kiderül, hogy mely táblákból származó rekordok lesznek a kimeneten. Itt

...
FROM t1,t2
...

illetve itt

...
FROM  t1
INNER JOIN t2
...

a t1 és a t2 tábla sorai kombinálódnak össze egy keresztszorzatban (t1 mindegyik sora t2 mindegyik sorával). Úgy lehet elképzelni, hogy létrejön egyetlen nagy KSZ tábla, amely a két (vagy több) tábla minden oszlopát tartalmazza, rekordjai pedig a rekordok összes kombinációjából állnak:

A KSZ táblának nyilván 3 * 2 = 6 sora van. A keresztszorzat (direkt szorzat, Descartes-szorzat) elnevezés a halmazelméletből származik. Az A X B halmaznak azon rendezett párok az elemei, melyek első eleme A-beli, második eleme pedig B-beli és a szorzat minden lehetséges párt tartalmaz. Egy tábla rekordjait felfoghatjuk halmaz-elemeknek (feltéve, hogy nincs közöttük két teljesen egyforma).

Ha az implicit esetben nem adnánk meg semmilyen feltételt:

SELECT *
FROM t1,t2

illetve az ON záradékban mindig teljesülő feltételt adnánk meg:

SELECT *
FROM t1,t2
INNER JOIN t2 ON 1 = 1

akkor ezt a keresztszorzatot kapnánk. (Tessék kipróbálni!)

Másodszor megnézzük a WHERE összekapcsolási részét (azt a feltételt, amely a sorok összekapcsolásáról rendelkezik) vagy az ON feltételt, hogy lássuk, a megszűrt keresztszorzatból mely párok maradnak meg:

...
FROM t1,t2
WHERE t1.id = t2.id
...

illetve:

...
FROM t1
INNER JOIN t2 ON t1.id = t2.id
...

Most tehát azok a rekordok maradnak meg, amelyeknél t1.id és t2.id egyforma:

Harmadszor a WHERE feltétel szűrési részét vesszük szemügyre, ha van ilyen. Most van:

...
FROM t1,t2
WHERE t1.id = t2.id
AND   t1.nev = 'Valaki'

illetve:

...
FROM t1
INNER JOIN t2 ON t1.id = t2.id
WHERE t1.nev = 'Valaki'

A szűrési feltétel hatására csak azon sorok maradnak meg, amelyeknél t1 nev mezejében 'Valaki' áll.

INNER JOIN esetén mindegy, hogy a szűrő feltételt az ON záradékba vagy a WHERE záradékba írjuk, ez is ugyanazt az eredményt szolgáltatja:

...
FROM t1
INNER JOIN t2 ON t1.id = t2.id
WHERE t1.nev = 'Valaki'
AND t1.nev = 'Valaki'

Azért jobb a WHERE feltételbe tenni, mert könnyebben olvasható lesz a lekérdezés, azonnal látszik, melyik feltétel szolgál szűrésre és melyik összekapcsolásra. A könnyű olvashatóság nagyon fontos!

Negyedszer megnézzük, hogy van-e ORDER BY záradék; a keresztszorzat-táblában megmaradt rekordokat ennek megfelelően sorba kell rendezni. (A rendezésben szereplő mezőknek nem kell szerepelniük semmilyen feltételben vagy a kimeneti listában! Ez sokakat zavar kezdetben.) A példánkban most nincs rendezés előírva.

Végül megnézzük, hogy mely mezők jelennek meg a kimeneten:

SELECT t2.szamlaszam
FROM  t1,t2
WHERE t1.id = t2.id
AND   t1.nev = 'Valaki'

illetve:

SELECT t2.szamlaszam
FROM   t1
INNER JOIN t2 ON t1.id = t2.id
WHERE t1.nev = 'Valaki'

Most csak a t2 tábla egyik mezejét adjuk ki, de a megfelelő rekord megtalálásához szükség volt a másik táblára is.

Megjegyzés: A SELECT tanulmányozásának fázisait felhasználhatjuk a felépítéséhez is. Ha először a keresztszorzat minden oszlopát (mezejét) kiadjuk és lépésről lépésre építjük fel a lekérdezést, kevés meglepetés érhet. Persze ehhez kis teszt-táblákra van szükség – egy 10.000 és egy 20.000 soros tábla keresztszorzatként való megtekintése első lépésben nem túl praktikus, mivel kétszáz millió sora van...

Természetesen ezt sem balról jobbra és felülről lefelé. Egészen hasonlóan haladunk, mint az INNER JOIN esetében. A példában LEFT JOIN-t használunk, de értelemszerű változtatásokkal mindez érvényes nyilván a RIGHT JOIN-ra is.

Először a FROM, a JOIN és az ON záradékot kell megnézni, ebből kiderül, hogy mely táblákból származó rekordok lesznek a kimeneten. Például itt:

...
FROM  t1
LEFT JOIN t2 ON t1.id = t2.id
...

a t1 és a t2 tábla sorai kombinálódnak össze. Most is úgy gondolkodhatunk, hogy képződik a két tábla rekordjainak keresztszorzata, egy KSZ tábla, amely a két tábla minden oszlopából áll és tartalmazza a sorok összes létező kombinációját. Ha az ON záradékba 1 = 1 feltételt írnánk, most is ezt kapnánk:

Így lehet kipróbálni:

SELECT *
FROM  t1
LEFT JOIN t2 ON 1 = 1

Az ON t1.id = t2.id feltétel hatására megmaradnak a KSZ tábla azon sorai, melyek megfelelnek ennek a feltételnek – eddig ugyanaz történik, mint az INNER JOIN esetében. És most jön a különbség: amely baloldali rekordokra nem teljesül az ON feltétel, azokat hozzáteszi az adatbáziskezelő úgy, hogy a jobboldali tábla oszlopaiba NULL-t ír.

Másodszor a WHERE feltételt vizsgáljuk meg. A KSZ tábla soraiból csak azok maradnak meg, amelyek a szűrési feltételnek megfelelnek. Ha például ezt a feltételt írjuk:

...
FROM  t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.nev = 'Valaki'
...

akkor ez lesz az eredmény:

Harmadszor megnézzük, hogy van-e ORDER BY záradék; a KSZ táblában megmaradt rekordokat ennek megfelelően sorba kell rendezni. A példánkban most nincs rendezés előírva.

Végül megnézzük, hogy mely mezők jelennek meg a kimeneten:

SELECT  t1.nev, t2.szamlaszam
FROM  t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.nev = 'Valaki'

A szűrő feltétel helye: ON vagy WHERE?

Az INNER JOIN esetében a szűrő feltételt tetszés szerint elhelyezhettük az ON vagy a WHERE záradékban – az OUTER JOIN esetében nem így van.

Helyezzük át az összekapcsolási feltételbe a fenti példa szűrő feltételét:

SELECT *
FROM  t1
LEFT JOIN t2
ON t1.id = t2.id AND t1.nev = 'Valaki'
WHERE t1.nev = 'Valaki'

Az eredmény első ránézésre meglepő lesz:

Az ON feltételnek csak egyetlen rekord felel meg, ezért a többibe NULL-ok kerültek – és ezek is megjennek a kimeneten. Ennek a feltételnek így önmagában nem sok értelme van persze, hiszen a kimeneten megjelenik NULL-okkal mindenki, akinek nincs számlája vagy van ugyan neki, de nem 'valaki_szama' azonosítójú. Ha ezeket kiszűrjük:

...
FROM  t1
LEFT JOIN t2
ON t1.id = t2.id AND t1.nev = 'Valaki'
WHERE t2.id IS NOT NULL
...

akkor értelmes eredményt kapunk:

de értelmetlenül bonyolult módon – ugyanezt elérhettük volna egy egyszerű INNER JOIN segítségével is.

Előfordul azért a gyakorlatban értelmes bővítése is az ON záradéknak. Tegyük fel, hogy ennél a példánál azokra a számlákra vagyunk kíváncsiak, amelyekhez nem tartozik 'cucc 2' megnevezésű árura vonatkozó számlasor, vagyis éppen az INNER JOIN által szolgáltatott halmaz komplementerét akarjuk megkapni. Így jutunk célhoz:

SELECT szamlak.szamla_id,cim
FROM szamlak
LEFT JOIN szamlasorok ON
          szamlak.szamla_id = szamlasorok.szamla_id
          AND megnevezes = 'cucc 2'
WHERE szamlasorok.szamla_id IS NULL

Az ON feltétel hatására megjelennek mindazon számlák a számlasorral együtt, melyekhez tartozik 'cucc 2' számlasor; a többi számla pedig úgy jelenik meg, hogy a számlasor mezőkben NULL áll.

SELECT *
FROM szamlak
LEFT JOIN szamlasorok ON
          szamlak.szamla_id = szamlasorok.szamla_id
          AND megnevezes = 'cucc 2'

eredménye az összekapcsolt tábla összes sora:

És nekünk most pont a NULL-os sorokra van szükségünk.

Ha a megnevezésre vonatkozó feltételt áthelyeznénk a WHERE záradékba:

SELECT szamlak.szamla_id,cim
FROM szamlak
LEFT JOIN szamlasorok ON
          szamlak.szamla_id = szamlasorok.szamla_id
WHERE megnevezes = 'cucc 2'
AND szamlasorok.szamla_id IS NULL

akkor üres halmazt kapnánk a kimeneten. Vagyis semmit. Vajon miért?

Kezdjük az elején! Nézzük meg ennek a lekérdezésnek az eredményét:

SELECT *
FROM szamlak
LEFT JOIN szamlasorok ON
          szamlak.szamla_id = szamlasorok.szamla_id

Ezt kapjuk:

Stimmel a dolog – csak a 'tegnapi' számlához tartoznak sorok. Ha most ezt a táblát megszűrjük a

...
WHERE megnevezes = 'cucc 2'
AND szamlasorok.szamla_id IS NULL

feltétellel, akkor semmi nem marad. Hiszen nincs ilyen sor az összekapcsolt táblában. Ne feledjük:

Belülről kifelé. Ha nem értjük, hogy működik egy beágyazott SELECT, futtassuk le először a belső SELECT-et, az eredményét kézzel helyettesítsük be a külsőbe és világossá fog válni az egész. Például ezt a lekérdezést akarjuk megfejteni:

SELECT f1 FROM t1
WHERE f1 > (SELECT AVG(f1) FROM t1)

Először lefuttatjuk a belső lekérdezést:

SELECT AVG(f1) FROM t1

amelynek az eredménye 42. Ezt behelyettesítjük a külső lekérdezésbe:

SELECT f1 FROM t1
WHERE f1 > 42

Ennek a módszernek nyilván ennél bonyolultabb SELECT-ek esetében vesszük hasznát, illetve akkor, ha hibás a lekérdezés, pl. a fenti esetben a belső lekérdezés egynél több értéket szolgáltat.

Néha azért nem tudjuk megfejteni, hogy miként működik egy lekérdezés, mert túlságosan kevés mező értékét adja ki. A szűrő- és az összekapcsoló feltételekben szereplő mezőkre sokszor nincs szükségünk a kimeneten, ám a működés megismeréséhez annál inkább fontosak. Ilyenkor egyszerűen írjuk bele ezeket a kimeneti mezőlistába!

Amikor nullával osztás történik, arra nem egyformán reagálnak az adatbáziskezelők. Ha tehát kiadjuk ezt a parancsot:

akkor például

• a MySQL (legalábbis az általam használt verzió) NULL-t fog visszaadni;
• az MS Access VBScript-en keresztül lekérdezve nullát (!);
• az MS Access a felhasználói felületén keresztül hibajelzést
• ...

szóval ahány ház, annyi szokás.

Tanulság: Kerüljük el a nullával osztást, mert ha már megtörtént, annak környezettől függően különböző következményei lehetnek! Már azt sem könnyű utólag megállapítani, hogy nullával osztás történt.

Ez primitív tanácsnak tűnik, mégsem az; összetettebb lekérdezéseknél könnyen becsusszanhat egy ilyen eset:

Biztonságosnak sajnos csak azt a megoldást nevezhetjük, amikor első lépésben megvizsgáljuk a nevezőt és

1. ha az nulla, akkor ezt a tényt jelezzük
2. ha nem, akkor végrehajtjuk a lekérdezést.

Ilyen szempontból az SQL-t ugyanolyan programozási nyelvként érdemes kezelni, mint a többit: először megvizsgáljuk, hogy elvégezhető-e egy művelet (például nem fogunk-e nullával osztani) és csak ezután végezzük el a műveletet. Erről a szemléletről nagyon könnyű megfeledkezni az SQL deklaratív jellege miatt!

Ha egy SELECT parancsba nem teszünk ORDER BY záradékot, akkor a kimeneten a rekordok sorrendje nem garantált, tehát nem biztos, hogy megegyezik azzal a sorrenddel, ahogyan a rekordokat bevittük. Általában azt fogjuk látni, hogy a bevitel sorrendjében kapjuk a rekordokat, de előfordulhat (főleg, ha nagyon sok sorból áll a kimenet), hogy néhol más lesz a sorrend.

Ha tehát fontos a sorrend, akkor mindig használjunk ORDER BY záradékot – még akkor is, ha ettől a lekérdezésünk lassúbb lesz.

A rendezés nyilván időt vesz igénybe, nem ritkán ez a leglassúbb művelet az egész SELECT-ben – amiből persze következik, hogy ha nem fontos a sorrend, akkor viszont ne rendezzünk.

A NULL egyik legfontosabb jellegzetessége az, hogy bármivel és bárhogyan hasonlítjuk össze, az összehasonlítás hamis eredményt szolgáltat. Még egyik NULL érték sem egyenlő a másikkal! Ez persze nem is annyira meglepő, ha belegondolunk, hogy a NULL azt jelenti, hogy az illető helyen álló érték még nincs kitöltve, nem ismerjük (vagy értelmezhetetlen) - így hát mindenféle összehasonlítás értelmetlen. A NULL tehát nem is érték, hanem egy jelzés; az SQL ezt fejezi ki azzal, hogy egy NULL nem egyenlő semmilyen más értékkel, nem különbözik tőle, nem nagyobb nála, nem is kisebb nála... akárhogy kérdezzúk, 'nem' lesz a válasz. Nincs értelme a kérdésnek.

Képzeljük el, hogy az adat egy dobozban van, és valaki ki akarja barkochbázni, hogy mi van a dobozban. Ha az üres (NULL van benne) és azt kérdezik, hogy

akkor azt kell válaszolnunk, hogy nem. Ha azt kérdezik, hogy kisebb-e 42-nél, arra is nemmel kell válaszolnunk, mint ahogy arra a kérdésre is, hogy egyenlő-e a doboz tartalma 42-vel. De mindezen válaszok félrevezetőek, ha a játékostársunk nem ismeri az SQL-barkochba szabályait – két olyan kérdés létezik csak, melyekre adott válasz informatív ilyenkor:

Üres a doboz? (doboz IS NULL)

  vagy:

Nem üres a doboz? (doboz IS NOT NULL)

Egy leválogatásban azon rekordok fognak szerepelni,

• amelyeknél el lehetett végezni a feltételvizsgálatot és
• amelyekre teljesül a feltétel.

Mivel az IS NULL és IS NOT NULL vizsgálatokon kívül semmilyen feltétel nem vizsgálható NULL tartalmú mezőre, ezért minden más feltétel kiejti az eredményhalmazból a vizsgált mezőben NULL-t tartalmazó rekordokat.

Ha azt a barkochbaszerű kérést intézzük játszópajtásunkhoz, Eskú Elekhez, hogy

akkor ő nem fogja odaadni az üres dobozokat. Teljes joggal, mivelhogy azoknak nincs tartalmuk, tehát nem jelenthető ki róluk, hogy a tartalmuk különbözik 42-től. A semmi nem különbözik egyetlen dologtól sem. (Talán szerencsésebb lett volna NOTHING-nak nevezni az üres mezőt NULL helyett, dehát engem nem kérdeztek és most már késő. Ám azért magunkban hívhatjuk így – hátha ez a varázsige megsegít... :)

Gyakorló példák: Hány nem-NULL van? Mi nem egyenlő mivel?

A NULL másik legfontosabb jellegzetessége az, hogy majdnem az összes aritmetikai vagy sztring művelet végzésénél

az eredmény NULL lesz, ha bármelyik operandus NULL.

A NULL olyan, mint a fekete lyuk: magához vonz (majdnem) mindent, amelyik feltételvizsgálatba, matematikai vagy sztring műveletbe bekerül, annak eredményét elkapja és hideg kíméletlenséggel NULL-lá teszi. Azért csak majdnem mindent írtam, mert vannak speciális NULL-kezelő függvények, amelyeknek épp az a szakmájuk, hogy a NULL-t elfogják és valamivé átalakítsák. Levadásszák és megsütik. Ezekről részletesebben itt olvashatunk.

A NULL és a logikai műveletek: Erről érdemes külön egy kicsit részletesebben szólni.

AZ AND művelet tulajdonságai:

FALSE AND NULL azért FALSE, mert a másik operandus bármilyen értékű lehet, nem teheti TRUE-vá az eredményt. Ha azt mondják nekünk, hogy akkor adok neked kölcsön, ha a Nap nyugaton kel fel és még majd ki fogok találni egy másik feltételt is, akkor biztosan nem számíthatunk a kölcsönre.

TRUE AND NULL azért NULL, mert nem lehet eldönteni, hogy teljesül-e. Ha a második feltétel ismertté válik és TRUE, akkor az eredmény TRUE lesz, ha a második feltétel FALSE, akkor FALSE lesz.

Aki nem hiszi, próbálja ki például így:

AZ OR művelet tulajdonságai:

TRUE OR NULL azért TRUE, mert a másik operandus bármilyen értékű lehet, nem teheti FALSE-zá az eredményt.Ha azt mondják nekünk, hogy akkor adok neked kölcsön, ha a Nap keleten kel fel vagy egy másik feltétel is teljesül, amit csak később fogok kitalálni, akkor biztosan számíthatunk a kölcsönre.

Gyakorló példák: Mennyi az összjutalom? NULL-lal osztunk Munkahelyek összefűzve

Külön figyelmet igényel a COUNT függvény használata, amikor NULL is előfordulhat a vizsgált mezőben.

Gyakorló példák: Hány NULL van? Hány sor van? Mennyi van az értékekből?

A NULL alapvető jelentése az, hogy az érték (egyelőre) ismeretlen vagy nem értelmezhető, de lehet azt is jelezni vele, hogy az illető jellemző nem létezik. Ha például a teljesites_datuma mező NULL, akkor logikus, hogy még nem történt meg a teljesítés, annak dátuma egyelőre ismeretlen. Ilyenkor nincs probléma, fel sem merül, hogy a dátum létezik, csak éppen üres.

Ám ha pl. az ideiglenes_lakhely mező NULL, akkor már gondok lehetnek. Ez tudniillik jelentheti

• azt, hogy a rekord bevitelekor az ügyintéző még nem tudta, van-e az illető személynek ideiglenes lakhelye, vagy
• azt, hogy tudjuk: nincs neki

SELECT...WHERE LENGTH(ideiglenes_lakhely) = 0 ...

  vagy:

SELECT...WHERE ideiglenes_lakhely = '' ...

Itt azokat a rekordokat válogattuk le, amelyekben a az ideiglenes_lakhely mező ki van töltve, csak éppen egy nulla hosszúságú (üres) sztringgel.

A NULL-t tartalmazó mezőket így kaphatjuk meg:

Itt azokat a rekordokat válogattuk le, amelyekben a az ideiglenes_lakhely mező nincs kitöltve.

A különbséget azért nehéz megérteni, mert önkéntelenül magunk elé képzelünk egy kockás papírt, amelyen van egy táblázat és ebben a táblázatban egyes rubrikákat üresen hagyunk – itt nyilván nem értelmezhető az 'üres értéket írok ide' kijelentés. Az adatbázisokban viszont nagyon is mást jelent az, hogy ide nem írtak (még) semmit és az, hogy ide egy üres sztringet írtak.

Az adatbázistáblát sokkal inkább hasonlíthatjuk egy polcos szekrényhez, mint egy kockás papírhoz. Minden polcon (rekordban) dobozok (mezők) vannak, amelyek lehetnek üresek (NULL van bennük) vagy tartalmazhatnak egy papírt, amire fel van írva egy érték. Az üres doboz (NULL) és az üres papírt tartalmazó doboz ('') nem ugyanaz!

Amikor ORDER BY záradékkal látjuk el a lekérdezésünket, a kitöltetlen mezőket tartalmazó rekordok vagy legelöl vagy leghátul fognak szerepelni adatbáziskezelőtől függő módon. Ha definiálni akarjuk, hogy hova kerüljenek, akkor sajnos nincs egységes módszerünk. Nézzünk meg néhány lehetőséget!

CASE: Standard SQL megoldás (Oracle, MySQL, MS SQL, PostgreSQL...)

SELECT * FROM dolgozok
ORDER BY
  CASE
    WHEN dolgozo_nev IS NULL THEN 1
    ELSE 0
  END
  ,dolgozo_nev

Most két érték szerint rendezünk, a CASE eredménye szerint és azután a dolgozo_nev mező tartalma szerint.A CASE kifejezés 1-et szolgáltat, ha NULL van a mezőben és 0-t ha nem, ezért a NULL-ok a lista végére kerülnek. Ha a lista elejére szeretnénk tenni őket, akkor nyilván a CASE-ben fel kell cserélni az 1-et és a 0-t.

IIF: MS Access

SELECT *
FROM dolgozok
ORDER BY IIF(dolgozo_nev IS NULL,1,0), dolgozo_nev

Itt pontosan ugyanaz történik, mint a fenti megoldásnál, csak a szintaktika más, mert az Accessnél így kell a feltételes kifejezést leírni.

ISNULL: MySQL

SELECT * FROM dolgozok
ORDER BY ISNULL(dolgozo_nev),dolgozo_nev

Az ISNULL függvény visszatérő értéke 1, amikor NULL van a mezőben és 0 amikor nem. Ennek a megoldásnak az az előnye, hogy rövidebb, áttekinthetőbb – a hátránya csak annyi, hogy nem hordozható (ha más adatbáziskezelő alá akarjuk átvinni az adatainkat, akkor át kell alakítani a lekérdezést).

NULLS FIRST, NULLS LAST: Oracle

SELECT * FROM dolgozok
ORDER BY dolgozo_nev NULLS LAST

Ez a legszebb megoldás, kár, hogy a többi említett adatbáziskezelő nem támogatja (legalábbis egyelőre).

Itt arról lesz szó, hogy a NULL milyen módokon jut be az adatbázisba, illetve hogy miként alakul át adatbevitel közben.

Az átváltozás kétféle lehet:

1. Az adatbeviteli program NULL-t próbál bevinni, de valami más (egy DEFAULT) érkezik meg.
2. Az adatbeviteli program valamely nem NULL értéket próbál bevinni, mégis NULL érkezik meg.

Vegyük most sorra a különböző eseteket.

Az első, triviális eset az, amikor egy INSERT vagy UPDATE utasítás explicit módon NULL-t visz be, pl.:

    INSERT INTO t1 (csaladinev,  utonev,  elso_mhely,mostani_mhely)
             VALUES('Ismeretlen','István','SQL Kft', NULL)
    

Ha a mező definícióját megváltoztatjuk úgy, hogy ne legyen itt megengedve NULL, akkor ez az INSERT nem fut le, hibajelzést kapunk, ami jó dolog! Azért jó, mert értesülünk róla, hogy módosítani kell az adatbeviteli programot.

A második (implicit) eset az, amikor az INSERT utasítás nem rendelkezik olyan mezőről, amelyben megengedett a NULL:

A mostani_mhely mezőről ez az utasítás nem rendelkezett; ilyenkor a mezőre érvényes DEFAULT kerül be a rekordba.

A DEFAULT értéket megadhatjuk a tábladefinícióban mi magunk, ha ezt nem tesszük meg, akkor az adatbáziskezelő az alapértelmezett DEFAULT-ját használja. Ez NULL olyan mezőknél, ahol megengedett a NULL, különben pedig adatbázisfüggő, MySQL-nél numerikus mezőknél 0, szöveges mezőknél üres sztring. Azért mondhatjuk, hogy implicit módon settenkedik be a NULL, mert nem az adatbeviteli program teszi oda, hanem az adatbáziskezelő.

Ha tehát az INSERT...VALUES utasítás nem rendelkezik egy mezőről, akkor oda bekerülhet NULL.

A harmadik eset az, amikor egy táblát INSERT...SELECT utasítással töltünk fel egy másik táblából, mint például evvel a MySQL paranccsal:

Ha a két tábla definíciója megegyezik, akkor a NULL-ok is átkerülnek változatlanul; ám ha t2-ben pédául mostani_mhely definíciójában NOT NULL megkötés van, akkor most is a DEFAULT (pl. üres sztring) fog bekerülni, hasonlóan az előző esethez. A NULL tehát itt is átváltozott.
MySQL-nél – sajnos – ilyenkor mindig az alapértelmezett DEFAULT íródik be, akkor is, ha mi erre a mezőre valami mást definiáltunk. Tehát pl. numerikus mezőnél a NULL helyett 0 fog bekerülni, akkor is, ha erre a mezőre a tábladefinícióban 999-et adtunk meg DEFAULT-ként. Ez bizony kellemetlen meglepetéseket tud okozni.

Fontos megjegyezni azt is, hogy az INSERT...SELECT utasítás működése, sőt, szintaktikája sem szabványos, hanem adatbáziskezelő-függő.

A negyedik, az előzőhöz hasonló eset az, amikor egy táblát egy szöveges delimitált (CSV) fájlból töltünk fel. MySQL esetében a parancs valahogy így néz ki:

A MySQL a CSV fájlban található \N-t alakítja át úgy, hogy a mezőbe NULL-t tesz. Már ha az ott megengedett – ha nem, akkor itt is ugyanaz történik, mint a fenti esetben: az alapértelmezett DEFAULT kerül be (akkor is, ha a tábladefinícióban mást adtunk meg). A NULL most is átváltozott. (Ugyanez persze más adatbáziskezelőknél is előfordul, csak a szintaktika különbözik.)

Az ötödik, MySQL-specifikus eset az, amikor egyetlen INSERT utasítással több sort viszünk be egy táblába:

CREATE TABLE t1(f1 INT NOT NULL);

INSERT INTO t1 VALUES(1),(2),(NULL);

Sajnos most is az alapértelmezett DEFAULT érték (azaz 0) kerül be a táblába, hibajelzés nélkül, bár egy figyelmeztetést azért kiad a MySQL, amelyet SHOW WARNINGS utasítással le tudunk kérdezni. Hogy még tovább bonyolódjon a helyzet, be lehet állítani olyan működési módot, hogy a csoportos INSERT-nél is hibajelzést okozzon ilyenkor a NULL bevitelének kísérlete:

A NULL 'csendes' átkonvertálása nagyon veszélyes dolog, mert nem kívánt értékek jelenhetnek meg váratlanul és később nehezen felderíthető módon az adatbázisunkba.

Tanulság: A bemenő adatokat mindig ellenőrizni kell. Az adatbázisba írás után már sokkal nehezebb lehet az ellenőrzés – többek között például a mágikusan bújócskázó és átváltozó NULL-ok miatt.

A hatodik – szintén MySQL-specifikus – eset az, amikor UPDATE segítségével próbáljuk meg NULL-ra állítani egy olyan mező tartalmát, melyet NOT NULL megkötéssel definiáltunk:

Itt is az alapértelmezett DEFAULT kerül be és csak WARNING-ot eredményez, kivéve, ha a fent bemutatott utasítással beállítjuk a 'szigorú' TRADITIONAL működési módot.

A hetedik eset, amikor nulla hosszúságú sztringet akarunk bevinni, de az adatbáziskezelő ezt nem támogatja és ehelyett NULL-t illeszt be. Ez a helyzet pl. az Oracle-nél.

Előfordul, hogy egy mező értékét fel akarjuk használni valamilyen kifejezésben, ám ha a mezőben NULL van, akkor a kifejezés értéke is NULL lesz. Példát erre itt találhatunk: amikor az egyik mezőben NULL van, akkor az összeg helyett NULL jelenik meg az eredményben. Azt szeretnénk megoldani, hogy amennyiben NULL van a mezőben, akkor valamilyen érték (pl.0) kerüljön bele a kifejezésbe, ha nem NULL, akkor pedig maga az érték.

Erre létezik több megoldás is, de sajnos mindegyik adatbáziskezelőnél egyforma szintaktikájú nem.

Speciális NULL-átalakítók

Ezek a függvények elsősorban arra szolgálnak, hogy a mező értékét adják ki, ha az nem NULL, illetve az általunk specifikált értéket, ha NULL.

COALESCE(): Oracle, MySQL, MS SQL

Fontos megjegyezni, hogy a COALESCE függvény létezik ugyan mindhárom adatbáziskezelőnél, de a használatában vannak különbségek! (Ebben, az itt bemutatott legegyszerűbb esetben nincsenek.)

Ez lesz a kimenet:

Ez történik itt:

NULL  --> COALESCE --> 0
érték --> COALESCE --> változatlan érték

A fentebbi megfogalmazás egyébként nem pontos: ha az illető mező nem NULL, akkor kiadhatjuk a mező tartalmát, de akármilyen más kifejezést is, pl.:

SELECT csaladinev,utonev,
       COALESCE(0.9 * jutalom_tavaly,0) +
       COALESCE(0.8 * jutalom_iden,0) AS osszjutalom
FROM t1

(A példa persze nem túl értelmes, csak szemléltetésre szolgál.)

NVL(), IFNULL, ISNULL

NVL(): Oracle

IFNULL(): MySQL

ISNULL(): MS SQL, MS Access

A kimenet ugyanaz lesz, mint a COALESCE-es megoldásnál.

Általános feltételes elágazások használata

Természetesen az általános (tehát nem NULL-specifikus) feltételes elágazásokat is használhatjuk, bár így komplikáltabb megoldáshoz jutunk. Itt olyan példát mutatok az egyszerűség kedvéért, ahol csak egyetlen mezőt vizsgálunk.

CASE(): Oracle, MySQL, MS SQL

SELECT csaladinev,utonev,
       CASE
         WHEN mostani_mhely IS NULL THEN 'kitöltetlen'
         ELSE mostani_mhely
       END AS mostani
FROM t1

IIF(): MS Access, MS SQL

SELECT csaladinev,utonev,
       IIF(mostani_mhely IS NULL,'kitöltetlen',mostani_mhely) AS mostani
FROM t1

Amikor két tábla között létrehozunk kapcsolatot, gyakran előfordul, hogy az egyik tábla bizonyos rekordjainak nincs párja a másikban. Itt vannak például egy vállalat részlegei:

és néhány dolgozója:

Mindenki legfeljebb egy részleghez tartozik, ezért elegendő a reszleg_id mező, nincs szükség kapcsolótáblára. Teknőc Ernő a kézbesítő a cégnél, ő egyik részleghez sincs hozzárendelve. A dolgozókat és részlegüket könnyen ki tudjuk listázni:

SELECT dolgozo_id,dolgozo_nev,reszleg_nev
FROM dolgozok
LEFT JOIN reszlegek ON
          dolgozok.reszleg_id = reszlegek.reszleg_id

A lekérdezés eredménye ez lesz:

A cégnél karácsonyi ünnepséget tartanak külső helyszínen és közzétesznek egy listát arról, hogy kinek hol kell gyülekeznie indulás előtt. A szabály egyszerű: mindenkinek a saját részlegénél. A fenti lekérdezés ennek a listának az elkészítésére nem alkalmas, mert szegény Teknőc Ernőt csúnyán elküldené a bánatos NULL-ba. A szervezők úgy döntenek, hogy a marketing osztályon kell gyülekezniük mindazoknak, akik nincsenek részleghez rendelve – olyan lekérdezést kell tehát írnunk, amelyben a NULL-t a reszleg_id mezőben a marketingesek azonosítójára (10-re) cseréljük le.

Szó volt már a NULL átalakítókról nosza vegyük elő például a CASE elágazást:

SELECT dolgozo_id,dolgozo_nev,reszleg_nev AS gyülekezés
FROM dolgozok
INNER JOIN reszlegek ON
           reszlegek.reszleg_id =
           CASE
             WHEN dolgozok.reszleg_id IS NULL THEN 10
             ELSE dolgozok.reszleg_id
           END

vagy a COALESCE függvényt:

SELECT dolgozo_id,dolgozo_nev,reszleg_nev AS gyülekezés
FROM dolgozok
INNER JOIN reszlegek ON
           COALESCE(dolgozok.reszleg_id,10) = reszlegek.reszleg_id

Itt már INNER JOIN-t használhattunk, mert az összekapcsoló érték sohasem NULL az átalakítás miatt, így Teknőc Ernő is eljut a karácsonyi ünnepségre. (Bár így is morog, hogy inkább a könyvelősökkel szeretett volna utazni, de ne is törődjünk vele, ő már csak ilyen zsörtölődős fajta.)

Persze nemcsak a NULL-t lehet lecserélni a táblák közötti kapcsolat módosítása céljából, hanem bármely más értéket is. Tegyük föl például, hogy a karácsonyi buli előtt a könyvelésben dolgozóknak is a tervezési osztályon kell gyülekezniük; ezt így oldhatjuk meg:

SELECT dolgozo_id,dolgozo_nev,reszleg_nev AS gyülekezés
FROM dolgozok
INNER JOIN reszlegek ON
           reszlegek.reszleg_id =
           CASE
             WHEN dolgozok.reszleg_id IS NULL THEN 10
             WHEN dolgozok.reszleg_id = 20 THEN 30
             ELSE dolgozok.reszleg_id
           END

Így persze a könyvelők fognak zúgolódni, hogy semmi kedvük a tervezőkkel vegyülni, részlegeskedjenek azok Karácsonykor mással – de ezt a problémát már nem tudjuk az SQL keretein belül megoldani.

És végül érdemes megjegyezni, hogy NULL-átalakítás nélkül, UNION segytségével is megoldható Teknőc Ernő problémája:

SELECT dolgozo_id,dolgozo_nev,reszleg_nev AS gyülekezés
FROM dolgozok
INNER JOIN reszlegek ON dolgozok.reszleg_id = reszlegek.reszleg_id
UNION
SELECT dolgozo_id,dolgozo_nev,reszleg_nev AS gyülekezés
FROM dolgozok
LEFT JOIN reszlegek ON reszlegek.reszleg_id = 10
WHERE dolgozok.reszleg_id IS NULL

Az első SELECT azokat válogatja le, akik hozzá vannak rendelve valamelyik részleghez, a második pedig azokat, akik nem. Ennek a megoldásnak az előnye, hogy akár külön hirdetménybe tudjuk tenni az utóbbi csoportot, ha akarjuk.