Was ist FULL JOIN in SQL? Eine Erläuterung mit 4 Beispielen

Was ist ein FULL JOIN - einer der oft vernachlässigten SQL-Join-Typen? In diesem Artikel zeigen wir Ihnen, wie nützlich FULL JOIN sein kann und lehren Sie, wie Sie ihn in verschiedenen Szenarien anwenden können. Die vier Beispiele decken einige der typischen Anwendungen ab.

Ein SQL-Join ist eine Konstruktion zur Kombination von Daten aus zwei oder mehr Tabellen. Der FULL JOIN ist eine der Arten von Verknüpfungen. Sie werden am meisten von diesem Artikel profitieren, wenn Sie bereits mit SQL-Joins und deren Funktionsweise vertraut sind. Wenn nicht oder wenn Sie Ihr Wissen auffrischen müssen, empfehlen wir Ihnen unseren interaktiven SQL-JOINs Kurs, um die Lücken zu schließen. Dort erwarten Sie dreiundneunzig Übungen, in denen Sie Join-Typen rekapitulieren und das Filtern von Daten mithilfe von Einfach- und Mehrfach-Joins, Self-Joins und Non-Equi-Joins üben können.

Was ist ein FULL JOIN?

FULL JOIN oder FULL OUTER JOIN (SQL akzeptiert beide) ist eine äußere Verknüpfung. In SQL ist eine äußere Verknüpfung eine Art von Verknüpfung, die nicht übereinstimmende Zeilen aus einer oder beiden verknüpften Tabellen enthält; LEFT JOIN und RIGHT JOIN sind ebenfalls äußere Verknüpfungen. FULL JOIN ist eine Vereinigung von LEFT JOIN und RIGHT JOIN: Er zeigt die übereinstimmenden und die nicht übereinstimmenden Zeilen aus beiden Tabellen. Wenn die Werte aus einer Tabelle in der anderen Tabelle nicht übereinstimmen, gibt FULL JOIN NULL s zurück:

FULL JOIN Syntax

Die Syntax von FULL JOIN lautet:

SELECT …
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Wie bei SQL-Joins üblich, bestehen die Hauptbestandteile der FULL JOIN -Syntax aus den Klauseln JOIN und ON. Auf die erste Tabelle in der Klausel FROM folgt das Schlüsselwort FULL JOIN, mit dem die zweite Tabelle verbunden wird. Die Bedingung, unter der die Tabellen verbunden werden, wird in der ON Klausel angegeben. Normalerweise werden Tabellen über eine gemeinsame Spalte verbunden, z. B. die Spalte id in der Tabelle writer Tabelle und die writer_ID Spalte in der book Tabelle. Diese beiden Spalten haben aufgrund der Primärschlüssel-Fremdschlüssel-Beziehung die gleichen Werte.

In einem einfachen Beispiel könnten wir diesen Code schreiben und das folgende Ergebnis erhalten:

Andere Arten von Verknüpfungen in SQL

Wir haben bereits erwähnt, dass FULL JOIN nur eine von mehreren Arten von Joins in SQL ist. Hier ist die vollständige Liste:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

JOIN (oder INNER JOIN) ist ein innerer Join-Typ, der nur die übereinstimmenden Zeilen aus den verbundenen Tabellen zurückgibt. Mehr über INNER JOIN können Sie hier erfahren.

LEFT JOIN (oder LEFT OUTER JOIN) listet alle Zeilen aus der ersten (linken) Tabelle und nur die übereinstimmenden Zeilen aus der zweiten (rechten) Tabelle auf. Wenn es keine übereinstimmenden Zeilen in der rechten Tabelle gibt, werden die Werte als NULL angezeigt. Mehr dazu finden Sie in unserem Artikel über LEFT JOIN.

RIGHT JOIN (oder RIGHT OUTER JOIN) ist das Spiegelbild von LEFT JOIN. Er gibt alle Zeilen aus der zweiten (rechten) Tabelle und nur die übereinstimmenden Zeilen aus der ersten (linken) Tabelle zurück. Wenn es in der linken Tabelle nicht übereinstimmende Zeilen gibt, sind die Werte NULL.

