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

マルチテーブル参加クエリ

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

結合タイプ

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

インナージョイン

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

Inner Join

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

  • SQL
  • Java

次のSQLステートメントで、キーワードJOINを使用して、左側のテーブルauthorsと右側のテーブルbook_authorsの行を、結合条件a.id = ba.author_idの内部結合として結合することを宣言します。結果セットには、結合条件を満たす行のみが含まれます。著者が本を書いたことがない場合、 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 Documentaryoflionのすべての評価を削除しましょう。

DELETE FROM ratings WHERE book_id = 3438991610;

もう一度クエリします。ライオンのドキュメンタリーという本はまだ結果セットに表示されていますが、右の表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は、 結合したテーブルの再配置 Reorderアルゴリズムを使用して、複数のテーブルが結合される順序を決定します。

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

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;

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

も参照してください

このページの内容