Typer af SQL Joins

1. Introduktion

I denne vejledning viser vi forskellige typer SQL-sammenføjninger, og hvordan de let kan implementeres i Java.

2. Definition af modellen

Lad os starte med at oprette to enkle tabeller:

Opret TABELFORHAVER (ID int IKKE NULL PRIMÆR NØGLE, FIRST_NAME varchar (255), LAST_NAME varchar (255)); OPRET TABELARTIKEL (ID int IKKE NULL PRIMÆR NØGLE, TITEL varchar (255) IKKE NULL, AUTHOR_ID int, UDENLANDSK TAST (AUTHOR_ID) REFERENCER FORHAVER (ID)); 

Og udfyld dem med nogle testdata:

INDSÆT I FORHOLDSVÆRDIER (1, 'Siena', 'Kerr'), (2, 'Daniele', 'Ferguson'), (3, 'Luciano', 'Wise'), (4, 'Jonas', 'Lugo' ); INDSÆT I ARTIKELVÆRDIER (1, 'Første trin i Java', 1), (2, 'SpringBoot-tutorial', 1), (3, 'Java 12-indsigt', null), (4, 'SQL JOINS', 2) , (5, 'Introduction to Spring Security', 3);

Bemærk, at i vores eksempeldatasæt ikke alle forfattere har artikler og omvendt. Dette vil spille en stor rolle i vores eksempler, som vi vil se senere.

Lad os også definere en POJO, som vi vil bruge til at gemme resultaterne af JOIN-operationer i hele vores tutorial:

klasse ArticleWithAuthor {privat streng titel; private String authorFirstName; private String authorLastName; // standard konstruktør, settere og getters}

I vores eksempler udtrækker vi en titel fra ARTIKEL-tabellen og forfatterdata fra AUTHOR-tabellen.

3. Konfiguration

For vores eksempler bruger vi en ekstern PostgreSQL-database, der kører på port 5432. Bortset fra FULL JOIN, som ikke understøttes i hverken MySQL eller H2, skal alle leverede uddrag fungere med enhver SQL-udbyder.

Til vores Java-implementering har vi brug for en PostgreSQL-driver:

 org.postgresql postgresql 42.2.5 test 

Lad os først konfigurere en java.sql.Tilslutning at arbejde med vores database:

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

Lad os derefter oprette en DAO-klasse og nogle hjælpemetoder:

klasse ArticleWithAuthorDAO {privat endelig forbindelsesforbindelse; // constructor private List executeQuery (strengforespørgsel) {try (Statement statement = connection.createStatement ()) {ResultSet resultSet = statement.executeQuery (query); returner mapToList (resultSet); } fange (SQLException e) {e.printStackTrace (); } returner ny ArrayList (); } privat liste mapToList (ResultSet resultSet) kaster SQLException {List list = new ArrayList (); while (resultSet.next ()) {ArticleWithAuthor articleWithAuthor = ny ArticleWithAuthor (resultSet.getString ("TITLE"), resultSet.getString ("FIRST_NAME"), resultSet.getString ("LAST_NAME")); list.add (articleWithAuthor); } returliste }}

I denne artikel dykker vi ikke ned i detaljer om brug ResultatSæt, erklæring, og Forbindelse. Disse emner er dækket i vores JDBC-relaterede artikler.

Lad os begynde at udforske SQL sammenføjninger i afsnit nedenfor.

4. Indvendig samling

Lad os starte med muligvis den enkleste type deltagelse. INNER JOIN er en operation, der vælger rækker, der matcher en angivet betingelse fra begge tabeller. Forespørgslen består af mindst tre dele: vælg kolonner, deltag tabeller og deltag betingelse.

Med det i tankerne bliver syntaksen i sig selv ret ligetil:

VÆLG ARTIKEL.TITEL, FORHANDLER.LAST_NAME, FORHOLDER.FIRST_NAME FRA ARTIKEL INDRE BLIV MEDLEM FORFATTER PÅ AUTHOR.ID = ARTIKEL.AUTHOR_ID

Vi kan også illustrere resultatet af INNER JOIN som en fælles del af krydsende sæt:

Lad os nu implementere metoden til INNER JOIN i ArticleWithAuthorDAO klasse:

Liste over artiklerInnerJoinAuthor () {String-forespørgsel = "VÆLG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL INDRE MEDLEM FORFATTER PÅ AUTHOR.ID = ARTIKEL.AUTHOR_ID"; returnere executeQuery (forespørgsel); }

Og test det:

@Test offentlig ugyldigt nårQueryWithInnerJoin_thenShouldReturnProperRows () 

Som vi nævnte før, vælger INNER JOIN kun almindelige rækker efter en forudsat betingelse. Når vi ser på vores indsatser, ser vi, at vi har en artikel uden en forfatter og en forfatter uden en artikel. Disse rækker springes over, fordi de ikke opfylder den angivne betingelse. Som et resultat henter vi fire sammenføjede resultater, og ingen af ​​dem har tomme forfatterdata eller tom titel.

5. Venstre deltagelse

Lad os derefter fokusere på VENSTRE MEDLEM. Denne type sammenføjning vælger alle rækker fra den første tabel og matcher de tilsvarende rækker fra den anden tabel. For når der ikke er noget match, udfyldes kolonner med nul værdier.

Før vi dykker ned i Java-implementering, skal vi se på en grafisk gengivelse af LEFT JOIN:

I dette tilfælde er resultatet af LEFT JOIN inkluderer hver post fra sættet, der repræsenterer den første tabel med skærende værdier fra den anden tabel.

Lad os nu gå til Java-implementeringen:

Liste over artiklerLeftJoinAuthor () {String-forespørgsel = "VÆLG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL VENSTRE BLIV MEDLEM FORFATTER PÅ AUTHOR.ID = ARTICLE.AUTHOR_ID"; returnere executeQuery (forespørgsel); }

Den eneste forskel i forhold til det foregående eksempel er, at vi brugte LEFT nøgleordet i stedet for det INNRE nøgleord.

Før vi tester vores LEFT JOIN-metode, skal vi igen se på vores indsatser. I dette tilfælde modtager vi alle poster fra ARTIKEL-tabellen og deres matchende rækker fra AUTHOR-tabellen. Som vi nævnte før, har ikke alle artikler en forfatter endnu, så vi forventer at have nul værdier i stedet for forfatterdata:

@Test offentlig ugyldig nårQueryWithLeftJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (række -> række.getAuthorFirstName () == null); }

6. Right Join

RIGHT JOIN ligner meget LEFT JOIN, men det returnerer alle rækker fra den anden tabel og matcher rækker fra den første tabel. Som i tilfældet med LEFT JOIN erstattes tomme matches af nul værdier.

Den grafiske gengivelse af denne form for sammenføjning er en spejlrefleksion af den, vi har illustreret for VENSTRE MEDLEM:

Lad os implementere RIGHT JOIN i Java:

Liste over artiklerRightJoinAuthor () {String-forespørgsel = "VÆLG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL HØJRE MELD FORFATTER PÅ AUTHOR.ID = ARTICLE.AUTHOR_ID"; returnere executeQuery (forespørgsel); }

Lad os igen se på vores testdata. Da denne tilslutningsoperation henter alle poster fra anden tabel, forventer vi at hente fem rækker, og fordi ikke alle forfattere allerede har skrevet en artikel, forventer vi nogle nul værdier i kolonnen TITEL:

@Test offentlig ugyldig nårQueryWithRightJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (række -> række.getTitle () == null); }

