Arten von SQL-Joins

1. Einleitung

In diesem Tutorial zeigen wir verschiedene Arten von SQL-Joins und wie sie einfach in Java implementiert werden können.

2. Modell definieren

Beginnen wir mit der Erstellung von zwei einfachen Tabellen:

CREATE TABLE AUTHOR ( ID int NOT NULL PRIMARY KEY, FIRST_NAME varchar(255), LAST_NAME varchar(255) ); CREATE TABLE ARTICLE ( ID int NOT NULL PRIMARY KEY, TITLE varchar(255) NOT NULL, AUTHOR_ID int, FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(ID) ); 

Und füllen Sie sie mit einigen Testdaten:

INSERT INTO AUTHOR VALUES (1, 'Siena', 'Kerr'), (2, 'Daniele', 'Ferguson'), (3, 'Luciano', 'Wise'), (4, 'Jonas', 'Lugo'); INSERT INTO ARTICLE VALUES (1, 'First steps in Java', 1), (2, 'SpringBoot tutorial', 1), (3, 'Java 12 insights', null), (4, 'SQL JOINS', 2), (5, 'Introduction to Spring Security', 3);

Beachten Sie, dass in unserem Beispieldatensatz nicht alle Autoren Artikel haben und umgekehrt. Dies wird eine große Rolle in unseren Beispielen spielen, die wir später sehen werden.

Definieren wir auch ein POJO, das wir zum Speichern der Ergebnisse von JOIN-Operationen in unserem Tutorial verwenden:

class ArticleWithAuthor { private String title; private String authorFirstName; private String authorLastName; // standard constructor, setters and getters }

In unseren Beispielen extrahieren wir einen Titel aus der ARTIKEL-Tabelle und Autorendaten aus der AUTHOR-Tabelle.

3. Konfiguration

In unseren Beispielen verwenden wir eine externe PostgreSQL-Datenbank, die auf Port 5432 ausgeführt wird. Abgesehen von FULL JOIN, das weder in MySQL noch in H2 unterstützt wird, sollten alle bereitgestellten Snippets mit jedem SQL-Anbieter funktionieren.

Für unsere Java-Implementierung benötigen wir einen PostgreSQL-Treiber:

 org.postgresql postgresql 42.2.5 test 

Konfigurieren wir zunächst eine java.sql.Connection für die Arbeit mit unserer Datenbank:

Class.forName("org.postgresql.Driver"); Connection connection = DriverManager. getConnection("jdbc:postgresql://localhost:5432/myDb", "user", "pass");

Als Nächstes erstellen wir eine DAO-Klasse und einige Dienstprogrammmethoden:

class ArticleWithAuthorDAO { private final Connection connection; // constructor private List executeQuery(String query) { try (Statement statement = connection.createStatement()) { ResultSet resultSet = statement.executeQuery(query); return mapToList(resultSet); } catch (SQLException e) { e.printStackTrace(); } return new ArrayList(); } private List mapToList(ResultSet resultSet) throws SQLException { List list = new ArrayList(); while (resultSet.next()) { ArticleWithAuthor articleWithAuthor = new ArticleWithAuthor( resultSet.getString("TITLE"), resultSet.getString("FIRST_NAME"), resultSet.getString("LAST_NAME") ); list.add(articleWithAuthor); } return list; } }

In diesem Artikel werden wir nicht auf Details zur Verwendung von ResultSet, Statement und Connection eingehen . Diese Themen werden in unseren JDBC-Artikeln behandelt.

Beginnen wir in den folgenden Abschnitten mit der Untersuchung von SQL-Joins.

4. Inner Join

Beginnen wir mit der möglicherweise einfachsten Art der Verknüpfung. Der INNER JOIN ist eine Operation, die Zeilen aus beiden Tabellen auswählt, die einer bereitgestellten Bedingung entsprechen. Die Abfrage besteht aus mindestens drei Teilen: Spalten auswählen, Tabellen verknüpfen und Verknüpfungsbedingung.

In Anbetracht dessen wird die Syntax selbst ziemlich einfach:

SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME FROM ARTICLE INNER JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID

Wir können auch das Ergebnis von INNER JOIN als einen gemeinsamen Teil sich überschneidender Mengen veranschaulichen :

Implementieren wir nun die Methode für INNER JOIN in der ArticleWithAuthorDAO- Klasse:

List articleInnerJoinAuthor() { String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME " + "FROM ARTICLE INNER JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID"; return executeQuery(query); }

Und testen Sie es:

@Test public void whenQueryWithInnerJoin_thenShouldReturnProperRows() 

Wie bereits erwähnt, wählt INNER JOIN nur gemeinsame Zeilen anhand einer bereitgestellten Bedingung aus. Wenn wir uns unsere Beilagen ansehen, sehen wir, dass wir einen Artikel ohne Autor und einen Autor ohne Artikel haben. Diese Zeilen werden übersprungen, da sie die angegebene Bedingung nicht erfüllen. Infolgedessen rufen wir vier verknüpfte Ergebnisse ab, von denen keines leere Autorendaten oder leere Titel enthält.

5. Links beitreten

Als nächstes konzentrieren wir uns auf den LEFT JOIN. Diese Art der Verknüpfung wählt alle Zeilen aus der ersten Tabelle aus und stimmt mit den entsprechenden Zeilen aus der zweiten Tabelle überein. Denn wenn es keine Übereinstimmung gibt, werden die Spalten gefüllt mit Nullwerten .

Bevor wir uns mit der Java-Implementierung befassen, werfen wir einen Blick auf eine grafische Darstellung des LEFT JOIN:

In diesem Fall enthält das Ergebnis von LEFT JOIN jeden Datensatz aus der Menge, der die erste Tabelle mit sich überschneidenden Werten aus der zweiten Tabelle darstellt.

Kommen wir nun zur Java-Implementierung:

List articleLeftJoinAuthor() { String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME " + "FROM ARTICLE LEFT JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID"; return executeQuery(query); }

Der einzige Unterschied zum vorherigen Beispiel besteht darin, dass wir das Schlüsselwort LEFT anstelle des Schlüsselworts INNER verwendet haben.

Bevor wir unsere LEFT JOIN-Methode testen, werfen wir noch einmal einen Blick auf unsere Beilagen. In diesem Fall erhalten wir alle Datensätze aus der ARTICLE-Tabelle und die entsprechenden Zeilen aus der AUTHOR-Tabelle. Wie bereits erwähnt, hat noch nicht jeder Artikel einen Autor. Daher erwarten wir Nullwerte anstelle der Autorendaten:

@Test public void whenQueryWithLeftJoin_thenShouldReturnProperRows() { List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor(); assertThat(articleWithAuthorList).hasSize(5); assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null); }

6. Right Join

Der RIGHT JOIN ähnelt dem LEFT JOIN, gibt jedoch alle Zeilen aus der zweiten Tabelle zurück und stimmt mit den Zeilen aus der ersten Tabelle überein. Wie beim LEFT JOIN werden leere Übereinstimmungen durch Nullwerte ersetzt .

Die grafische Darstellung dieser Art von Verknüpfung ist eine Spiegelung derjenigen, die wir für die LEFT JOIN dargestellt haben:

Lassen Sie uns den RIGHT JOIN in Java implementieren:

List articleRightJoinAuthor() { String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME " + "FROM ARTICLE RIGHT JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID"; return executeQuery(query); }

Schauen wir uns noch einmal unsere Testdaten an. Da diese Verknüpfungsoperation alle Datensätze aus der zweiten Tabelle abruft, erwarten wir, dass fünf Zeilen abgerufen werden. Da nicht jeder Autor bereits einen Artikel geschrieben hat, erwarten wir einige Nullwerte in der Spalte TITLE:

@Test public void whenQueryWithRightJoin_thenShouldReturnProperRows() { List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor(); assertThat(articleWithAuthorList).hasSize(5); assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null); }

7. Vollständige äußere Verbindung

This join operation is probably the most tricky one. The FULL JOIN selects all rows from both the first and the second table regardless of whether the condition is met or not.

We can also represent the same idea as all values from each of the intersecting sets:

Let's have a look at the Java implementation:

List articleOuterJoinAuthor() { String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME " + "FROM ARTICLE FULL JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID"; return executeQuery(query); }

Now, we can test our method:

@Test public void whenQueryWithFullJoin_thenShouldReturnProperRows() { List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor(); assertThat(articleWithAuthorList).hasSize(6); assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null); assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null); }

Schauen wir uns noch einmal die Testdaten an. Wir haben fünf verschiedene Artikel, von denen einer keinen Autor hat, und vier Autoren, von denen einer keinen zugewiesenen Artikel hat. Aufgrund des FULL JOIN erwarten wir, sechs Zeilen abzurufen. Vier von ihnen werden gegeneinander abgeglichen, die anderen beiden nicht. Aus diesem Grund gehen wir auch davon aus, dass in beiden AUTHOR-Datenspalten mindestens eine Zeile mit Nullwerten und in der Spalte TITLE eine Zeile mit einem Nullwert vorhanden ist.

8. Fazit

In diesem Artikel haben wir die grundlegenden Arten von SQL-Joins untersucht. Wir haben uns Beispiele für vier Arten von Joins angesehen und wie sie in Java implementiert werden können.

Wie immer ist der vollständige Code, der in diesem Artikel verwendet wird, auf GitHub verfügbar.