JOIN-ok — táblák összekapcsolása

1Miért kell a JOIN?

A jól megtervezett adatbázisban az adatok külön táblákban élnek (a vásárló a vasarlo-ban, a rendelése a rendeles-ben). Ha egy kérdés több táblát is érint — pl. „melyik vásárló mikor rendelt?” —, akkor ezeket össze kell kapcsolnunk. Erre való a JOIN: a kapcsolódó táblák sorait egy közös érték mentén párosítja, és egyetlen, széles eredménytáblát ad.

2Hogyan kapcsolódnak a táblák?

A JOIN-nak két dolgot kell megadnunk: melyik két táblát kapcsoljuk össze, és milyen feltétellel párosítjuk a sorokat. Ez utóbbi az ON feltétel — szinte mindig az idegen kulcs és a hivatkozott elsődleges kulcs egyenlősége:

SELECT v.vezeteknev, r.id, r.rendeles_datuma
FROM vasarlo v
JOIN rendeles r ON r.vasarlo_id = v.id;
--                  └── a kapcsolási feltétel: a gyermek FK = a szülő PK

A párosítás logikája: a szerver minden rendeles sorhoz megkeresi azt a vasarlo sort, amelyre az ON feltétel igaz (r.vasarlo_id = v.id), és a kettőt egyetlen sorrá fűzi össze. Az alábbi ábra ezt mutatja — a vonalak a párosított sorokat kötik össze:

Az eredményt úgy képzeld el, mint egyetlen széles táblát, amelyben egymás mellett áll a két tábla összes oszlopa — minden sikeres párosítás egy-egy sor:

Figyeld meg: Nagy kétszer szerepel, mert két rendelése van — egy bal oldali sor annyiszor jelenik meg, ahány pár jut rá. Erre a széles táblára működik aztán minden, amit eddig tanultunk (WHERE, ORDER BY, GROUP BY…), a SELECT-listával pedig csak a kívánt oszlopokat mutatjuk meg belőle.

-- 1. Teljes táblanevekkel — alias nélkül is működik:
SELECT vasarlo.vezeteknev, rendeles.id, rendeles.rendeles_datuma
FROM vasarlo
JOIN rendeles ON rendeles.vasarlo_id = vasarlo.id;

-- 2. Rövid aliasokkal — ugyanaz, csak tömörebb (a gyakorlatban ezt szokás):
SELECT v.vezeteknev, r.id, r.rendeles_datuma
FROM vasarlo v
JOIN rendeles r ON r.vasarlo_id = v.id;

3INNER JOIN — csak az egyezők

Az INNER JOIN (vagy egyszerűen JOIN) csak azokat a sorokat adja vissza, amelyekhez mindkét táblában van pár. Halmazként ez a két tábla metszete.

SELECT v.vezeteknev, r.id AS rendeles_id, r.rendeles_datuma
FROM vasarlo v
INNER JOIN rendeles r ON r.vasarlo_id = v.id;

4LEFT JOIN — a bal tábla minden sora

A LEFT JOIN (más néven LEFT OUTER JOIN) a bal tábla összes sorát visszaadja, és mellé a jobb tábla egyező sorait. Ahol a bal sorhoz nincs pár a jobb táblában, ott a jobb oldali oszlopok NULL-ok lesznek.

-- MINDEN vásárló, a rendeléseivel (vagy nélkülük):
SELECT v.vezeteknev, r.id AS rendeles_id
FROM vasarlo v
LEFT JOIN rendeles r ON r.vasarlo_id = v.id;

Tóth és Szabó is megjelenik — a hiányzó pár helyén NULL-lal. (A vásárló nélküli 13-as rendelés viszont itt nem szerepel: az a jobb tábla párja nélküli sora.)

5RIGHT JOIN — a jobb tábla minden sora

A RIGHT JOIN a LEFT tükörképe: a jobb tábla összes sorát adja vissza, és mellé a bal tábla egyezőit; ahol nincs bal pár, ott a bal oldali oszlopok NULL-ok.

SELECT v.vezeteknev, r.id AS rendeles_id
FROM vasarlo v
RIGHT JOIN rendeles r ON r.vasarlo_id = v.id;

Most minden rendelés megjelenik: a 13-as „vendégrendeléshez” nincs vásárló, ezért ott a bal oldal NULL. Tóth és Szabó viszont (rendelés híján) kimaradt — pont fordítva, mint a LEFT JOIN-nál.

