Types de jointures SQL

1. Introduction

Dans ce didacticiel, nous montrerons différents types de jointures SQL et comment elles peuvent être facilement implémentées en Java.

2. Définition du modèle

Commençons par créer deux tableaux simples:

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) ); 

Et remplissez-les avec quelques données de test:

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);

Notez que dans notre exemple d'ensemble de données, tous les auteurs n'ont pas d'articles, et vice-versa. Cela jouera un grand rôle dans nos exemples, que nous verrons plus tard.

Définissons également un POJO que nous utiliserons pour stocker les résultats des opérations JOIN tout au long de notre tutoriel:

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

Dans nos exemples, nous allons extraire un titre de la table ARTICLE et les données des auteurs de la table AUTHOR.

3. Configuration

Pour nos exemples, nous utiliserons une base de données PostgreSQL externe fonctionnant sur le port 5432. Hormis le FULL JOIN, qui n'est pris en charge ni dans MySQL ni dans H2, tous les extraits fournis devraient fonctionner avec n'importe quel fournisseur SQL.

Pour notre implémentation Java, nous aurons besoin d'un pilote PostgreSQL:

 org.postgresql postgresql 42.2.5 test 

Commençons par configurer une java.sql.Connection pour qu'elle fonctionne avec notre base de données:

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

Ensuite, créons une classe DAO et quelques méthodes utilitaires:

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; } }

Dans cet article, nous n'entrerons pas dans les détails sur l'utilisation de ResultSet, Statement et Connection. Ces sujets sont traités dans nos articles relatifs à JDBC.

Commençons par explorer les jointures SQL dans les sections ci-dessous.

4. Jointure intérieure

Commençons par probablement le type de jointure le plus simple. L'INNER JOIN est une opération qui sélectionne les lignes correspondant à une condition fournie dans les deux tables. La requête comprend au moins trois parties: sélectionner des colonnes, joindre des tables et condition de jointure.

Gardant cela à l'esprit, la syntaxe elle-même devient assez simple:

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

Nous pouvons également illustrer le résultat de INNER JOIN en tant que partie commune d'ensembles croisés:

Implémentons maintenant la méthode pour INNER JOIN dans la classe ArticleWithAuthorDAO :

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); }

Et testez-le:

@Test public void whenQueryWithInnerJoin_thenShouldReturnProperRows() 

Comme nous l'avons mentionné précédemment, INNER JOIN sélectionne uniquement les lignes communes par une condition fournie. En regardant nos encarts, nous voyons que nous avons un article sans auteur et un auteur sans article. Ces lignes sont ignorées car elles ne remplissent pas la condition fournie. En conséquence, nous récupérons quatre résultats joints, et aucun d'entre eux n'a de données d'auteur vides ni de titre vide.

5. Jointure gauche

Ensuite, concentrons-nous sur LEFT JOIN. Ce type de jointure sélectionne toutes les lignes de la première table et correspond aux lignes correspondantes de la deuxième table. Lorsqu'il n'y a pas de correspondance, les colonnes sont remplies avec des valeurs nulles .

Avant de plonger dans l'implémentation Java, jetons un coup d'œil à une représentation graphique de LEFT JOIN:

Dans ce cas, le résultat du LEFT JOIN inclut tous les enregistrements de l'ensemble représentant la première table avec des valeurs croisées de la seconde table.

Passons maintenant à l'implémentation Java:

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); }

La seule différence par rapport à l'exemple précédent est que nous avons utilisé le mot clé LEFT au lieu du mot clé INNER.

Avant de tester notre méthode LEFT JOIN, jetons à nouveau un coup d'œil à nos insertions. Dans ce cas, nous recevrons tous les enregistrements de la table ARTICLE et leurs lignes correspondantes de la table AUTHOR. Comme nous l'avons mentionné précédemment, tous les articles n'ont pas encore d'auteur, nous nous attendons donc à avoir des valeurs nulles à la place des données d'auteur:

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

6. Jointure droite

Le RIGHT JOIN ressemble beaucoup à LEFT JOIN, mais il renvoie toutes les lignes de la deuxième table et correspond aux lignes de la première table. Comme dans le cas du LEFT JOIN, les correspondances vides sont remplacées par des valeurs nulles .

La représentation graphique de ce type de jointure est un reflet miroir de celle que nous avons illustrée pour LEFT JOIN:

Implémentons le RIGHT JOIN en Java:

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); }

Encore une fois, regardons nos données de test. Étant donné que cette opération de jointure récupère tous les enregistrements de la deuxième table, nous prévoyons de récupérer cinq lignes, et comme tous les auteurs n'ont pas déjà écrit un article, nous attendons des valeurs nulles dans la colonne TITLE:

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

7. Jointure externe complète

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); }

Une fois de plus, regardons les données de test. Nous avons cinq articles différents, dont l'un n'a pas d'auteur, et quatre auteurs, dont l'un n'a pas d'article attribué. À la suite du FULL JOIN, nous prévoyons de récupérer six lignes. Quatre d'entre eux sont confrontés les uns aux autres, et les deux autres ne le sont pas. Pour cette raison, nous supposons également qu'il y aura au moins une ligne avec des valeurs nulles dans les deux colonnes de données AUTHOR et une avec une valeur nulle dans la colonne TITLE.

8. Conclusion

Dans cet article, nous avons exploré les types de base de jointures SQL. Nous avons examiné des exemples de quatre types de jointures et comment ils peuvent être implémentés en Java.

Comme toujours, le code complet utilisé dans cet article est disponible à l'adresse over sur GitHub.