MERGE JOIN
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
MERGE JOIN 算法与 HASH JOIN 算法具有相似的特性，因为它会读取涉及的所有表的数据（在应用谓词之后）。但它不需要将数据存储在内存中，因为它可以在以预排序顺序获取所有表的数据的情况下“合并”它们，例如，由于连接键上的覆盖索引。一个例子
SELECT * FROM AUTHOR INNER MERGE JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select()
.from(AUTHOR)
.mergeJoin(BOOK)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.fetch();
方言支持
此示例使用 jOOQ
selectFrom(AUTHOR.mergeJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
翻译成以下特定方言的表达式
CockroachDB, SQLServer
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
BOOK.ID,
BOOK.AUTHOR_ID,
BOOK.TITLE,
BOOK.PUBLISHED_IN,
BOOK.LANGUAGE_ID
FROM AUTHOR
INNER MERGE JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
Oracle
SELECT /*+leading(AUTHOR BOOK) use_merge(AUTHOR BOOK)*/
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
BOOK.ID,
BOOK.AUTHOR_ID,
BOOK.TITLE,
BOOK.PUBLISHED_IN,
BOOK.LANGUAGE_ID
FROM AUTHOR
JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
YugabyteDB
SELECT /*+leading(AUTHOR BOOK) MergeJoin(AUTHOR BOOK)*/
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
BOOK.ID,
BOOK.AUTHOR_ID,
BOOK.TITLE,
BOOK.PUBLISHED_IN,
BOOK.LANGUAGE_ID
FROM AUTHOR
JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, DB2, Databricks, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到！