1. ismétlő óra: JOIN-ok — táblák összekapcsolása

Miről szól ez az óra?

Ez a két részes ismétlés első fele. Ma magukra a JOIN-okra koncentrálunk: végigmegyünk minden kapcsolattípuson (1:1, 1:N, N:M), mindegyiket megnézzük INNER JOIN-nal és LEFT JOIN-nal is, és minden esetben megszámoljuk, hány sor jött vissza és miért.

Ehhez jön két gyakorlati kérdés, amivel a mindennapi munkában lépten-nyomon találkozol:

1. Ha van egy plusz feltételem az összekapcsoláshoz, azt a JOIN ... ON mellé írjam, vagy a WHERE-be?
2. Hogyan keresem meg azokat a sorokat, amelyekhez nincs adat a másik táblában?

A 2. ismétlő órán ugyanezekre a lekérdezésekre — változatlan FROM/JOIN résszel — rátesszük a GROUP BY-t. Ennek a sorrendnek egyetlen oka van:

Az óra nem magyarázza el újra, hogy a WHERE-alapú (implicit) összekötéshez képest miért más az explicit JOIN — azt már vettük. Itt magukra a JOIN-okra és az eredményükre koncentrálunk.

Az óra felépítése

1. blokk: Két tábla — INNER és LEFT

A modell egy mondatban

A JOIN egy új, ideiglenes táblát gyárt két tábla soraiból. Az ON feltétel mondja meg, mely sorok tartoznak össze; a JOIN típusa pedig azt, hogy mi történjen a párt nem találó sorokkal.

Ez az egész. Minden más ebből következik.

1.1. Egy az egyhez (1:1): ügyfelek és adatlapjaik

A 24 ügyfelünkből csak 5-nek van kitöltött adatlapja az ugyfel_adatok táblában.

INNER JOIN — csak a párok:

SELECT u.id, u.nev, ua.telefonszam
FROM ugyfelek u
INNER JOIN ugyfel_adatok ua ON ua.ugyfel_id = u.id
ORDER BY u.id;

5 sor. A 24 ügyfélből 19 csendben eltűnt — nem hiba, nem figyelmeztetés, egyszerűen nincsenek ott.

LEFT JOIN — minden ügyfél megmarad:

SELECT u.id, u.nev, ua.telefonszam
FROM ugyfelek u
LEFT JOIN ugyfel_adatok ua ON ua.ugyfel_id = u.id
WHERE u.id <= 8
ORDER BY u.id;

WHERE nélkül 24 sor jönne vissza — pontosan annyi, ahány ügyfél van. Ez az 1:1 kapcsolat jellemzője: a LEFT JOIN nem szaporítja a sorokat, csak kiegészíti őket.

1.2. Egy a többhöz (1:N): kategóriák és termékek

Itt már szaporodnak a sorok: egy kategóriához több termék tartozik.

INNER JOIN:

SELECT k.nev AS kategoria, t.nev AS termek, t.ar
FROM kategoriak k
INNER JOIN termekek t ON t.kategoria_id = k.id
ORDER BY k.id, t.id;

25 sor. Nem 6 (ahány kategória) és nem 26 (ahány termék) — hanem annyi, ahány párosítás létrejött.

LEFT JOIN — a kategóriák felől:

SELECT k.nev AS kategoria, t.nev AS termek, t.ar
FROM kategoriak k
LEFT JOIN termekek t ON t.kategoria_id = k.id
WHERE k.id IN (4, 5, 6)          -- Sport, Könyv, Játék
ORDER BY k.id, t.id;

WHERE nélkül 26 sor: a 25 párosítás plusz egy sor a „Játék” kategóriának, amelyhez még nincs termék.

1.3. „És ha megfordítjuk?” — melyik oldal a bal oldal?

Ez a kérdés dönti el, mit akarsz látni. Írjuk meg ugyanezt a kapcsolatot a másik irányból:

SELECT t.id, t.nev AS termek, k.nev AS kategoria
FROM termekek t
LEFT JOIN kategoriak k ON k.id = t.kategoria_id     -- most a TERMÉK a bal oldal
WHERE t.id >= 23
ORDER BY t.id;

Ez is 26 sor — de teljesen más okból! Itt nem a termék nélküli kategória a plusz sor, hanem az Ajándékutalvány, amelynek a kategoria_id-ja NULL, tehát nincs kategóriája.

1.4. Sorszám-összefoglaló — ezt érdemes fejben tartani

2. blokk: Több tábla láncban — INNER és LEFT

