Tipi di join SQL

1. Introduzione

In questo tutorial, mostreremo diversi tipi di join SQL e come possono essere facilmente implementati in Java.

2. Definizione del modello

Cominciamo creando due semplici tabelle:

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

E riempili con alcuni dati di prova:

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

Tieni presente che nel nostro set di dati di esempio non tutti gli autori hanno articoli e viceversa. Questo giocherà un ruolo importante nei nostri esempi, che vedremo più avanti.

Definiamo anche un POJO che useremo per memorizzare i risultati delle operazioni JOIN durante il nostro tutorial:

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

Nei nostri esempi, estrarremo un titolo dalla tabella ARTICLE e i dati degli autori dalla tabella AUTHOR.

3. Configurazione

Per i nostri esempi, utilizzeremo un database PostgreSQL esterno in esecuzione sulla porta 5432. A parte FULL JOIN, che non è supportato né in MySQL né in H2, tutti gli snippet forniti dovrebbero funzionare con qualsiasi provider SQL.

Per la nostra implementazione Java, avremo bisogno di un driver PostgreSQL:

 org.postgresql postgresql 42.2.5 test 

Configuriamo prima una connessione java.sql.Connection per lavorare con il nostro database:

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

Successivamente, creiamo una classe DAO e alcuni metodi di utilità:

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 questo articolo, non ci immergeremo nei dettagli sull'utilizzo di ResultSet, Statement e Connection. Questi argomenti sono trattati nei nostri articoli correlati a JDBC.

Cominciamo a esplorare i join SQL nelle sezioni seguenti.

4. Inner Join

Cominciamo con forse il tipo più semplice di join. L'INNER JOIN è un'operazione che seleziona le righe che corrispondono a una condizione fornita da entrambe le tabelle. La query è composta da almeno tre parti: selezione di colonne, join di tabelle e condizione di join.

Tenendo presente questo, la sintassi stessa diventa piuttosto semplice:

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

Possiamo anche illustrare il risultato di INNER JOIN come una parte comune di insiemi intersecanti:

Implementiamo ora il metodo per INNER JOIN nella 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); }

E provalo:

@Test public void whenQueryWithInnerJoin_thenShouldReturnProperRows() 

Come accennato in precedenza, INNER JOIN seleziona solo le righe comuni in base a una condizione fornita. Guardando i nostri inserti, vediamo che abbiamo un articolo senza autore e un autore senza articolo. Queste righe vengono ignorate perché non soddisfano la condizione fornita. Di conseguenza, recuperiamo quattro risultati uniti e nessuno di essi ha dati di autori vuoti né titolo vuoto.

5. Unisciti a sinistra

Quindi, concentriamoci su LEFT JOIN. Questo tipo di join seleziona tutte le righe della prima tabella e corrisponde alle righe corrispondenti della seconda tabella. Quando non c'è corrispondenza, le colonne vengono riempite con valori nulli .

Prima di immergerci nell'implementazione di Java, diamo un'occhiata a una rappresentazione grafica del LEFT JOIN:

In questo caso, il risultato del LEFT JOIN include ogni record del set che rappresenta la prima tabella con i valori intersecanti della seconda tabella.

Passiamo ora all'implementazione 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); }

L'unica differenza rispetto all'esempio precedente è che abbiamo utilizzato la parola chiave LEFT invece della parola chiave INNER.

Prima di testare il nostro metodo LEFT JOIN, diamo di nuovo un'occhiata ai nostri inserti. In questo caso, riceveremo tutti i record dalla tabella ARTICLE e le righe corrispondenti dalla tabella AUTHOR. Come accennato in precedenza, non tutti gli articoli hanno ancora un autore, quindi ci aspettiamo di avere valori nulli al posto dei dati dell'autore:

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

6. Right Join

Il RIGHT JOIN è molto simile al LEFT JOIN, ma restituisce tutte le righe della seconda tabella e corrisponde alle righe della prima tabella. Come nel caso del LEFT JOIN, le corrispondenze vuote vengono sostituite da valori nulli .

La rappresentazione grafica di questo tipo di join è un riflesso speculare di quello che abbiamo illustrato per LEFT JOIN:

Implementiamo il RIGHT JOIN in 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); }

Di nuovo, diamo un'occhiata ai nostri dati di prova. Poiché questa operazione di join recupera tutti i record dalla seconda tabella, ci aspettiamo di recuperare cinque righe e poiché non tutti gli autori hanno già scritto un articolo, ci aspettiamo alcuni valori nulli nella colonna TITLE:

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

7. Full Outer Join

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

Ancora una volta, esaminiamo i dati del test. Abbiamo cinque diversi articoli, uno dei quali non ha autore e quattro autori, uno dei quali non ha un articolo assegnato. Come risultato di FULL JOIN, ci aspettiamo di recuperare sei righe. Quattro di loro sono abbinati l'uno contro l'altro e gli altri due no. Per questo motivo, presumiamo anche che ci sarà almeno una riga con valori nulli in entrambe le colonne di dati AUTHOR e una con un valore nullo nella colonna TITLE.

8. Conclusione

In questo articolo, abbiamo esplorato i tipi di base di join SQL. Abbiamo esaminato esempi di quattro tipi di join e come possono essere implementati in Java.

Come sempre, il codice completo utilizzato in questo articolo è disponibile su GitHub.