Jetzt können Sie besser verstehen, warum wir gesagt haben, dass FULL JOIN die Vereinigung von LEFT JOIN und RIGHT JOIN ist. Da alle Daten aus der linken und der rechten Tabelle und auch die nicht übereinstimmenden Zeilen angezeigt werden, ist es so, als ob Sie LEFT JOIN und RIGHT JOIN gleichzeitig verwenden.

CROSS JOIN gibt ein kartesisches Produkt zurück. Mit anderen Worten, es gibt alle Kombinationen aller Zeilen aus den beiden verbundenen Tabellen zurück.

FULL JOIN Beispiele

Gehen wir nun zu den Beispielen über, die die typische Verwendung von FULL JOIN zeigen.

Beispiel 1: Alle Mitarbeiter und Abteilungen des Unternehmens anzeigen

Aufgabe: Sie müssen alle Mitarbeiter eines Unternehmens anzeigen, auch wenn sie keine zugewiesene Abteilung haben. Außerdem sollen alle Abteilungen angezeigt werden, auch wenn ihnen keine Mitarbeiter zugeordnet sind.

Datensatz: Wir verwenden zwei Tabellen, um das Problem zu lösen. Die erste Tabelle ist Abteilung (das Skript finden Sie hier), die eine Liste der Abteilungen des Unternehmens enthält:

id	department_name
1	IT
2	Accounting
3	Sales

Die zweite Tabelle ist die Tabelle " Mitarbeiter" mit den unten aufgeführten Daten. Das Skript finden Sie hier:

id	first_name	last_name	department_id
1	Sarah	Zimmerman	2
2	Thomas	Tyson	1
3	Daniel	Richardson	1
4	Sofia	Tardelli	2
5	Mark	Fitzpatrick	4

Lösung:

SELECT 
  first_name,
  last_name,
  department_name
FROM employees e
FULL JOIN department d
  ON e.department_id = d.id;

Erläuterung: Der Code wählt die Vor- und Nachnamen der Mitarbeiter sowie den Abteilungsnamen aus.

Das ist möglich, weil die beiden Tabellen unter FULL JOIN zu finden sind. Wir verweisen auf die Tabelle employees in FROM und geben ihr einen Alias. Dann verweisen wir auf die zweite Tabelle, departmentin FULL JOIN. Die Tabellen werden anhand der Abteilungs-IDs, die in beiden Tabellen zu finden sind, verbunden. Diese Verknüpfungsbedingung ist in der Klausel ON enthalten.

Ausgabe: Die Ausgabe liefert alle Daten aus beiden Tabellen. Es gibt einige interessante Zeilen, die näher erläutert werden müssen.

Mark Fitzpatrick hat keine Abteilung - sein department_id Wert ist 5, aber es gibt keine Abteilung mit der ID 5 in der Tabelle department. Daher steht unter dem Abteilungsnamen ein NULL-Wert. Dies könnte daran liegen, dass er ein neuer Mitarbeiter ist und die employees Tabelle nicht mit den Abteilungsdaten aktualisiert wurde.

Außerdem wird in der letzten Zeile die Abteilung "Vertrieb" angezeigt, aber kein Name des Mitarbeiters. Dies bedeutet, dass die Abteilung Vertrieb keine Mitarbeiter hat. Dies ist nichts Ungewöhnliches, da wir gehört haben, dass alle drei Mitarbeiter der Abteilung Vertrieb vor kurzem das Unternehmen verlassen haben.

first_name	last_name	department_name
Sarah	Zimmerman	Accounting
Thomas	Tyson	IT
Daniel	Richardson	IT
Sofia	Tardelli	Accounting
Mark	Fitzpatrick	NULL
NULL	NULL	Sales

Beispiel 2: Alle Kunden mit der Anzahl der gekauften Produkte und der Anzahl der Produkte ohne Verkauf anzeigen

Aufgabe: Es gibt einen Internet-Schallplattenladen, der Vinyls (d.h. Musikplatten) verkauft. Ihre Aufgabe ist es, alle Kunden aufzulisten und die Anzahl der Schallplatten zu ermitteln, die jeder Kunde gekauft hat. Die Ausgabe muss auch die Anzahl der Schallplatten anzeigen, die noch niemand gekauft hat.

Datensatz: Wir werden mit drei Tabellen arbeiten. Die erste ist customermit dem Skript hier:

id	first_name	last_name
1	Marvin	Simmons
2	Marianne	Dickens
3	Susan	Strozzi