Három vagy több tábla összekötése nem új szabály, csak ugyanaz a szabály többször egymás után. A lánc: ugyfelek → rendelesek → rendeles_tetelek.

2.1. INNER JOIN lánc — lépésenként

SELECT u.nev, r.id AS rendeles_id, r.allapot, rt.id AS tetel_id, rt.mennyiseg
FROM ugyfelek u
INNER JOIN rendelesek r        ON r.ugyfel_id   = u.id
INNER JOIN rendeles_tetelek rt ON rt.rendeles_id = r.id
ORDER BY u.id, r.id, rt.id;

Nézzük meg, hogyan változik a sorszám minden egyes JOIN után:

2.2. LEFT JOIN lánc — ugyanaz, senkit nem hagyva el

SELECT u.id, u.nev, r.id AS rendeles_id, r.allapot, rt.id AS tetel_id, rt.mennyiseg
FROM ugyfelek u
LEFT JOIN rendelesek r        ON r.ugyfel_id   = u.id
LEFT JOIN rendeles_tetelek rt ON rt.rendeles_id = r.id
ORDER BY u.id, r.id, rt.id;

Nézzünk bele három konkrét ügyfél sorába:

Antal Máriának nincs rendelése. A LEFT JOIN mégis legyártott neki egy sort, csupa NULL jobb oldallal — és a lánc második LEFT JOIN-ja is NULL-t hagyott, hiszen rt.rendeles_id = NULL sem talál semmit.

Fekete Lilla ezzel szemben kétszer szerepel, mert a 7-es rendelésének két tétele van. Ugyanaz az ügyfél, ugyanaz a rendelés, két sor. Jegyezd meg ezt a sort — a 6. blokkban visszatérünk rá.

2.3. ⚠️ A lánc-csapda: INNER a LEFT után

-- ❌ HIBÁS: a második JOIN INNER
SELECT COUNT(*)
FROM ugyfelek u
LEFT  JOIN rendelesek r        ON r.ugyfel_id   = u.id
INNER JOIN rendeles_tetelek rt ON rt.rendeles_id = r.id;
-- Eredmény: 25

25 sor — pontosan annyi, mint a tiszta INNER láncnál. A LEFT JOIN gondosan megtartotta Antal Mária NULL-os sorát, aztán a következő INNER JOIN kidobta, mert NULL-hoz nem talált tételt.

3. blokk: Több a többhöz (N:M) kapcsolat

Az N:M kapcsolat nem külön JOIN-fajta — csak két 1:N kapcsolat egy kapcsolótáblán keresztül. Egy ügyfélnek több kedvenc terméke lehet, és egy terméket több ügyfél is megjelölhet: közöttük a kedvencek tábla áll.

ugyfelek  ──1:N──►  kedvencek  ◄──N:1──  termekek
                  (kapcsolótábla)

3.1. INNER JOIN — a tényleges kedvencek

SELECT
    u.nev AS ugyfel,
    t.nev AS termek,
    kd.hozzaadas_datuma,
    kd.ar_akkor,
    t.ar  AS mai_ar
FROM ugyfelek u
INNER JOIN kedvencek kd ON kd.ugyfel_id = u.id
INNER JOIN termekek t   ON t.id = kd.termek_id
ORDER BY u.id, t.id;

Pontosan 9 sor — annyi, ahány sor a kedvencek táblában van. Ez az N:M szabálya: a kapcsolótábla sorainak száma határozza meg az eredményt, nem a két szélső táblájé.

Figyeld meg azt is, hogy az Apple AirPods Pro kétszer szerepel (Kiss Péter és Nagy Anna listáján), és Kiss Péter háromszor. Ez normális: az N:M kapcsolat mindkét irányban szaporít.

3.2. LEFT JOIN — mindenki, kedvenc nélkül is

SELECT u.id, u.nev AS ugyfel, t.nev AS termek
FROM ugyfelek u
LEFT JOIN kedvencek kd ON kd.ugyfel_id = u.id
LEFT JOIN termekek t   ON t.id = kd.termek_id
WHERE u.id BETWEEN 4 AND 8
ORDER BY u.id, t.id;

WHERE nélkül 28 sor: a 9 kedvenc + 19 ügyfél, akinek egyet sem jelölt meg.

És a másik irányból, ha minden terméket akarunk látni:

SELECT COUNT(*)
FROM termekek t
LEFT JOIN kedvencek kd ON kd.termek_id = t.id
LEFT JOIN ugyfelek u   ON u.id = kd.ugyfel_id;
-- Eredmény: 29