6FULL OUTER JOIN — minden sor mindkét oldalról

A FULL OUTER JOIN mindkét tábla összes sorát visszaadja: ahol van pár, összefűzi; ahol nincs (akár bal, akár jobb oldalon), ott a hiányzó oldal oszlopai NULL-ok. Halmazként ez a két tábla uniója.

7CROSS JOIN — Descartes-szorzat

A CROSS JOIN-nak nincs ON feltétele: a bal tábla minden sorát összepárosítja a jobb tábla minden sorával. Ez a halmazok Descartes-szorzata. Az eredmény sorszáma a két tábla sorszámának szorzata.

-- Minden vásárló minden kategóriával párosítva (4 × 3 = 12 sor):
SELECT v.vezeteknev, k.nev AS kategoria
FROM vasarlo v
CROSS JOIN kategoria k
ORDER BY v.vezeteknev, kategoria;

Mikor jó ez nekünk? Amikor tényleg minden kombinációra szükség van:

• Választék generálása: egy póló összes méret–szín párosítása (S/M/L × fekete/fehér) — a variációk táblájának feltöltéséhez.
• Kimutatás váza: a fenti „minden vásárló × minden kategória” rács adja a riport összes celláját; erre LEFT JOIN-nal jönnek a tényleges vásárlások — így az üres (0 Ft-os) cellák is látszanak, nem tűnnek el a kimutatásból.
• Naptár-rács: minden nap × minden termék — azokra a napokra is kell sor, amikor nem volt eladás.

8Több tábla összekapcsolása

Több JOIN is láncolható egymás után. A webshop „teljes” kérdéséhez — „ki, mit, mennyiért rendelt?” — négy táblát kötünk össze a kapcsolataik mentén: vasarlo → rendeles → rendeles_tetel → termek.

SELECT v.vezeteknev,
       r.id AS rendeles,
       t.nev AS termek,
       rt.mennyiseg,
       rt.egysegar
FROM vasarlo v
JOIN rendeles r        ON r.vasarlo_id = v.id
JOIN rendeles_tetel rt ON rt.rendeles_id = r.id
JOIN termek t          ON t.id = rt.termek_id;

Így oldjuk fel az N:M kapcsolatot is: a rendeles_tetel kapcsolótáblán keresztül kötjük össze a rendeléseket a termékekkel. Aggregálással pedig pl. vásárlónkénti összköltést kapunk:

SELECT v.vezeteknev, SUM(rt.mennyiseg * rt.egysegar) AS osszkoltes
FROM vasarlo v
JOIN rendeles r        ON r.vasarlo_id = v.id
JOIN rendeles_tetel rt ON rt.rendeles_id = r.id
GROUP BY v.id
ORDER BY osszkoltes DESC;

9Önkapcsolás (self join)

Egy tábla önmagával is összekapcsolható — ez az önkapcsolás. Akkor kell, ha egy soron belül egy másik ugyanabban a táblában lévő sorra hivatkozunk. Webshop-példa: a kategoria tábla szulo_id oszlopa egy másik kategóriára mutat. A szülő nevének megjelenítéséhez a táblát kétszer használjuk, két különböző aliasszal:

SELECT gy.nev AS alkategoria,
       sz.nev AS szulo_kategoria
FROM kategoria gy
LEFT JOIN kategoria sz ON gy.szulo_id = sz.id;
-- gy = gyermek, sz = szülő — ugyanaz a tábla, két szerepben

10Élő SQL-gyakorló

Próbáld ki a JOIN-okat a webshopon! Figyeld meg, hogyan jelennek meg / tűnnek el a rendelés nélküli vásárlók (Tóth, Szabó) és a vásárló nélküli 13-as „vendégrendelés” a különböző JOIN-típusoknál.

11Feladatok

SELECT v.vezeteknev, r.id
FROM vasarlo v
LEFT JOIN rendeles r ON r.vasarlo_id = v.id;

SELECT v.vezeteknev, t.nev
FROM vasarlo v
JOIN termek t;

SELECT gy.nev AS alkategoria, sz.nev AS szulo_kategoria
FROM kategoria gy
LEFT JOIN kategoria sz ON gy.szulo_id = sz.id;

12Önellenőrző kvíz

13Fogalomtár

14Összegzés