Többtáblás lekérdezések – SQL

Adatbázis – SQL

Lekérdezések – SQL

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

1. Kapcsolódó mezők: A dolgozo és az osztaly táblák az oid mezőn keresztül kapcsolódnak.
2. Feltételek: A WHERE feltétel szűkíti az eredményt, például csak veszprémi dolgozókat mutat meg.
3. 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?

1. A dolgozo tábla és az osztaly tábla oid mezője alapján összekapcsolódik.
2. Csak azokat a dolgozókat listázza, akik Veszprémből származnak.
3. 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’ )

• Kiválasztja azokat a dolgozókat, akik ugyanahhoz az oid-hoz (osztályhoz) tartoznak, mint „Fehér Alajos”.
• Az allekérdezés (SELECT oid FROM dolgozo WHERE nev = 'Fehér Alajos') visszaadja az oid értékét, amely az „Fehér Alajos”-hoz tartozik.
• A WHERE d.oid = (...) feltétel ezt az oid értéket használja, hogy megtalálja azokat a dolgozókat, akik ugyanabban az osztályban vannak, mint „Fehér Alajos”.

  AND d.nev != ‘Fehér Alajos’; – kizárja Fehér Alajost az eredmények közül. 

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;