29 sor: a 9 kedvenc + 20 termék, amit senki nem jelölt meg. (26 termékből 6 szerepel valakinek a listáján.)

3.3. Sorszám-összefoglaló az N:M-hez

4. blokk: Plusz feltétel az összekapcsoláshoz — ON vagy WHERE?

Eddig az ON-ban mindig csak a kapcsolómezők egyenlősége szerepelt. De mi van, ha plusz feltételem is van? A feladat: „Kössük össze az ügyfeleket a rendeléseikkel — de csak a teljesített rendelések érdekelnek.”

Két helyre írhatod. Nézzük meg mindkettőt.

4.1. INNER JOIN esetén nincs különbség

-- A) a feltétel az ON mellett
SELECT COUNT(*) FROM ugyfelek u
INNER JOIN rendelesek r ON r.ugyfel_id = u.id AND r.allapot = 'teljesítve';
-- Eredmény: 8

-- B) a feltétel a WHERE-ben
SELECT COUNT(*) FROM ugyfelek u
INNER JOIN rendelesek r ON r.ugyfel_id = u.id
WHERE r.allapot = 'teljesítve';
-- Eredmény: 8

Ugyanaz a nyolc sor. Logikus: az INNER JOIN amúgy is csak a párokat tartja meg, tehát mindegy, hogy a szűrés a párosításkor vagy utána történik.

Ha csak INNER JOIN-t használnál, ez a kérdés soha nem jönne elő. A különbség a LEFT JOIN-nál robban.

4.2. LEFT JOIN esetén óriási a különbség

A) verzió — a feltétel az ON mellett:

SELECT u.id, u.nev, r.id AS rendeles_id, r.allapot
FROM ugyfelek u
LEFT JOIN rendelesek r
       ON r.ugyfel_id = u.id
      AND r.allapot = 'teljesítve'      -- ⬅ ITT
WHERE u.id <= 8
ORDER BY u.id, r.id;

B) verzió — a feltétel a WHERE-ben:

SELECT u.id, u.nev, r.id AS rendeles_id, r.allapot
FROM ugyfelek u
LEFT JOIN rendelesek r ON r.ugyfel_id = u.id
WHERE r.allapot = 'teljesítve'          -- ⬅ ITT
  AND u.id <= 8
ORDER BY u.id, r.id;

Tóth Gábor, Fekete Lilla és Molnár Zsuzsa eltűnt. Pedig ők léteznek, és van rendelésük — csak nem teljesített (lemondva, illetve folyamatban).

4.3. Miért? — a végrehajtási sorrend adja a választ

Az ON az 1. lépésben fut, a WHERE a 2.-ban. Ebből minden következik:

A B) verzióban a LEFT JOIN először tényleg legyártja Tóth Gábor sorát NULL állapottal. Aztán jön a WHERE r.allapot = 'teljesítve', ami a NULL = 'teljesítve' kifejezést értékeli ki — az eredmény pedig nem TRUE, hanem NULL, ami a WHERE szemében ugyanolyan bukás, mint a FALSE. A sor kiesik.

4.4. A döntési szabály — tanuld meg ezt a három sort

5. blokk: A „hiány” keresése — amihez nincs adat a másik táblában

Ez a tesztelő egyik legfontosabb kérdéstípusa, mert az adatintegritási hibák szinte mindig így néznek ki: „van egy sor, amihez lennie kellene párnak, de nincs.”

• Melyik ügyfél nem rendelt még soha? (marketinglista)
• Melyik terméket nem adtuk el egyszer sem? (kifutó termékek)
• Melyik ügyfélnek nincs kitöltve az adatlapja? (hiányos migráció)
• Melyik rendeléshez nem tartozik egyetlen tétel sem? (sérült adat!)

5.1. Az alapminta: LEFT JOIN + IS NULL

A trükk két lépésből áll, és pontosan azt használja ki, amit az 1. és 4. blokkban megtanultunk:

1. LEFT JOIN-nal megtartunk minden bal oldali sort — a pár nélkülieket is, NULL jobb oldallal.
2. Aztán a WHERE-ben pont ezekre a NULL-okra szűrünk — így csak a pár nélküli sorok maradnak.

SELECT u.id, u.nev, u.varos
FROM ugyfelek u
LEFT JOIN rendelesek r ON r.ugyfel_id = u.id
WHERE r.id IS NULL              -- ⬅ „nem talált párt"
ORDER BY u.id;

14 ügyfél a 24-ből még soha nem rendelt. (Emlékszel? A 2.2-es táblázatban pont ez a 14 sor volt a különbség a 15 és a 29 között.)

