Az SQL-ben a többtáblás lekérdezések lehetővé teszik, hogy különböző táblák adatait kombináljuk és egységes eredményként jelenítsük meg. Ehhez az JOIN utasítást használjuk. Nézzük meg részletesen, hogyan működnek ezek a lekérdezések.
1. INNER JOIN – Csak az egyező sorok
Az INNER JOIN segítségével csak azok a sorok kerülnek be az eredménybe, amelyeknél a két tábla közötti feltétel teljesül.
Példa: SELECT dolgozo.*, osztaly.onev FROM dolgozo INNER JOIN osztaly ON dolgozo.oid = osztaly.oid WHERE varos = 'Veszprém';
dolgozo: Ez a dolgozók adatait tartalmazza.osztaly: Ez a dolgozók osztályát tartalmazza.ON dolgozo.oid = osztaly.oid: Ez a kapcsolódási feltétel (a dolgozó és az osztály kapcsolatát adja meg).WHERE varos = 'Veszprém': Csak a veszprémi dolgozókat listázza ki.
Eredmény:
Az eredmény csak azokat a dolgozókat tartalmazza, akiknek van érvényes oid értékük mindkét táblában.
2. LEFT JOIN – Minden az első táblából, hiányzó értékekkel
A LEFT JOIN az első táblából (jelen esetben a dolgozo) minden rekordot megjelenít, akkor is, ha a második táblában (osztaly) nincs hozzá tartozó adat.
SELECT dolgozo.*, osztaly.onev FROM dolgozo LEFT JOIN osztaly ON dolgozo.oid = osztaly.oid WHERE varos = 'Veszprém';
Az eredmény tartalmazza mind a veszprémi dolgozókat, mind azokat, akiknek nincs osztályuk (az onev mezőjük NULL lesz).
3. RIGHT JOIN – Minden a második táblából
A RIGHT JOIN ritkábban használt, de fordított logikát követ: a második táblából minden rekordot megjelenít, akkor is, ha az első táblában nincs hozzá tartozó adat.
SELECT dolgozo.*, osztaly.onev FROM dolgozo RIGHT JOIN osztaly ON dolgozo.oid = osztaly.oid;
Magyarázat a példák alapján
- Kapcsolódó mezők: A
dolgozo és az osztaly táblák az oid mezőn keresztül kapcsolódnak.
- Feltételek: A
WHERE feltétel szűkíti az eredményt, például csak veszprémi dolgozókat mutat meg.
- NULL értékek: A
LEFT JOIN esetében a második táblából hiányzó adatok helyett NULL érték jelenik meg.
Gyakorlatban
- INNER JOIN: Használd, ha csak az egyező rekordokra van szükséged.
- LEFT JOIN: Használd, ha az első tábla összes rekordjára szükséged van, de a második táblából csak a meglévő adatok érdekelnek.
- RIGHT JOIN: Használd, ha fordított logikával dolgozol (ritkábban használt).
Feladatok
1. Listázd ki a veszprémi dolgozók nevét és osztálynevét
SELECT d.nev, o.onev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.varos = 'Veszprém';
Ez a lekérdezés egy többtáblás SQL lekérdezés, amely két táblát – dolgozo és osztaly – kapcsol össze az oid mezőn keresztül. Lássuk részletesen, miért van szükség a d.nev, o.onev, valamint a kapcsolás (ON d.oid = o.oid) megadására:
1. Miért kell d.nev és o.onev?
Amikor két vagy több táblát kapcsolsz össze, a lekérdezés során a mezők nevét egyértelműen meg kell határozni, hogy a rendszer tudja, melyik táblából hivatkozol az adott mezőre.
-
d.nev: Ez azt jelenti, hogy a nev mezőt a dolgozo táblából szeretnéd használni. Az alias (d) egy rövidítés, amelyet a dolgozo tábla azonosítására hoztál létre. Ha csak nev-et írnál, a rendszer nem tudná, hogy a nev mezőt a dolgozo vagy az osztaly táblából kéred (ha esetleg mindkét táblában van ilyen nevű mező).
-
o.onev: Ugyanígy, ez azt jelenti, hogy a onev mezőt az osztaly táblából szeretnéd használni. Az alias (o) itt is segít az osztaly tábla egyértelmű azonosításában.
Alias használat előnyei:
- Rövidebb, áttekinthetőbb kódot írhatsz (pl.
d helyett nem kell mindig dolgozo-t kiírni).
- Egyértelművé teszi, hogy melyik táblából származik az adott mező.
2. Miért kell az ON d.oid = o.oid?
Ez a lekérdezés INNER JOIN típusú kapcsolást alkalmaz, ami azt jelenti, hogy csak azokat a sorokat adja vissza, amelyek mindkét táblában egyeznek a megadott feltétel szerint.
d.oid: A dolgozo táblában az oid mezőt használjuk az osztály azonosítására.o.oid: Az osztaly táblában az oid mező az osztály azonosítója.
Az ON d.oid = o.oid feltétel azt mondja meg a rendszernek, hogy csak azokat a dolgozókat kapcsolja össze a megfelelő osztályokkal, ahol a két tábla oid értékei megegyeznek.
Kapcsolás nélkül: Ha az ON feltételt kihagynád, a rendszer nem tudná, hogyan kapcsolja össze a táblákat, és minden sor minden sorral párosítva jelenne meg (ezt cross join-nak hívjuk), ami hibás eredményt adna.
3. Mit csinál a teljes lekérdezés?
- A
dolgozo tábla és az osztaly tábla oid mezője alapján összekapcsolódik.
- Csak azokat a dolgozókat listázza, akik Veszprémből származnak.
- A visszaadott eredmény tartalmazza:
- A dolgozó nevét a
dolgozo táblából (d.nev).
- Az osztály nevét az
osztaly táblából (o.onev).
2. feladat: Listázd ki csak azokat a dolgozókat, akik "Veszprém" városban dolgoznak, az osztály nevükkel együtt!
SELECT d.nev, d.fizetes, o.onev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.varos = 'Veszprém';
3. feladat: Listázd ki az összes osztályt és az ahhoz tartozó dolgozók számát!
SELECT o.onev, COUNT(d.szigszam) AS dolgozok_szama FROM osztaly o LEFT JOIN dolgozo d ON o.oid = d.oid GROUP BY o.onev;
Azért használtam itt LEFT JOIN-t, hogy kiadja azokat az értékeket is, ahol nincs hozzárendelve senki (pl egy olyan osztályt, ahol nincs alkalmazott)
4. feladat: Listázd ki azokat az osztályokat, amelyekhez még nem tartozik dolgozó!
SELECT o.onev FROM osztaly o LEFT JOIN dolgozo d ON o.oid = d.oid
WHERE d.oid IS NULL
5. feladat: Listázd ki a legmagasabb fizetéssel rendelkező dolgozót minden osztályban!
SELECT o.onev, d.nev, MAX(d.fizetes) AS max_fizetes FROM osztaly o INNER JOIN dolgozo d ON o.oid = d.oid GROUP BY o.onev;
6. feladat: Listázd ki a dolgozókat, akik ugyanazon az osztályon dolgoznak, mint Fehér Alajos
SELECT d.nev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.oid = ( SELECT oid FROM dolgozo WHERE nev = 'Fehér Alajos' ) AND d.nev != 'Fehér Alajos';
Részekre bontva: SELECT d.nev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid - a dolgozó táblából kilistázza a neveket és összekapcsolja az osztály táblával, így mindkét tábla értékei elérhetőek ebben a lekérdezésben
WHERE d.oid = ( SELECT oid FROM dolgozo WHERE nev = 'Fehér Alajos' )
7. feladat: Listázd ki a dolgozók városait, de csak egyszer jelenjen meg minden város!
SELECT DISTINCT varos FROM dolgozo;
8. feladat: Listázd ki azokat a dolgozókat, akiknek a fizetése meghaladja az osztályuk átlagfizetését!
SELECT d.nev, d.fizetes, o.onev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.fizetes > ( SELECT AVG(d2.fizetes) FROM dolgozo d2 WHERE d2.oid = d.oid );
9. feladat: Listázd ki az osztályokat, amelyekben több mint 2 dolgozó van!
SELECT o.onev, COUNT(d.szigszam) AS dolgozok_szama FROM osztaly o INNER JOIN dolgozo d ON o.oid = d.oid GROUP BY o.onev HAVING COUNT(d.szigszam) > 2;
