6 olyan sor volt a táblában, ahol a field1 mező NULL, a COUNT(mezőnév) függvény ezeket nem veszi figyelembe. COUNT(*) mindig az összes rekordok számát szolgáltatja, COUNT(mezőnév) pedig azokét, ahol a mező nem NULL.

Tanulság: Ha azon sorok számára vagyunk kíváncsiak, ahol egy mező ki van töltve, akkor az itt bemutatott módon is megtehetjük, felesleges a NULL értékek explicit kizárása:

0 lesz az eredmény, bár van egy olyan sor, ahol a status mezőben NULL található. A bajt az okozza, hogy a COUNT(mezőnév) függvény definíciószerűen csak azon sorok számát adja vissza, ahol a mező NULL-tól különbözik – tehát már maga a lekérdezett alap-halmaz:

nem tartalmaz olyan sorokat, amelyekben a status mező NULL, ezt aztán hiába próbáljuk a

feltétellel tovább szűrni, eddigre a NULL-ok már rég meglógtak. Helyesen így kell írni:

SELECT COUNT(*) FROM t1 WHERE status IS NULL

mert a COUNT(*) függvény viszont mindig a feltételek által kiválasztott összes sor számát adja vissza.

A válasz az, hogy igen, a SELECT DISTINCT a NULL értékeket is figyeli, a kimenet ez lesz:

Igen, a GROUP BY figyelembe veszi a NULL-t is – ám a kimenet ez lesz:

Hát ez gyönyörű. Látszik, hogy van olyan rekord, amelyben a status mező tartalma NULL, különben nem lenne ott az első sor – ám azt is mondja nekünk az SQL, hogy van ugyan ilyen rekord, de nullaszor (!!) fordul elő.

Miután kidühöngtük magunkat, próbáljunk meg rájönni, miért kaptuk ezt a gúnynak is beillő választ az SQL-től.

A fenti példában már egyszer beleütköztünk a COUNT(mezőnév) függvénynek abba a tulajdonságába, hogy csak a nem NULL mezőket tartalmazó rekordokat számolja. Itt is ez történik: először csoportosítja az adatbáziskezelő a rekordokat a status mező tartalma szerint, kap három csoportot. Ezután elvégzi az egyes csoportokon belül a számolást, felhívja a COUNT(status) függvényt; ez viszont a NULL-os csoportban egyetlen rekordot sem talál, mert ő arra van betanítva, hogy csak a nem-NULL értékeket számolja.

Helyesen persze így kell írni:

SELECT status, COUNT(*) AS N FROM t1
GROUP BY status

A kimenet így már sokkal szimpatikusabb lesz:

Buzgó Mócsing lesz az eredmény, tehát egyetlen sornál ítéli úgy meg az SQL, hogy az első munkahely nem egyezik meg a másodikkal — pedig azt vártuk volna, hogy az utolsó sor is ilyen. Hiszen az első munkahely az SQL kft, a második pedig NULL, vagyis nem egyeznek. Dehát akkor mit is mond az SQL? Talán csak nem azt, hogy megegyeznek?

Nem, ő ezt mondja: A harmadik sornál azt kérdezték tőlem, egyenlő-e elso_mhely és mostani_mhely? Erre nemmel kell válaszolnom, mert mostani_mhely ismeretlen. Persze akkor is nemmel válaszolnék, ha azt kérdezték volna, hogy egyenlőek-e (a szabályt ld. itt).

Vajon helyesen hogy néz ki a parancs?

Ennek megválaszolásához először is el kell döntenünk, hogy pontosan mi is a kérdés. Ha a főnökünk bízott meg ezzel a feladattal, vissza kell kérdeznünk: Mi legyen a nem ismert adatokkal? Ugyanis ha a precízebben megfogalmazott feladat így hangzik: 'Hány személy van, akinek ismerjük mindkét munkahelyét és ezek nem egyformák?' - akkor jó a fenti megoldás. Ha ellenben ez a követelmény: 'Hány személy van akinél az első és a jelenlegi munkahely bejegyzése különbözik egymástól?' - akkor így kell eljárnunk:

SELECT csaladinev,utonev FROM t1
WHERE elso_mhely <> mostani_mhely OR
(elso_mhely IS NULL AND mostani_mhely IS NOT NULL) OR
(elso_mhely IS NOT NULL AND mostani_mhely IS NULL)

Vagyis: azok a rekordok kellenek, ahol

• mindkét mező ki van töltve és nem egyformák vagy
• az első mező NULL, a második nem vagy
• a második mező NULL, az első nem.

Persze ennek gyakorlatilag nem sok értelme van, az első kérdésfeltevés sokkal reálisabb, csak éppen semmit nem tudunk meg azokról, akiknél valamelyik munkahely kitöltetlen. Értékelhető képet akkor kapunk, ha két lekérdezést futtatunk: Az egyik a kérdésben szereplő, a másik pedig azt adja meg, hogy kiknél kitöltetlen legalább az egyik munkahely-mező:

SELECT csaladinev,utonev FROM t1
WHERE elso_mhely IS NULL OR mostani_mhely IS NULL

Láthattuk tehát, hogy az SQL logikusan jár el, amikor az eredményhalmazból kizárja mindazon sorokat, ahol NULL szerepel egy összehasonlításban. Pedig első hallásra nagyon ésszerűtlennek tűnt ez a szabály...

Azt várnánk, hogy Ismeretlen Istvánnál 320000 lesz az összeg, hiszen csak az előző évi értéket ismerjük, de nem, az eredmény helyesen ez lesz:

Azért helyes ez az eredmény, mert

Ha a NULL helyett nullát szeretnénk az összegbe bevinni, akkor be kell vetnünk valamelyik erre szolgáló (és sajnos nem szabványos, adatbáziskezelőnként más és más) függvényt. Ezekről itt olvashatunk.

Természetesen nem kapunk hibajelzést, az eredmény ez lesz:

Nem történik NULL-lal osztás, hiszen amikor az SQL azt látja, hogy egy aritmetikai művelet valamelyik operandusa NULL, akkor nem számol semmit, hanem csak közli flegmán, hogy az eredmény definiálatlan, azaz NULL.

És milyen igaza van!

Ezt fogjuk kapni:

Azt vártuk volna, hogy az utolsó sorban ez látható majd:

hiszen itt az első munkahely ki van töltve, a második nem, ahelyett tehát üres sztringet képzeltünk volna el a kimeneten. Ám mivel a sztring-összefűzés egyik operandusa NULL, az eredmény is NULL lesz – akárcsak az aritmetikai műveletek esetében.

A SUM függvény összeadja a kitöltött, tehát nem NULL mezők összegét, a COUNT(*) pedig megadja az összes sor számát. Ha van NULL-t tartalmazó sor, akkor az első lekérdezés nyilván hibás eredményt ad, hiszen nem az összegzett értékek számával osztjuk el az összeget, hanem egy nagyobb számmal. Ha nagyon ragaszkodunk a COUNT és a SUM függvényhez, akkor ezt kellene írnunk:

Itt épp kapóra jön a COUNT(mezőnév) azon tulajdonsága, hogy csak a kitöltött mezőket számolja össze.

Tehát Gyula bácsinak volt igaza, hiszen János eredeti megoldása hibás és még a fenti, módosított változat is bonyolultabb az AVG-t alkalmazó lekérdezésnél – márpedig az egyszerűség, áttekinthetőség érték.

De azért hozzá kell tenni, hogy egyik lekérdezés sem szolgáltat érdemi eredményt önmagában. Ha az volt a kérdés, hogy mennyi az átlagfizetés a cégnél, akkor az egyetlen értelmes válasz valahogy így hangzik:

A dolgozók száma 40, ebből 30-nak van az adatbázisban kitöltve a fizetése,
az ő átlaguk 200000 forint.

Ez persze már nem adatbáziskezelési, hanem adatfeldolgozási probléma, de nagyon fontos tanulság: Mindig pontosan fogalmazzuk meg, hogy a statisztikai adataink mire vonatkoznak!

