SQL結合の種類

投稿日: 2019-10-19 2022-10-28
タグ: Persistence, 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のいずれでもサポートされていないFULLJOINを除いて、提供されているすべてのスニペットはすべてのSQLプロバイダーで機能するはずです。

Javaの実装には、PostgreSQLドライバーが必要です。

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
    <scope>test</scope>
</dependency>

まず、データベースで動作するように 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;
    }
}

この記事では、使用について詳しくは説明しません。 ResultSet、Statement、 と繋がり。これらのトピックは、 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

交差するセットの一般的な部分としてINNERJOINの結果を示すこともできます：

次に、ArticleWithAuthorDAOクラスにINNERJOINのメソッドを実装しましょう。

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. 左参加

次に、LEFTJOINに焦点を当てましょう。この種類の結合は、最初のテーブルからすべての行を選択し、2番目のテーブルの対応する行と一致します。一致するものがない場合、列はnull値。で埋められます。

Javaの実装に飛び込む前に、LEFTJOINのグラフィック表現を見てみましょう。

この場合、 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. 右参加

RIGHTJOINはLEFTJOINによく似ていますが、2番目のテーブルのすべての行を返し、最初のテーブルの行と一致します。 LEFT JOINの場合と同様に、空の一致はnullに置き換えられます。 値。

この種の結合のグラフィック表現は、LEFTJOINで示したものを反映したものです。

JavaにRIGHTJOINを実装しましょう。

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. フルアウタージョイン

この結合操作はおそらく最もトリッキーな操作です。FULLJOINは、条件が満たされているかどうかに関係なく、最初のテーブルと2番目のテーブルの両方からすべての行を選択します。

交差する各セットのすべての値と同じアイデアを表すこともできます。

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 値があり、TITLE列にnull値がある行が少なくとも1つあると想定しています。

8. 結論

この記事では、SQL結合の基本的なタイプについて説明しました。 4種類の結合の例と、それらをJavaで実装する方法を確認しました。

いつものように、この記事で使用されている完全なコードは、GitHubでから入手できます。

getdocs

13036