7. Fuld ydre sammenføjning

Denne tilslutningsoperation er sandsynligvis den mest vanskelige. FULL JOIN vælger alle rækker fra både den første og den anden tabel, uanset om betingelsen er opfyldt eller ej.

Vi kan også repræsentere den samme idé som alle værdier fra hvert af de krydsende sæt:

Lad os se på Java-implementeringen:

Liste over artiklerOuterJoinAuthor () {String-forespørgsel = "VÆLG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL FULDT BLIV MEDLEM FORFATTER PÅ AUTHOR.ID = ARTICLE.AUTHOR_ID"; returnere executeQuery (forespørgsel); }

Nu kan vi teste vores metode:

@Test offentlig ugyldig nårQueryWithFullJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (6); assertThat (articleWithAuthorList) .anyMatch (række -> række.getTitle () == null); assertThat (articleWithAuthorList) .anyMatch (række -> række.getAuthorFirstName () == null); }

Lad os endnu en gang se på testdataene. Vi har fem forskellige artikler, hvoraf den ene ikke har nogen forfatter og fire forfattere, hvoraf den ene ikke har nogen tildelt artikel. Som et resultat af FULL JOIN forventer vi at hente seks rækker. Fire af dem matches mod hinanden, og de resterende to ikke. Af den grund antager vi også, at der vil være mindst en række med nul værdier i begge AUTHOR-datakolonner og en med a nul værdi i kolonnen TITLE.

8. Konklusion

I denne artikel udforskede vi de grundlæggende typer af SQL-sammenføjninger. Vi kiggede på eksempler på fire typer sammenføjninger, og hvordan de kan implementeres i Java.

Som altid er den komplette kode, der bruges i denne artikel, tilgængelig på GitHub.