Das Skript für die zweite Tabelle, vinyl, finden Sie hier. Die Tabelle zeigt die Liste der Vinyls, wie Sie unten sehen können:

id	artist	album_name	price
1	Callier, Terry	What Color is Love	24.99
2	Guy, Buddy	Sweet Tea	32.99
3	Little Simz	A Curious Tale of Trials	32.99
4	LaVette, Bettye	Scene of the Crime	36.99

Die dritte Tabelle ist eine Kreuzungstabelle namens purchase. Das Skript finden Sie hier:

id	customer_id	vinyl_id	purchase_date
1	1	2	2023-01-03
2	1	3	2023-01-12
3	1	2	2023-02-18
4	1	3	2023-03-01
5	2	3	2023-03-01
6	2	2	2023-04-01
7	2	4	2023-05-01

Lösung:

SELECT 
  first_name,
  last_name,
  COUNT (v.id) AS vinyl_count	   
FROM customer c
FULL JOIN purchase p
  ON c.id = p.customer_id
FULL JOIN vinyl v
  ON p.vinyl_id = v.id
GROUP BY first_name, last_name;

Erläuterung: Dies ist ein Beispiel für die Verwendung von FULL JOIN sowie für die Verknüpfung von mehr als zwei Tabellen.

Die Abfrage wählt Kundennamen aus. Dann wird die Aggregatfunktion COUNT() verwendet, um die Anzahl der gekauften Vinyls zu ermitteln. FULL JOIN die customer Tabelle mit der purchase Tabelle. Dies geschieht anhand der Kunden-IDs in beiden Tabellen.

Nun müssen wir die dritte Tabelle verknüpfen. Das ist ganz einfach: Schreiben Sie wieder FULL JOIN und verweisen Sie auf die Tabelle vinyl. Jetzt können Sie sie mit der Tabelle purchase über die Vinyl-IDs verknüpfen.

Ausgabe: Die Ausgabe zeigt, dass es eine Schallplatte gibt, die noch niemand bestellt hat. Sie können sie an den NULLen erkennen.

Sie zeigt auch eine Liste aller Kunden und die Anzahl der von ihnen gekauften Schallplatten. Susan Strozzi hat nichts gekauft. Marianne Dickens und Marvin Simmons haben drei bzw. vier Vinyls gekauft.

first_name	last_name	vinyl_count
NULL	NULL	1
Susan	Strozzi	0
Marianne	Dickens	3
Marvin	Simmons	4

Beispiel 3: Auflistung aller Schüler-Zertifikatspaare und der Noten der Schüler

Aufgabe: Sie arbeiten für eine Online-SQL-Zertifizierungsplattform. Sie bietet mehrere Zertifizierungen an; jedes Jahr gibt es eine neue Ausgabe jeder Zertifizierung. Finden Sie alle möglichen Schüler-Zertifizierungs-Paare und die Note, die jeder Schüler in allen Ausgaben der Zertifizierung erhalten hat.

Datensatz: Der Datensatz wird immer größer; er umfasst jetzt vier Tabellen. Die erste Tabelle ist student; hier ist das Skript. Es ist eine Liste der Studenten der Plattform:

unsere Vinyls, beziehungsweise.

id	first_name	last_name
1	Tom	Frank
2	Mary	Maddison
3	Pavel	Kuba
4	Amanda	Wilson

Die zweite Tabelle ist die Zertifikatstabelle, die die Liste der Zertifikate enthält. Hier ist das Skript:

id	certificate_name
1	Microsoft Certified: Azure Data Fundamentals
2	Oracle Database SQL Certified Associate Certification
3	IBM Certified Database Associate
4	MySQL 5.7 Database Administrator Certification
5	EDB PostgreSQL 12 Associate Certification

Die nächste Tabelle ist eine Kreuzungstabelle namens certificate_enrollment

id	student_id	edition_id	grade	pass
1	2	1	620	FALSE
2	2	6	850	TRUE
3	2	10	900	TRUE
4	1	2	100	FALSE
5	1	7	500	FALSE
6	1	7	800	TRUE
7	4	8	800	TRUE

. Sie finden das Skript hier. Die Tabelle zeigt, welche Schüler sich für welchen Schein eingeschrieben haben, zusammen mit ihren Noten und ob sie bestanden haben:

Die letzte Tabelle ist eine Kreuzungstabelle mit dem Namen certificate_edition. Sie zeigt die Liste der Zertifikatsausgaben und verweist auf die Tabelle certificate. Das Skript finden Sie hier:

id	certificate_id	edition
1	1	2022
2	2	2022
3	3	2022
4	4	2022
5	5	2022
6	1	2023
7	2	2023
8	3	2023
9	4	2023
10	5	2023

Lösung:

SELECT 
  first_name,
  last_name,
  certificate_name,
  edition,
  grade
FROM student s
FULL JOIN certificate_enrollment cen
  ON s.id = cen.student_id
FULL JOIN certificate_edition ced
  ON cen.edition_id = ced.id
FULL JOIN certificate c
  ON ced.certificate_id = c.id;

Erläuterung: Wir wählen zunächst alle relevanten Spalten aus allen vier Tabellen aus: first_name und last_name from student, certificate_name aus certificate, edition aus certificate_edition, und grade aus certificate_enrollment.

Dann verbinden wir die Tabellen wie in den vorherigen Beispielen. Zuerst ist es die student Tabelle FULL JOINed mit certificate_enrollment auf die Schüler-IDs.

Die zweite FULL JOIN fügt die certificate_edition Tabelle hinzu, um sie zu verbinden mit certificate_enrollment. Die Tabellen werden über die ID der Zertifikatsausgabe verbunden.

Nachdem wir nun drei Tabellen verbunden haben, können wir die vierte Tabelle hinzufügen. Wir verweisen auf die Tabelle certificate in FULL JOIN. Wir verknüpfen sie mit certificate_edition über die Zertifikats-ID.

Ausgabe: Die Ausgabe zeigt genau das, was wir wollen.

Es gibt eine Liste aller Schüler, die ein Zertifikat erhalten haben, dessen Ausgabe und die Note, die jeder Schüler erhalten hat. Wenn die Note unter 700 liegt, ist der Schüler durchgefallen und muss die Zertifikatsprüfung wiederholen.

Ein Schüler hat sich noch nicht für ein Zertifikatsprogramm eingeschrieben; das ist Pavel Kuba. Außerdem gibt es vier Zertifikatseditionen ohne eingeschriebene Schüler.

first_name	last_name	certificate_name	edition	grade
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2022	620
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2023	850
Mary	Maddison	EDB PostgreSQL 12 Associate Certification	2023	900
Tom	Frank	Oracle Database SQL Certified Associate Certification	2022	100
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	500
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	800
Amanda	Wilson	IBM Certified Database Associate	2023	800
Pavel	Kuba	NULL	NULL	NULL
NULL	NULL	EDB PostgreSQL 12 Associate Certification	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2022	NULL
NULL	NULL	IBM Certified Database Associate	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2023	NULL

Beispiel 4: Alle Autoren-Themen-Paare finden und die Artikel und ihren Status anzeigen

Aufgabe: Sie arbeiten für ein Unternehmen, das Artikelautoren für seine Kunden einstellt. Es gibt eine Liste von Autoren und Themen, die Sie anbieten. Außerdem gibt es zu jedem Thema mehrere verfügbare Artikel. Die Autoren werden diesen Artikeln zugewiesen. Ein Artikel kann einen von zwei Status haben: "Schreiben" (der Artikel wird gerade geschrieben) oder "Überarbeiten" (der Artikel wird überarbeitet).

Finden Sie alle Autoren-Themen-Paare. Listen Sie außerdem alle Artikel und ihren Status auf.

Datensatz: Dieser Datensatz besteht ebenfalls aus vier Tabellen. Die erste Tabelle ist writermit dem hier verlinkten Skript:

id	first_name	last_name
1	Victoria	Thompson
2	Mike	McGill
3	Sky	Herrera
4	Jimmy	Goodman

Die nächste Tabelle ist topic. Hier ist das Skript:

id	topic_name
1	SQL
2	Python
3	ML
4	SQL Careers
5	Python Careers

Die dritte Tabelle ist article_assignment. Sie verbindet den Autor mit dem Artikel und zeigt den aktuellen Status des Artikels an. Hier ist das Skript:

id	writer_id	article_id	status
1	4	3	Revising
2	4	1	Writing
3	3	5	Writing
4	3	8	Revising
5	3	10	Revising
6	1	7	Writing

Die vierte Tabelle verknüpft den Artikel mit dem Thema. Sie heißt articleund das Skript befindet sich hier:

id	article_title	topic_id
1	What is FULL JOIN in SQL? An Explanation with 4 Examples	1
2	Pandas in Python	2
3	Supervised Learning	3
4	Basic SQL Interview Questions	4
5	Basic Python Interview Questions	5
6	SQL Fensterfunktionen	1
7	Ranking Data in Python	2
8	Unsupervised Learning	3
9	Intermediate SQL Interview Questions	4
10	Intermediate Python Interview Questions	5

Lösung:

SELECT 
  first_name,
  last_name,
  topic_name,
  article_title,
  status
FROM writer w
FULL JOIN article_assignment aa
  ON w.id = aa.writer_id
FULL JOIN article a
  ON aa.article_id = a.id
FULL JOIN topic t
  ON a.topic_id = t.id;

Erläuterung: Die Abfrage ist ähnlich wie die vorherige. Sie wählt alle relevanten Spalten aus - den Namen des Autors, das Thema, den Titel des Artikels und seinen Status. Danach verbindet sie die Tabellen writer und article_assignment über die Autoren-ID mit FULL OUTER JOIN.

Eine weitere FULL JOIN fügt die dritte Tabelle article und verknüpft sie mit der Artikel-ID. Schließlich wird die vierte Tabelle mit der Themen-ID verknüpft.

Ausgabe: Hier ist eine Liste mit allen Autoren, den Themen, Artikeln und Status.

Die Themen von Jimmy Goodman sind ML und SQL. Sky Herrera schreibt über Python-Karrieren und ML. Victoria Thompson schreibt nur über Python.

Mike McGill hat keine zugewiesenen Artikel. Außerdem gibt es einen Python-, einen SQL- und zwei SQL-Karriere-Artikel, die keinem Autor zugewiesen sind.

first_name	last_name	topic_name	article_title	status
Jimmy	Goodman	ML	Supervised Learning	Revising
Jimmy	Goodman	SQL	What is FULL JOIN in SQL? An Explanation with 4 Examples	Writing
Sky	Herrera	Python Careers	Basic Python Interview Questions	Writing
Sky	Herrera	ML	Unsupervised Learning	Revising
Sky	Herrera	Python Careers	Intermediate Python Interview Questions	Revising
Victoria	Thompson	Python	Ranking Data in Python	Writing
Mike	McGill	NULL	NULL	NULL
NULL	NULL	Python	Pandas in Python	NULL
NULL	NULL	SQL	SQL Fensterfunktionen	NULL
NULL	NULL	SQL Careers	Basic SQL Interview Questions	NULL
NULL	NULL	SQL Careers	Intermediate SQL Interview Questions	NULL

No FULL JOIN, No SQL Mastery

FULL JOIN wird wahrscheinlich nicht so oft verwendet wie JOIN oder LEFT JOIN. Es ist ein bescheidener Join, der wartet, bis man ihn braucht, um zu glänzen. Aber ohne ihn in Ihrem Repertoire können Sie sich nicht als SQL-Meister bezeichnen.

Die obigen Beispiele haben Ihnen praktische Szenarien gezeigt, in denen Sie alle Daten aus zwei (oder mehr) Tabellen verwenden müssen. FULL JOIN macht es wirklich einfach!

Natürlich brauchen Sie noch etwas mehr Übung, denn das Wichtigste bei der Verwendung von SQL-Joins ist die Entscheidung, welcher Join verwendet werden soll. Sobald Sie das beherrschen, werden Joins einfach - die Syntax ist dieselbe, egal welchen Join-Typ Sie verwenden. Unser SQL-JOINs Kurs kann Ihnen bei der Beherrschung von JOINhelfen, da er mit praktischen Übungen gespickt ist. Dasselbe gilt für unseren Artikel über SQL-Joins mit 12 Beispielen. Wenn Sie all das gelernt haben, werden Ihnen auch die Fragen im SQL JOIN-Interview nicht mehr schwer fallen.

Jetzt liegt es an Ihnen, all diese Ressourcen zu nutzen, um FULL JOIN zu meistern!