SQL - JOINs & Mengen

Aufbauend auf Gruppierung & Subqueries behandelt diese Seite das Verbinden mehrerer Tabellen, äußere Joins, Mengenoperationen sowie bedingte Logik mit CASE.

Miniwelt Oberstufe — zur Erinnerung

Wir arbeiten weiterhin mit der Datenbank einer fiktiven Berliner Oberstufe. Hinterlegt sind Schüler, Lehrer, Fächer, Kurse, Belegungen und Abiturprüfungen:

• Schueler (S_ID, Name, Vorname, Geburtsdatum, Bezirk, 🠙Tutor_ID)
  • 🠙Tutor_ID: Referenziert als Fremdschlüssel Lehrer.L_ID
• Lehrer (L_ID, Kuerzel, Name, Vorname)
• Fach (F_ID, Bezeichnung)
• Kurs (K_ID, 🠙F_ID, 🠙L_ID, Kursart, Semester)
  • Kursart: LK oder GK; Semester: Q1 bis Q4
• Belegung (🠙S_ID, 🠙K_ID, Note, Einbringung)
  • Note: 0–15 Punkte; Einbringung: Kurs wird für die Abiturnote berücksichtigt wenn 1, ansonsten nicht.
• Abitur_Pruefung (🠙S_ID, Pruefung, 🠙F_ID, Note)
  • Pruefung: Gibt an, ob 1. LK, 2. LK, 3. schriftliche Prüfung, 4. mündliche Prüfung oder 5. PK

\

JOINs, Mengen und Logik

Bevor wir in die Aufgaben einsteigen: Folgende Demo macht die verschiedenen JOIN-Varianten anschaulich. Wechsle zwischen den Typen — die Quelltabellen werden farbig markiert, das Ergebnis wird live berechnet.

Join-Typ

Nur Zeilen mit Partner in beiden Tabellen — also wo k.K_ID = b.K_ID.

Kunde
K_ID	Name
1	Anna
2	Ben
3	Clara

Bestellung
B_ID	K_ID	Produkt
101	1	Buch
102	2	Lampe
103	99	Stuhl
104	1	Stift

Ergebnis · 3 Zeilen
k.K_ID	k.Name	b.B_ID	b.K_ID	b.Produkt
1	Anna	101	1	Buch
1	Anna	104	1	Stift
2	Ben	102	2	Lampe

SELECT * FROM Kunde k
JOIN Bestellung b ON k.K_ID = b.K_ID;

Der einfache JOIN ●○

Zur Erinnerung: Ein Basis-JOIN (implizit immer ein INNER JOIN) verknüpft Datensätze aus zwei Tabellen anhand eines gemeinsamen Schlüssels. Zeilen, die in einer der beiden Tabellen keinen passenden Partner finden, werden im Ergebnis nicht angezeigt.

Training

1. Zeige von Homer den Vornamen, den Nachnamen sowie alle Noten mit den zugehörigen Kurs-IDs.
2. Liste das Kursangebot der Oberstufe auf (Bezeichnung des Faches, GK/LK), alphabetisch sortiert nach Fächernamen ohne Dopplungen.
3. Welche Lehrer, die mit 'H' oder 'N' beginnen, unterrichten Leistungskurse? Nur Vor- und Nachname ohne Dopplungen angeben.
4. Gib eine Liste aller Schüler (Vorname, Nachname) mit dem einfachen Notendurchschnitt aller belegten Kurde aus (LK und GK gleich gewichtet). Sortiere absteigend nach dem Notendurchschnitt.
5. Erstelle eine absteigend sortierte Übersicht über die durchschnittlichen Prüfungsergebnisse aller Abiturprüfungen (Fach-Bezeichnung, Pruefung, Durchschnittsnote).
   Hinweis: GROUP BY kann auch auf zwei Spalten gleichzeitig angewendet werden.
6. Liste alle Schüler aus Neukölln mit ihrer Tutorin/ihrem Tutor (Vorname, Name des Lehrers) auf.

Mehrfache JOINs ●○

