// B3.1 · ca. 30 Min. · Einstieg
Nach dieser Lektion sagen Sie voraus, wie viele Zeilen ein Join liefert, verhindern, dass ein LEFT JOIN still zum Inner Join wird, und zählen und filtern Gruppen ohne NULL-Überraschungen.
Lektion 1 von 6 in B3 Joins und Aggregate
Danach können Sie
Aufwärmen · Aufgabe 1 von 7
-- customers -- orders
-- id | name | city -- id | customer_id | amount | status
-- 1 | Ana | Berlin -- 101 | 1 | 50 | paid
-- 2 | Ben | Munich -- 102 | 1 | 30 | refunded
-- 3 | Cleo | NULL -- 103 | 2 | 20 | paid
-- 4 | Dev | BerlinVorhersagen · Aufgabe 2 von 7
-- customers -- orders
-- id | name | city -- id | customer_id | amount | status
-- 1 | Ana | Berlin -- 101 | 1 | 50 | paid
-- 2 | Ben | Munich -- 102 | 1 | 30 | refunded
-- 3 | Cleo | NULL -- 103 | 2 | 20 | paid
-- 4 | Dev | Berlin
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;Üben · Aufgabe 3 von 7
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';Üben · Aufgabe 4 von 7
SELECT c.name, COUNT(____) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;Üben · Aufgabe 5 von 7
Denksport · Aufgabe 6 von 7
SELECT name
FROM customers
WHERE city <> 'Berlin';Anwenden · Aufgabe 7 von 7
Prüfen Sie Ihr Ergebnis anhand dieser Liste
Lesen Sie das ausgearbeitete Beispiel und lösen Sie dann die Übungen. Ihr Code läuft in Ihrem Browser oder auf Ihrem Computer und wird nie hochgeladen.
Ausgearbeitetes Beispiel
setup.sql legt die beiden Tabellen dieser Lektion an; main.sql verbindet sie zweimal und sucht dann einen unbekannten Ort. Vergleichen Sie die ersten beiden Ergebnisse Zeile für Zeile: Der LEFT JOIN liefert alles, was der Inner Join liefert, und dazu eine mit NULL gefüllte Zeile für jeden Kunden ohne Bestellung. Auf Ihrem Rechner führen Sie es mit psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql aus.
main.sql
-- 1. Inner join: only customers who have an order.
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;
-- 2. Left join: every customer, NULL where there is no order.
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;
-- 3. Customers whose city is unknown.
SELECT name FROM customers WHERE city IS NULL;
setup.sql
CREATE TABLE customers (
id integer PRIMARY KEY,
name text,
city text
);
INSERT INTO customers VALUES
(1, 'Ana', 'Berlin'),
(2, 'Ben', 'Munich'),
(3, 'Cleo', NULL),
(4, 'Dev', 'Berlin');
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer REFERENCES customers,
amount integer,
status text
);
INSERT INTO orders VALUES
(101, 1, 50, 'paid'),
(102, 1, 30, 'refunded'),
(103, 2, 20, 'paid');
Ausführen mit
psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sqlAusgabe
name | order_id | amount
------+----------+--------
Ana | 101 | 50
Ana | 102 | 30
Ben | 103 | 20
(3 rows)
name | order_id | amount
------+----------+--------
Ana | 101 | 50
Ana | 102 | 30
Ben | 103 | 20
Cleo | |
Dev | |
(5 rows)
name
------
Cleo
(1 row)Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.
Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.
Übung 1 von 3
setup.sql hat customers und orders angelegt. Die Abfrage soll jeden Kunden mit der Zahl seiner Bestellungen zeigen, meldet aber 1 für Cleo und Dev, die keine haben. Ändern Sie, was COUNT zählt, sodass beide 0 zeigen. LEFT JOIN, GROUP BY und ORDER BY bleiben.
Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.
Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.
Nach dem LEFT JOIN hat Cleo noch eine Zeile, mit NULL in jeder Spalte von orders. COUNT(*) zählt diese Zeile.
COUNT(Spalte) zählt nur die Zeilen, in denen diese Spalte nicht NULL ist.
Zählen Sie eine Spalte von orders, die jede echte Bestellung hat, etwa ihren Primärschlüssel: COUNT(o.id).
Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;
Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.
main.sql
-- Cleo and Dev have no orders, yet this reports 1 for each.
SELECT c.name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;
test.sql
-- test: Jeder Kunde erscheint, mit 0 für die ohne Bestellung
-- output:
-- name | order_count
-- ------+-------------
-- Ana | 2
-- Ben | 1
-- Cleo | 0
-- Dev | 0
-- (4 rows)
setup.sql
CREATE TABLE customers (
id integer PRIMARY KEY,
name text,
city text
);
INSERT INTO customers VALUES
(1, 'Ana', 'Berlin'),
(2, 'Ben', 'Munich'),
(3, 'Cleo', NULL),
(4, 'Dev', 'Berlin');
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer REFERENCES customers,
amount integer,
status text
);
INSERT INTO orders VALUES
(101, 1, 50, 'paid'),
(102, 1, 30, 'refunded'),
(103, 2, 20, 'paid');
psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.
Programm ausführen:
psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sqlFür die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.
Übung 2 von 3
Listen Sie Name und Ort jedes Kunden auf, von dem nicht bekannt ist, dass er in Berlin wohnt, nach Namen sortiert. Ein Kunde mit city NULL gehört auch dazu: Wir wissen nicht, dass er in Berlin wohnt. Der Startcode vergisst ihn.
Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.
Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.
Für Cleo ist NULL <> 'Berlin' nicht true, sondern NULL, also verwirft WHERE ihre Zeile.
Entweder ergänzen Sie den NULL-Fall selbst: city <> 'Berlin' OR city IS NULL.
Oder Sie nehmen den Vergleich, der NULL wie einen Wert behandelt: city IS DISTINCT FROM 'Berlin'.
Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.
SELECT name, city
FROM customers
WHERE city IS DISTINCT FROM 'Berlin'
ORDER BY name;
Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.
main.sql
SELECT name, city
FROM customers
WHERE city <> 'Berlin'
ORDER BY name;
test.sql
-- test: Ben (Munich) und Cleo (Ort unbekannt) erscheinen, nach Namen sortiert
-- output:
-- name | city
-- ------+--------
-- Ben | Munich
-- Cleo |
-- (2 rows)
setup.sql
CREATE TABLE customers (
id integer PRIMARY KEY,
name text,
city text
);
INSERT INTO customers VALUES
(1, 'Ana', 'Berlin'),
(2, 'Ben', 'Munich'),
(3, 'Cleo', NULL),
(4, 'Dev', 'Berlin');
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer REFERENCES customers,
amount integer,
status text
);
INSERT INTO orders VALUES
(101, 1, 50, 'paid'),
(102, 1, 30, 'refunded'),
(103, 2, 20, 'paid');
psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.
Programm ausführen:
psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sqlFür die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.
Übung 3 von 3
Die Abfrage addiert die Beträge der Bestellungen jedes Kunden. Behalten Sie nur die Kunden, deren Summe mindestens 50 ist. Der Test betrifft Gruppen und kann daher nicht in WHERE stehen.
Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.
Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.
WHERE läuft, bevor es die Gruppen gibt, und sieht daher kein SUM.
HAVING kommt nach GROUP BY und filtert ganze Gruppen.
Fügen Sie HAVING SUM(o.amount) >= 50 zwischen GROUP BY und ORDER BY ein. Ein Alias aus SELECT wie total ist dort nicht nutzbar.
Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(o.amount) >= 50
ORDER BY c.name;
Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.
main.sql
-- Keep only the customers whose total is at least 50.
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;
test.sql
-- test: Nur Ana erscheint, deren Bestellungen zusammen 80 ergeben
-- output:
-- name | total
-- ------+-------
-- Ana | 80
-- (1 row)
setup.sql
CREATE TABLE customers (
id integer PRIMARY KEY,
name text,
city text
);
INSERT INTO customers VALUES
(1, 'Ana', 'Berlin'),
(2, 'Ben', 'Munich'),
(3, 'Cleo', NULL),
(4, 'Dev', 'Berlin');
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer REFERENCES customers,
amount integer,
status text
);
INSERT INTO orders VALUES
(101, 1, 50, 'paid'),
(102, 1, 30, 'refunded'),
(103, 2, 20, 'paid');
psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.
Programm ausführen:
psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sqlFür die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.
SELECT id, name
FROM customers c
JOIN orders o ON o.customer_id = c.id;
Was psql ausgibt
ERROR: column reference "id" is ambiguousWarum, und die Lösung
customers und orders haben beide eine Spalte id, und PostgreSQL rät nicht, welche Sie meinen. Stellen Sie jeder solchen Spalte ihre Tabelle oder ihren Alias voran: SELECT o.id, c.name. Viele Entwickler qualifizieren in einem Join jede Spalte, damit die Abfrage eindeutig bleibt, wenn eine Tabelle später eine Spalte dazubekommt.
SELECT c.name, c.city, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
Was psql ausgibt
ERROR: column "c.city" must appear in the GROUP BY clause or be used in an aggregate functionWarum, und die Lösung
Nach GROUP BY steht jede Ergebniszeile für eine ganze Gruppe, also muss jede Spalte in SELECT entweder in GROUP BY stehen oder in einem Aggregat wie COUNT. Nehmen Sie die Spalte in GROUP BY auf (GROUP BY c.name, c.city) oder gruppieren Sie nach dem Schlüssel c.id, der den Rest bestimmt.
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE SUM(o.amount) >= 50
GROUP BY c.name;
Was psql ausgibt
ERROR: aggregate functions are not allowed in WHEREWarum, und die Lösung
WHERE wählt einzelne Zeilen, bevor eine Gruppe gebildet ist, also gibt es noch keine Summe. Ein Test auf die Summe einer Gruppe gehört in HAVING, nach GROUP BY: HAVING SUM(o.amount) >= 50.
PostgreSQL im Browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 und PostgreSQL License. Lizenz und Quellcode
5 Fragen, ohne Hinweise. Ab 80 % ist die Lektion abgeschlossen.
Erledigen Sie zuerst alle Aufgaben oben, um das Abschlussquiz freizuschalten.