重要

このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

複数テーブル結合クエリ

多くのシナリオでは、1 つのクエリを使用して複数のテーブルからデータを取得する必要があります。1 JOINステートメントを使用して、2 つ以上のテーブルからのデータを結合できます。

結合タイプ

このセクションでは、結合タイプについて詳しく説明します。

内部結合

内部結合の結合結果は、結合条件に一致する行のみを返します。

Inner Join

たとえば、最も多作な著者を知りたい場合は、 authorsという名前の著者テーブルとbook_authorsという名前の書籍著者テーブルを結合する必要があります。

SQL
Java

次の SQL 文では、キーワードJOINを使用して、左側のテーブルauthorsと右側のテーブルbook_authorsの行を結合条件a.id = ba.author_idで内部結合として結合することを宣言します。結果セットには、結合条件を満たす行のみが含まれます。著者が本を 1 冊も書いていない場合、テーブルauthorsの著者のレコードは結合条件を満たさないため、結果セットには表示されません。

SELECT ANY_VALUE(a.id) AS author_id, ANY_VALUE(a.name) AS author_name, COUNT(ba.book_id) AS books
FROM authors a
JOIN book_authors ba ON a.id = ba.author_id
GROUP BY ba.author_id
ORDER BY books DESC
LIMIT 10;

クエリ結果は次のとおりです。

+------------+----------------+-------+
| author_id  | author_name    | books |
+------------+----------------+-------+
|  431192671 | Emilie Cassin  |     7 |
|  865305676 | Nola Howell    |     7 |
|  572207928 | Lamar Koch     |     6 |
| 3894029860 | Elijah Howe    |     6 |
| 1150614082 | Cristal Stehr  |     6 |
| 4158341032 | Roslyn Rippin  |     6 |
| 2430691560 | Francisca Hahn |     6 |
| 3346415350 | Leta Weimann   |     6 |
| 1395124973 | Albin Cole     |     6 |
| 2768150724 | Caleb Wyman    |     6 |
+------------+----------------+-------+
10 rows in set (0.01 sec)

public List<Author> getTop10AuthorsOrderByBooks() throws SQLException {
    List<Author> authors = new ArrayList<>();
    try (Connection conn = ds.getConnection()) {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("""
        SELECT ANY_VALUE(a.id) AS author_id, ANY_VALUE(a.name) AS author_name, COUNT(ba.book_id) AS books
        FROM authors a
        JOIN book_authors ba ON a.id = ba.author_id
        GROUP BY ba.author_id
        ORDER BY books DESC
        LIMIT 10;
        """);
        while (rs.next()) {
            Author author = new Author();
            author.setId(rs.getLong("author_id"));
            author.setName(rs.getString("author_name"));
            author.setBooks(rs.getInt("books"));
            authors.add(author);
        }
    }
    return authors;
}

左外部結合

左外部結合は、左テーブルのすべての行と、結合条件に一致する右テーブルの値を返します。右テーブルに一致する行がない場合は、 NULLで埋められます。

Left Outer Join

場合によっては、データクエリを完了するために複数のテーブルを使用したいが、結合条件が満たされないためにデータセットが小さくなりすぎないようにしたいことがあります。

たとえば、Bookshop アプリのホームページで、平均評価の新しい本のリストを表示したいとします。この場合、新しい本はまだ誰にも評価されていない可能性があります。内部結合を使用すると、これらの評価されていない本の情報がフィルター処理されてしまいますが、これは期待どおりではありません。

SQL
Java

次の SQL ステートメントでは、キーワードLEFT JOINを使用して、左側のテーブルbooksが右側のテーブルratingsに左外部結合で結合されることを宣言し、テーブルbooksのすべての行が返されるようにします。

SELECT b.id AS book_id, ANY_VALUE(b.title) AS book_title, AVG(r.score) AS average_score
FROM books b
LEFT JOIN ratings r ON b.id = r.book_id
GROUP BY b.id
ORDER BY b.published_at DESC
LIMIT 10;

クエリ結果は次のとおりです。

+------------+---------------------------------+---------------+
| book_id    | book_title                      | average_score |
+------------+---------------------------------+---------------+
| 3438991610 | The Documentary of lion         |        2.7619 |
| 3897175886 | Torey Kuhn                      |        3.0000 |
| 1256171496 | Elmo Vandervort                 |        2.5500 |
| 1036915727 | The Story of Munchkin           |        2.0000 |
|  270254583 | Tate Kovacek                    |        2.5000 |
| 1280950719 | Carson Damore                   |        3.2105 |
| 1098041838 | The Documentary of grasshopper  |        2.8462 |
| 1476566306 | The Adventures of Vince Sanford |        2.3529 |
| 4036300890 | The Documentary of turtle       |        2.4545 |
| 1299849448 | Antwan Olson                    |        3.0000 |
+------------+---------------------------------+---------------+
10 rows in set (0.30 sec)

最近出版された本にはすでに多くの評価が付けられているようです。上記の方法を検証するために、SQL ステートメントを使用して、本「The Documentary of lion」のすべての評価を削除してみましょう。

DELETE FROM ratings WHERE book_id = 3438991610;