Um sinnvolle Zeugnisse oder Kurslisten zu erstellen, müssen oft mehr als zwei Tabellen verbunden werden. Das Prinzip von JOIN ... ON ... lässt sich beliebig aneinanderreihen.

Beispiel: Erstelle ein „Zeugnis" für Lukas Müller (S_ID = 1) mit Fach-Bezeichnung, Kursart, Semester und Note. Dafür müssen vier Tabellen verbunden werden: Schueler → Belegung → Kurs → Fach.

Training

1. Bestimme für jede Lehrkraft den Notenschnitt. Sortiere absteigend.
2. Welche Schüler haben in einem ihrer LK-Kurse eine 15? Gib Vor- und Nachname und das jeweilige Fach an.
3. In wie vielen Kursen haben Schüler (Vorname, Nachname) 15 Punkte. Sortiere absteigend.
4. Gib alle Belegungen mit Streichresultat (Einbringung = 0) zusammen mit Schülername und Fachbezeichnung aus.

LEFT JOIN — auch das Fehlende sichtbar machen ●○

Ein INNER JOIN zeigt nur Zeilen, die in beiden Tabellen einen Partner finden. Ein LEFT JOIN (auch LEFT OUTER JOIN) behält alle Zeilen der linken Tabelle — auch wenn rechts kein Partner existiert. Fehlende Werte werden mit NULL aufgefüllt.

Damit lassen sich elegant Fragen wie „Wer hat keine …?" beantworten: Man verknüpft per LEFT JOIN und filtert die NULL-Zeilen mit WHERE rechtetabelle.spalte IS NULL.

Beispiel: Welche Lehrer betreuen keine Tutorenklasse?

Ein LEFT JOIN ist auch sinnvoll, wenn wir Zählungen inklusive Null brauchen. Beispiel: Anzahl Tutanden pro Lehrer, auch für Lehrer ohne Tutorenklasse:

Wichtig: Bei COUNT(*) würde jede Zeile gezählt — auch die NULL-Zeile aus dem LEFT JOIN. COUNT(s.S_ID) zählt nur tatsächlich vorhandene Schüler. Genau das wollen wir hier.

Training

1. Welche Fächer werden nicht als Leistungskurs angeboten? Gib die Fach-Bezeichnung aus. Tipp: Der (LEFT) JOIN kann mehrere Bedingungen verknüpfen (ON ... AND ... )
2. Liste alle Lehrer mit der Anzahl Kurse, die sie unterrichten — auch Lehrer mit 0 Kursen.
3. Für jeden Schüler: Wie viele Abiturprüfungen sind sind nicht bestanden (weniger als 5 Notenpunkte)? Gib Vorname, Name und Anzahl aus.
4. Welche Schüler haben in keinem ihrer Kurse die volle Punktzahl (15) erreicht? Nutze LEFT JOIN + IS NULL.

Self-JOIN — eine Tabelle mit sich selbst verknüpfen ●●

Manchmal müssen Zeilen einer Tabelle mit anderen Zeilen derselben Tabelle verglichen werden. Dafür wird die Tabelle in der FROM-Klausel zweimal referenziert, jeweils mit eigenem Alias.

Beispiel: Finde alle Schüler-Paare, die im selben Bezirk wohnen.

a.S_ID < b.S_ID verhindert zwei Probleme zugleich: Ein Schüler wird nicht mit sich selbst gepaart, und das Paar (A, B) erscheint nicht zusätzlich als (B, A).

Der String-Konkatenations-Operator || (zwei Pipe-Zeichen) gehört zum SQL-Standard und verkettet zwei oder mehr Zeichenketten zu einer einzigen. Gilt nicht für MySQL.

Training

1. Finde alle Belegungs-Paare zweier verschiedener Schüler (S_ID), die im selben Kurs dieselbe Note erhalten haben. Gib die beiden S_IDs, die Kurs-ID und die Note aus.
2. Find die beiden Schüler (S_ID,Name), die am häufigsten die selben Noten in den selben Kursen haben. Wie viele Noten sind gleich?
3. Finde alle Schüler-Paare, die denselben Tutor haben und im selben Bezirk wohnen.
4. Finde alle Paare verschiedener Kurse, die im gleichen Semester vom gleichen Lehrer unterrichtet werden.