A NOT IN művelet tulajdonképpen leegyszerüsített írásmódja annak, hogy a mező tartalma nem egyenlő egyik értékkel sem:

Az f1 <> NULL eredménye NULL, ha ezt AND művelettel összekapcsoljuk a többi feltétellel, akkor vagy NULL-t kapunk (az összes többi feltétel teljesült), vagy FALSE-ot (valamelyik nem teljesült). TRUE soha sem lesz. Erről részletesebben itt olvashatunk.

Az Olvasó számára mesterkéltnek tűnhet ez az egész feladat: Miért tennék én az IN művelet operandusai közé egy NULL-t? Valójában ez könnyen előfordulhat, amikor egy program szedi össze az operandusokat és nem vigyáz arra, hogy NULL ne lehessen köztük, vagy egy allekérdezés eredménye szolgáltatja az értékek listáját.

Nézzük meg először, hogy az allekérdezés:

jó-e, a várt eredményt szolgáltatja-e. Azt tapasztaljuk, hogy a kimenet hibátlan:

További ellenőrzésként összerakjuk kézzel a teljes lekérdezést:

SELECT * FROM hallgatok
WHERE lakhely NOT IN (
  'Budapest','Debrecen','Sopron',NULL  
)

Ez is úgy működik, ahogy az eredeti SELECT, nem szolgáltat egyetlen sort sem – eddig rendben, de akkor mégis mi okozhatja a nem várt eredményt?

A tapasztaltabb Olvasók már sejthetik, hol a baj gyökere: ha meglepetés van, akkor szinte biztosak lehetünk benne, hogy egy NULL (Nem Unalmas Lekérdezést Létrehozó) ólálkodik a háttérben. És valóban, az allekérdezés által szolgáltatott listában ott egy NULL, ezáltal létrejött a fentebbi példában bemutatott eset: a NOT IN paraméterlistájába NULL került.

Meg kell jegyezni, hogy az üres kimenet tulajdonképpen jól tükrözi a helyes választ a kérdésre. Nem tudjuk megmondani, hogy van-e olyan hallgató, akinek a városában nincs tanár, mert hiányosak az adataink, márpedig hiányos adatokból csak hiányos vagy hibás válaszokat lehet előállítani. Hiába változtatjuk meg a lekérdezést így:

SELECT * FROM hallgatok
WHERE lakhely NOT IN (
  SELECT DISTINCT lakhely FROM tanarok
  WHERE lakhely IS NOT NULL
)

Megkapjuk ugyan a 103-as számú hallgatót – csak nem biztos, hogy ez az igazság. Hiszen lehet, hogy a 4-es azonosítójú tanár épp Miskolcon lakik!

Ez a megoldás:

SELECT hallgato_id,hallgatok.lakhely
FROM hallgatok
LEFT JOIN tanarok ON hallgatok.lakhely = tanarok.lakhely
WHERE tanar_id IS NULL

Nem lehet elégszer hangsúlyozni: ez nem biztos, hogy érvényes megoldás, mert talán a 4-es azonosítójú tanár épp Miskolcon lakik. Hiányos adatokból csak hiányos választ lehet előállítani. Hiába no, van, amiből lehet várat építeni, van, amiből nem...

A UNIQUE megkötés azt jelenti, hogy két kitöltött mező értéke nem lehet egyforma. Ha NULL-t tartalmaz a mező, akkor nincs kitöltve.

Sikerülni fog az adatbevitel, a tábla így fog kinézni:

Hát kérem, ide becsempésztek két értéket! Ki tehette vajon?

Mivel más nem járt erre, csak az adatbáziskezelő lehetett a tettes. Na de honnan találta ki, hogy mit írjon oda? És hogyan tudnánk megmondani neki, hogy mást írjon?

Ha egy bemeneti mező nincs specifikálva, akkor az adatbáziskezelő az alapértelmezett (default) értéket teszi oda. Amennyiben a meződefinícióban külön nem rendelkeztünk róla, akkor a default érték default-ja kerül be, ami

