SQL結合のタイプ

1. 前書き

このチュートリアルでは、さまざまな種類のSQL結合と、それらをJavaで簡単に実装する方法を示します。

2. モデルの定義

2つの簡単なテーブルを作成することから始めましょう。
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)
);
そして、それらにいくつかのテストデータを入力します。
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);
サンプルデータセットでは、すべての著者が記事を持っているわけではなく、その逆もあることに注意してください。 これは例で大きな役割を果たしますが、これについては後で説明します。
また、チュートリアル全体でJOIN操作の結果を保存するために使用するPOJOを定義します。
class ArticleWithAuthor {

    private String title;
    private String authorFirstName;
    private String authorLastName;

    // standard constructor, setters and getters
}
この例では、ARTICLEテーブルからタイトルを抽出し、AUTHORテーブルから著者データを抽出します。

3. 構成

例では、ポート5432で実行されている外部PostgreSQLデータベースを使用します。 * MySQLまたはH2のいずれでもサポートされていないFULL JOINを除き、提供されたすべてのスニペットは任意のSQLプロバイダーで動作するはずです。*
Javaの実装には、https://search.maven.org/classic/#search%7Cga%7C1%7Cg%3A%22org.postgresql%22%20AND%20a%3A%22postgresql%22 [PostgreSQLドライバ]:
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
    <scope>test</scope>
</dependency>
まず、データベースと連携するようにa _java.sql.Connection_を構成しましょう。
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.
  getConnection("jdbc:postgresql://localhost:5432/myDb", "user", "pass");
次に、DAOクラスといくつかのユーティリティメソッドを作成しましょう。
class ArticleWithAuthorDAO {

    private final Connection connection;

    // constructor

    private List<ArticleWithAuthor> 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<ArticleWithAuthor> mapToList(ResultSet resultSet) throws SQLException {
        List<ArticleWithAuthor> 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;
    }
}
この記事では、_https://www.baeldung.com/jdbc-resultset [ResultSet]、Statement、_、および_Connection._の使用について詳しく説明しません。これらのトピックについては、https://www.baeldung .com / java-jdbc [JDBC]関連記事。
以下のセクションでSQL結合の探索を始めましょう。

4. 内部結合

おそらく最も単純なタイプの結合から始めましょう。 INNER JOINは、指定された条件に一致する行を両方のテーブルから選択する操作です。 クエリは、選択列、結合テーブル、結合条件の少なくとも3つの部分で構成されます。
これを念頭に置いて、構文自体は非常に簡単になります。
SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME
  FROM ARTICLE INNER JOIN AUTHOR
  ON AUTHOR.ID=ARTICLE.AUTHOR_ID
交差セットの共通部分としての* INNER JOINの結果も説明できます。*
link:/uploads/inner_join-100x61.png%20100w []
_ArticleWithAuthorDAO_クラスのINNER JOINのメソッドを実装しましょう:
List<ArticleWithAuthor> 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);
}
そしてそれをテストします。
@Test
public void whenQueryWithInnerJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleInnerJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(4);
    assertThat(articleWithAuthorList)
      .noneMatch(row -> row.getAuthorFirstName() == null || row.getTitle() == null);
}
前述したように、INNER JOINは指定された条件によって共通の行のみを選択します。 挿入物を見ると、著者のいない記事が1つと記事のない著者が1人いることがわかります。 これらの行は指定された条件を満たさないためスキップされます。 その結果、4つの結合された結果を取得しますが、空の著者データも空のタイトルもありません。

5. 左結合

次に、LEFT JOINに注目しましょう。 この種類の結合では、最初のテーブルのすべての行が選択され、2番目のテーブルの対応する行と一致します。 一致しない場合、列は_null_ values __.__で埋められます
Java実装に飛び込む前に、LEFT JOINのグラフィカルな表現を見てみましょう。
link:/uploads/left_join-100x61.png%20100w []
この場合、* LEFT JOINの結果には、最初のテーブルを表すセットのすべてのレコードと、2番目のテーブルの交差する値が含まれます。*
それでは、Javaの実装に移りましょう。
List<ArticleWithAuthor> 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);
}
前の例との唯一の違いは、INNERキーワードの代わりにLEFTキーワードを使用したことです。
LEFT JOINメソッドをテストする前に、挿入物をもう一度見てみましょう。 この場合、ARTICLEテーブルからすべてのレコードを受け取り、AUTHORテーブルから一致する行を受け取ります。 前述したように、すべての記事にまだ著者がいるわけではないため、著者データの代わりに_null_値が含まれることが期待されます。
@Test
public void whenQueryWithLeftJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(5);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}

6. 右結合

  • RIGHT JOINはLEFT JOINに非常に似ていますが、2番目のテーブルのすべての行を返し、最初のテーブルの行に一致します。* LEFT JOINの場合と同様に、空の一致は_null_値に置き換えられます。

    この種の結合のグラフィカルな表現は、LEFT JOINで示したものの鏡面反射です。
    link:/uploads/right_join-100x61.png%20100w []
    JavaでRIGHT JOINを実装しましょう。
List<ArticleWithAuthor> 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);
}
繰り返しますが、テストデータを見てみましょう。 この結合操作は2行目のテーブルからすべてのレコードを取得するため、5行を取得すると予想されます。また、すべての著者が記事をすでに書いているわけではないため、TITLE列にいくつかの_null_値が予想されます:
@Test
public void whenQueryWithRightJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(5);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
}

7. 完全外部結合

この結合操作はおそらく最も難しい操作です。 * FULL JOINは、条件が満たされているかどうかに関係なく、最初のテーブルと2番目のテーブルの両方からすべての行を選択します。*
交差する各セットのすべての値と同じ考え方を表すこともできます。
link:/uploads/full_join-100x61.png%20100w []
Javaの実装を見てみましょう。
List<ArticleWithAuthor> 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);
}
これで、メソッドをテストできます。
@Test
public void whenQueryWithFullJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(6);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}
もう一度、テストデータを見てみましょう。 5つの異なる記事があり、そのうちの1人には著者がいません。4人の著者には1つの記事が割り当てられていません。 FULL JOINの結果として、6行を取得する予定です。 それらの4つは互いに一致し、残りの2つは一致しません。 そのため、AUTHORデータ列に_null_値を持つ行が少なくとも1つ、TITLE列に_null_値を持つ行が少なくとも1つあると想定しています。

8. 結論

この記事では、SQL結合の基本的なタイプを検討しました。 4種類の結合の例と、それらをJavaで実装する方法を検討しました。
いつものように、この記事で使用される完全なコードはhttps://github.com/eugenp/tutorials/tree/master/persistence-modules/core-java-persistence[GitHubで]から入手できます。