再度クエリを実行します。結果セットには依然として「The Documentary of lion」という本が表示されますが、右側の表ratingsのscoreから計算されたaverage_score列目にはNULLが入力されます。

+------------+---------------------------------+---------------+
| book_id    | book_title                      | average_score |
+------------+---------------------------------+---------------+
| 3438991610 | The Documentary of lion         |          NULL |
| 3897175886 | Torey Kuhn                      |        3.0000 |
| 1256171496 | Elmo Vandervort                 |        2.5500 |
| 1036915727 | The Story of Munchkin           |        2.0000 |
|  270254583 | Tate Kovacek                    |        2.5000 |
| 1280950719 | Carson Damore                   |        3.2105 |
| 1098041838 | The Documentary of grasshopper  |        2.8462 |
| 1476566306 | The Adventures of Vince Sanford |        2.3529 |
| 4036300890 | The Documentary of turtle       |        2.4545 |
| 1299849448 | Antwan Olson                    |        3.0000 |
+------------+---------------------------------+---------------+
10 rows in set (0.30 sec)

INNER JOIN使用するとどうなるでしょうか? 試してみるのはあなた次第です。

public List<Book> getLatestBooksWithAverageScore() throws SQLException {
    List<Book> books = new ArrayList<>();
    try (Connection conn = ds.getConnection()) {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("""
        SELECT b.id AS book_id, ANY_VALUE(b.title) AS book_title, AVG(r.score) AS average_score
        FROM books b
        LEFT JOIN ratings r ON b.id = r.book_id
        GROUP BY b.id
        ORDER BY b.published_at DESC
        LIMIT 10;
        """);
        while (rs.next()) {
            Book book = new Book();
            book.setId(rs.getLong("book_id"));
            book.setTitle(rs.getString("book_title"));
            book.setAverageScore(rs.getFloat("average_score"));
            books.add(book);
        }
    }
    return books;
}

右外部結合

右外部結合は、右テーブルのすべてのレコードと、結合条件に一致する左テーブルの値を返します。一致する値がない場合は、 NULLで埋められます。

Right Outer Join

クロス結合

結合条件が定数の場合、2 つのテーブル間の内部結合はクロス結合と呼ばれます。クロス結合は、左側のテーブルのすべてのレコードを右側のテーブルのすべてのレコードに結合します。左側のテーブルのレコード数がmで、右側のテーブルのレコード数がnの場合、結果セットにはm \* nレコードが生成されます。

左セミ結合

TiDB は、SQL 構文レベルではLEFT SEMI JOIN table_nameサポートしていません。ただし、実行プランレベルでは、サブクエリ関連の最適化、書き換えられた同等の JOIN クエリのデフォルトの結合方法としてsemi joinを使用します。

暗黙的な結合

明示的に結合を宣言するJOIN文が SQL 標準に追加される前は、 FROM t1, t2句を使用して SQL 文で 2 つ以上のテーブルを結合し、 WHERE t1.id = t2.id句を使用して結合の条件を指定することができました。これは、内部結合を使用してテーブルを結合する暗黙的な結合として理解できます。

TiDB は、次の一般的なテーブル結合アルゴリズムをサポートしています。

オプティマイザーは、結合されたテーブルのデータ量などの要素に基づいて、実行する適切な結合アルゴリズムを選択します。 EXPLAINステートメントを使用すると、クエリが結合に使用するアルゴリズムを確認できます。

TiDB のオプティマイザーが最適な結合アルゴリズムに従って実行されない場合は、オプティマイザのヒント使用して、TiDB により適切な結合アルゴリズムを使用するように強制できます。

たとえば、上記の左結合クエリの例が、オプティマイザによって選択されていないハッシュ結合アルゴリズムを使用してより速く実行されると仮定すると、 SELECTキーワードの後にヒント/*+ HASH_JOIN(b, r) */を追加できます。テーブルに別名がある場合は、ヒントで別名を使用することに注意してください。

EXPLAIN SELECT /*+ HASH_JOIN(b, r) */ b.id AS book_id, ANY_VALUE(b.title) AS book_title, AVG(r.score) AS average_score
FROM books b
LEFT JOIN ratings r ON b.id = r.book_id
GROUP BY b.id
ORDER BY b.published_at DESC
LIMIT 10;

結合アルゴリズムに関連するヒント:

注文を結合する

実際のビジネスシナリオでは、複数のテーブルの結合ステートメントが非常に一般的です。結合の実行効率は、結合内の各テーブルの順序に関係します。TiDB は、結合したテーブルの再配置アルゴリズムを使用して、複数のテーブルを結合する順序を決定します。

オプティマイザによって選択された結合順序が予想どおりに最適でない場合は、 STRAIGHT_JOIN使用して、TiDB がFROM句で使用されるテーブルの順序でクエリを結合するように強制できます。

EXPLAIN SELECT *
FROM authors a STRAIGHT_JOIN book_authors ba STRAIGHT_JOIN books b
WHERE b.id = ba.book_id AND ba.author_id = a.id;

この結合したテーブルの再配置アルゴリズムの実装の詳細と制限の詳細については、結合したテーブルの再配置アルゴリズムの紹介を参照してください。