Bedingte Logik mit CASE WHEN ●○

Mit CASE WHEN ... THEN ... ELSE ... END lässt sich Wenn-Dann-Logik direkt in SELECT, WHERE, ORDER BY oder GROUP BY verwenden. Das Konstrukt ist vergleichbar mit einer mehrstufigen if-elif-else-Abfrage in einer Programmiersprache.

Beispiel: Übersetze Punkte (0–15) in klassische Notenstufen.

CASE lässt sich auch sehr effektiv mit Aggregatfunktionen kombinieren. So kann z. B. der LK- und der GK-Schnitt eines Schülers in einer einzigen Zeile pro Schüler berechnet werden:

Trick: AVG() ignoriert NULL-Werte. Der CASE-Ausdruck ohne ELSE liefert NULL für die jeweils andere Kursart — so wird sie aus der Mittelung ausgeschlossen.

Training

1. Gib für jede Belegung Vorname, Name, Note und eine Spalte Bestanden aus, die 'ja' ist, wenn die Note ≥ 5 ist, sonst 'nein'.
2. Zähle pro Schüler, wie viele Belegungen in den Notenstufen sehr gut, gut, befriedigend, ausreichend und Ausfall liegen (5 Spalten).
3. Für jeden Lehrer: Anzahl seiner LK-Kurse und Anzahl seiner GK-Kurse in zwei Spalten (LK_Anzahl, GK_Anzahl). Hinweis: Ein Lehrer hat weder LK noch GK Kurse und soll trotzdem aufgeführt werden.

Mengenoperationen: UNION, INTERSECT, EXCEPT ●●

Mengenoperationen kombinieren zwei SELECT-Ergebnisse zu einer neuen Ergebnismenge. Beide Teilabfragen müssen dieselbe Anzahl Spalten mit kompatiblen Datentypen liefern.

Operator	Bedeutung
UNION	Vereinigung (Duplikate entfernt)
UNION ALL	Vereinigung (mit Duplikaten — schneller)
INTERSECT	Schnittmenge: Zeilen, die in beiden vorkommen
EXCEPT	Differenz: Zeilen aus erster Menge ohne die aus zweiter

Die folgende Demo zeigt das Verhalten der vier Operatoren anschaulich. Wechsle zwischen den Operatoren — das Ergebnis wird live berechnet.

Mengen-Operator

Vereinigung ohne Duplikate: alle Zeilen, die in A oder B vorkommen — gleiche Werte erscheinen nur einmal.

A
name
Anna
Ben
Clara
Dora

∪

B
name
Clara
Dora
Emil
Frida

=

Ergebnis · 6
name
Anna
Ben
Clara
Dora
Emil
Frida

SELECT name FROM A
UNION
SELECT name FROM B;

Beispiel: Welche Fächer werden nicht als Leistungskurs angeboten?

Beispiel UNION: Welche Schüler haben mindestens eine 15 erreicht — entweder in einer Kursnote oder in einer Abiturprüfung?

Training

Nutze Mengenoperationen.

1. Welche Schüler (S_ID) haben sowohl in einer Belegung als auch in einer Abiturprüfung mindestens einmal 15 Punkte erreicht?
2. Welche Schüler hatten in den Kursen mal 15 Punkte, aber in keiner Abiturprüfung jemals die volle Punktzahl?
3. Welche Schüler haben mindestens einmal weniger als 5 Punkte erhalten, egal ob in einem Kurs oder in den Abiprüfungen?
4. Gib alle Notenwerte von Lukas Müller (S_ID = 1), aus Belegungen und Abiturprüfungen, absteigend sortiert aus. Frage zum Nachdenken: Warum wäre UNION hier inhaltlich falsch?
5. Welche Fächer (F_ID) hat Lukas Müller (S_ID = 1) sowohl in Q1 als auch in Q4 belegt? (Das sind die Fächer, die er die ganze Oberstufe durchgezogen hat, typischerweise die LKs.)

Oberstufen-Datenbank

Die verwendete SQLite-Datenbank kann auch heruntergeladen werden und beispielsweise mit SQLite-Browser bearbeitet werden.