• NULL-t megengedő mezőknél NULL
• a NULL-t tiltó szöveges mezőknél a MySQL esetében üres sztring, INT mezőnél nulla, stb.
• és figyelem: default értékek default-ja adatbáziskezelő-, sőt, verziófüggő is lehet!

Megadhatunk a mező definíciójában persze más default értéket:

CREATE TABLE t1(
csaladinev    VARCHAR(20),
utonev        VARCHAR(20),
elso_mhely    VARCHAR(20) NOT NULL DEFAULT '[semmi]',
mostani_mhely VARCHAR(20) NULL
);

Ha ilyen lett volna a tábladefiníció, akkor a fenti INSERT hatására ez került volna be az adatbázisba:

Az első fontos tanulság az, hogy akkor is bekerülhet valami az adatbázisba, ha mi külön nem rendelkezünk róla és ez a 'valami' a default érték. A NOT NULL megkötés önmagában csak az ilyen adatbevitel ellen véd meg:

tehát amikor egy program explicit módon NULL-t kísérel meg bevinni. Ha a program nem rendelkezik az illető mezőről, akkor NULL ugyan nem jut be, később esetleg hibákat okozó default érték viszont igen.

Megállapíthatjuk, hogy az adatbeviteli programoknak nagyon nagy szerepe van az adat-integritás megőrzésében, az adatbázis-megkötések egyedül nem képesek erre. A NULL-lal és a default értékekkel vívott hősies küzdelemről részletesebben itt olvashatunk.

A második tanulság az, hogy a default értéknek is van default-ja, ami meglepetéseket okozhat, mivel nem jól látható, a háttérből ugrik elő hirtelen. Célszerű itt is a maximális szájbarágás elvét követni és a meződefinícióban a default értéket explicit módon megadni.

A nev mezőt nem specifikáltuk a bemeneten, ezért oda a DEFAULT értéket próbálta meg beilleszteni az adatbáziskezelő, ami nyilván mindig ugyanaz, ezért másodszor már nem sikerülhetett a művelet. (PRIMARY KEY esetén nem lehet a mezőben NULL, tehát biztosan nem NULL az alapértelmezett érték.)

Ez a megoldás:

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

A kimeneten ezt látjuk:

Valóban azt kaptuk, amit vártunk, csak az első számla tételei jelennek meg, mert a másodikhoz egy sem tartozik.

Valóban, a János által javasolt megoldás (amelyet implicit JOIN-nak hívnak egyébként) és az INNER JOIN-nal felépített megoldás:

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

ekvivalensek. Végrehajtási idő szempontjából sincs közöttük különbség; ízlés kérdése, hogy melyiket használjuk. A JOIN-os változat szerintem könnyebben érthető, különösen akkor, amikor a WHERE záradék további bonyolult kifejezéseket tartalmaz. Az INNER JOIN részben külön vannak választva az összekapcsoláshoz szükséges feltételek, ez javítja az olvashatóságot.

Igen, a két megoldás ekvivalens, esetleg sebességbeli különbség lehet közöttük, ami persze adatbáziskezelőtől és verziótól függhet, és nagy valószínűséggel ha egyáltalán létezik is ilyen különbség, nem nagy mértékű.

A két megoldás között elsősorban stilisztikai különbség van: szerintem az első jobban olvasható, mert az összekapcsoláshoz szükséges feltétel és a leválogató feltétel élesen szétválik. Bonyolultabb feltételek esetén az olvashatóságbeli különbség sokkal nagyobb lehet, mint ebben az egyszerű példában.

Meg kell jegyezni, hogy MS Access esetében a második megoldásnál a teljes feltételt zárójelbe kell tenni:

különben szintaktikai hibajelzést kapunk. Ha ennek az okát kérdezi az Olvasó, akkor csak azt tudom válaszolni: Azért van ez így, mert így sikerült nekik.

Ez a megoldás (ha a NULL-ok helyett nem akarunk valami mást kiíratni):

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