Ugyanez a minta az N:M kapcsolatra — melyik terméket nem adtuk el soha?

SELECT t.id, t.nev, t.ar
FROM termekek t
LEFT JOIN rendeles_tetelek rt ON rt.termek_id = t.id
WHERE rt.id IS NULL
ORDER BY t.id;

5.2. Ugyanez NOT EXISTS-szel

SELECT u.id, u.nev, u.varos
FROM ugyfelek u
WHERE NOT EXISTS (
    SELECT 1 FROM rendelesek r WHERE r.ugyfel_id = u.id
)
ORDER BY u.id;

Szintén 14 sor, ugyanazok. Ez a változat gyakran olvashatóbb, mert szó szerint azt mondja, amit gondolsz: „nem létezik hozzá rendelés”. Nincs benne a LEFT JOIN + IS NULL kétlépéses trükk, és nincs veszélye a sorok felszorzódásának sem.

Mindkettőt ismerd fel — éles kódban mindkettővel találkozni fogsz.

5.3. ⚠️ A NOT IN csapdája

Van egy harmadik, kézenfekvőnek tűnő megoldás, ami NULL jelenlétében csendben rossz eredményt ad. Keressük meg azokat a kategóriákat, amelyekhez nem tartozik termék:

-- ❌ HIBÁS
SELECT k.nev
FROM kategoriak k
WHERE k.id NOT IN (SELECT t.kategoria_id FROM termekek t);

Eredmény: 0 sor. Pedig az 1.2-es példából tudjuk, hogy a „Játék” kategóriához nincs termék!

Mi történt? Emlékezz az 1.3-as példára: az Ajándékutalvány termék kategoria_id-ja NULL. Az allekérdezés tehát ilyesmit ad vissza: (1, 1, 1, …, 5, NULL). Az SQL pedig a 6 NOT IN (1, 2, 3, 4, 5, NULL) kifejezést így értékeli ki: 6 <> 1 AND 6 <> 2 AND … AND 6 <> NULL. Az utolsó tag NULL — és TRUE AND NULL = NULL, ami nem TRUE. Egyetlen sor sem tud átmenni a feltételen.

-- ✅ JAVÍTVA: kizárjuk a NULL-okat az allekérdezésből
SELECT k.nev
FROM kategoriak k
WHERE k.id NOT IN (
    SELECT t.kategoria_id FROM termekek t
    WHERE t.kategoria_id IS NOT NULL
);

Eredmény: Játék. Most már jó.

5.4. A haladó eset: „nincs ilyen párja” (a 4. és 5. blokk találkozása)

Kérdés: „Melyek azok a termékek, amelyekre nem érkezett 5 csillagos vélemény?“

Ez nem sima anti-join, mert nem az a kérdés, hogy „nincs véleménye”, hanem hogy „nincs ilyen véleménye”. Sokan itt esnek el.

1. próbálkozás — „szűrjünk arra, hogy nem 5” (HIBÁS):

-- ❌
SELECT DISTINCT t.id, t.nev
FROM termekek t
INNER JOIN velemenyek v ON v.termek_id = t.id
WHERE v.ertekeles <> 5;

Két sor. Ez több szempontból is rossz:

• Az INNER JOIN miatt a vélemény nélküli termékek eltűntek — pedig azokra végképp nem érkezett 5 csillagos.
• A NULL értékelésű vélemények is kiestek, mert NULL <> 5 nem TRUE.
• És ami a legrosszabb: ha egy terméknek van 5 csillagos és van 3 csillagos véleménye is, akkor a 3 csillagos sor miatt bekerülne a listába — hibásan.

2. próbálkozás — ON-szűrés + anti-join (HELYES):

-- ✅
SELECT t.id, t.nev
FROM termekek t
LEFT JOIN velemenyek v
       ON v.termek_id = t.id
      AND v.ertekeles = 5          -- ⬅ csak az 5 csillagosakat kapcsoljuk hozzá (4. blokk)
WHERE v.id IS NULL                 -- ⬅ és megtartjuk azokat, amikhez nem jött ilyen (5.1)
ORDER BY t.id;

24 termék a 26-ból. Csak a Samsung Galaxy A55 és a Logitech MX Master 3 esik ki — pontosan ez a két termék kapott 5 csillagot.

Olvasd el a lekérdezést hangosan, két lépésben:

1. Az ON … AND v.ertekeles = 5 azt mondja: „minden termékhez kapcsold hozzá az 5 csillagos véleményeit — ha nincs ilyen, hagyd üresen a helyét.”
2. A WHERE v.id IS NULL azt mondja: „és most add vissza azokat, ahol a hely üres maradt.”

5.5. Anti-join minták összefoglalva

Összefoglalás

A JOIN-ok egy táblázatban

A két nagy csapda, ami megöli a LEFT JOIN-t — mindkettő hibaüzenet nélkül:

1. INNER a LEFT után a láncban (2.3) → a második JOIN kidobja a NULL-os sorokat.
2. A jobb oldali tábla szűrése a WHERE-ben (4.2) → ugyanaz történik, csak a WHERE teszi.

A négy ökölszabály

1. Mielőtt JOIN-t írsz, döntsd el, melyik táblából akarsz minden sort látni — az megy a FROM után.
2. LEFT JOIN-láncban minden további JOIN is LEFT legyen — egyetlen INNER mindent elront.
3. A jobb oldali tábla szűrése az ON mellé megy, nem a WHERE-be.
4. Hiány kereséséhez LEFT JOIN … WHERE jobb.id IS NULL vagy NOT EXISTS — a NOT IN NULL-lal becsap.

Tesztelői ellenőrző lista JOIN-okhoz

• Hány sort vártál? (24 ügyfél? 6 kategória? 26 termék?) Ennyi jött vissza?
• Melyik táblából akartál minden sort látni? Az van a FROM után?
• LEFT JOIN-láncban mindegyik JOIN LEFT?
• A jobb oldali táblára vonatkozó szűrés az ON mellett van?
• Ha NOT IN-t használtál: lehet-e NULL az allekérdezés eredményében?

Házi feladatok az önálló gyakorláshoz

1. feladat — JOIN irány

Írj egy lekérdezést, amely minden terméket kilistáz a kategóriája nevével együtt! Azok a termékek is szerepeljenek, amelyeknek nincs kategóriájuk. Hány sor lesz az eredmény, és melyik termékhez lesz NULL a kategória?

SELECT t.id, t.nev AS termek, k.nev AS kategoria
FROM termekek t
LEFT JOIN kategoriak k ON k.id = t.kategoria_id
ORDER BY t.id;

2. feladat — ON vagy WHERE? (aggregálás nélkül)

Listázd ki minden ügyfél nevét és a 2024-ben leadott rendeléseinek azonosítóját és dátumát! Azok az ügyfelek is szerepeljenek egy sorral, akiknek 2024-ben nem volt rendelésük — beleértve azt is, akinek egyáltalán nincs rendelése.

Utána írd meg úgy is, hogy a dátumszűrés a WHERE-be kerül. Hány sort ad a két változat?

-- ✅ A feltétel az ON mellett
SELECT u.id, u.nev, r.id AS rendeles_id, r.rendeles_datum
FROM ugyfelek u
LEFT JOIN rendelesek r
       ON r.ugyfel_id = u.id
      AND r.rendeles_datum >= DATE '2024-01-01'
ORDER BY u.id, r.id;

-- ❌ A feltétel a WHERE-ben
SELECT u.id, u.nev, r.id AS rendeles_id, r.rendeles_datum
FROM ugyfelek u
LEFT JOIN rendelesek r ON r.ugyfel_id = u.id
WHERE r.rendeles_datum >= DATE '2024-01-01'
ORDER BY u.id, r.id;

3. feladat — Anti-join kétféleképpen

Melyik ügyfeleknek nincs kitöltve az adatlapja (nincs soruk az ugyfel_adatok táblában)? Írd meg a lekérdezést kétféleképpen: LEFT JOIN-nal és NOT EXISTS-szel!

-- A) LEFT JOIN + IS NULL
SELECT u.id, u.nev, u.email
FROM ugyfelek u
LEFT JOIN ugyfel_adatok ua ON ua.ugyfel_id = u.id
WHERE ua.ugyfel_id IS NULL
ORDER BY u.id;

-- B) NOT EXISTS
SELECT u.id, u.nev, u.email
FROM ugyfelek u
WHERE NOT EXISTS (
    SELECT 1 FROM ugyfel_adatok ua WHERE ua.ugyfel_id = u.id
)
ORDER BY u.id;

4. feladat — Anti-join N:M kapcsolaton

Melyik termékeket nem jelölte meg senki kedvencként? Hány ilyen termék van?

SELECT t.id, t.nev, t.ar
FROM termekek t
LEFT JOIN kedvencek kd ON kd.termek_id = t.id
WHERE kd.termek_id IS NULL
ORDER BY t.id;