A kimeneten ezt látjuk:

Valóban azt kaptuk, amit vártunk, az első számlához két tétel tartozik, a másodikhoz egy sem.

Ez a megoldás:

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

RIGHT JOIN-nal is megfoglamazhatjuk persze (mint mindig):

SELECT szamlasorok.szamla_id,sor_id,megnevezes
FROM szamlak
RIGHT JOIN szamlasorok ON
           szamlak.szamla_id = szamlasorok.szamla_id
WHERE szamlak.szamla_id IS NULL

1. eset. Az eredmény ez lesz:

Egy egyszerű keresztszorzat jött létre, az egyik tábla összes sora összekombinálódott a másik tábla összes sorával. A LEFT JOIN a definíciója szerint kiadja a baloldali tábla összes sorát és

• ezekhez párosítja a másik táblának azon sorait, amelyekre teljesül az összekapcsolási feltétel;
• ha pedig a jobboldali táblából egyetlen sor sem felel meg az ON feltételnek, akkor a jobboldali tábla mezői helyett NULL fog állni a kimeneten.

Most minden sorra teljesült a feltétel, ezért kapjuk a baloldali tábla összes sorát kombinálva a jobboldali tábla összes sorával.

2. eset. Az eredmény ez lesz:

Most sehol sem teljesült a feltétel, ezért kapjuk a baloldali tábla összes sorát egy-egy NULL kíséretében.

Mivel ugyebár tudjuk, hogy a LEFT JOIN hogy működik, ezért könnyű a válasz.

Az első esetben a WHERE feltétel megszűri a kimenetet, csak azon rekordok maradnak meg, melyeknél 'cucc 2' áll a számlasor megnevezésében.

A második esetben nincs WHERE feltétel, ezért a lekérdezés kiadja a szamlak tábla ÖSSZES sorát. Mivel csak a 'tegnapi' számlához tartozik 'cucc 2', ezért fog a 'mai' számlánál NULL állni a harmadik oszlopban.

És akkor most térjünk rá arra a kérdésre, hogy melyik megoldás a helyes. Ez nyilván attól függ, hogy mire vagyunk kíváncsiak, akarjuk-e látni azon számlákat is, amelyekhez nem tartozik 'cucc 2' megnevezésű számlasor. Ha LEFT JOIN-t írtunk, akkor feltehetőleg igenis látni akarjuk ezeket is, tehát az ON-ba írt feltétel a helyes. Az első megoldás igazából félrevezető, mert azokat a rekordokat adja ki, mint egy INNER JOIN. Ha ezekre vagyunk kíváncsiak, írjunk INNER JOIN-t a félreértések elkerülése érdekében!

Erre a lekérdezésre van szükségünk:

SELECT auto_id,marka,tipus_id,tipusnev
FROM autok
LEFT JOIN tipusok ON
          tipus_id = id_egyik OR
          tipus_id = id_masik

Nyilvánvalóan nem INNER JOIN-t kell használnunk, mert akkor eltűnnének azok az autók a kimenetről, amelyeknek a típuskódja nem található meg a tipusok táblában. A kimenet:

A szamlasorok táblában ez a tartalom is lehetett:

Van egy számlasor, ami a 202-es számlához tartozik, csak éppen NULL a megnevezése. Ez persze arra mutat, hogy nem tökéletes az adatbázisszerkezet és az adatbeviteli program sem áll a helyzet magaslatán – ami a valóságban sajnos nem egyszer előfordul.

A táblanevekhez rendelhetünk helyettesítő nevet (alias), amit az AS kulcsszóval vagy anélkül odaírhatunk a táblanév után. A LEFTT-et úgy vette az SQL fordító, mintha a t1 neveként definiáltam volna, a JOIN-nak pedig az INNER az alapértelmezett változata, ezt nem kell kiírni. Ilyennek tekintette a SELECT parancsomat:

SELECT f1,f2 FROM t1 AS LEFTT
INNER JOIN t2 ON f1 = f2