HASH JOIN
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
HASH JOIN 算法使用哈希表来查找一个表在连接另一个表时的键。虽然此算法使用潜在的大型中间数据结构，将所有表中的数据存储在哈希表中（在应用谓词之后），但它可以在线性时间内连接数据，这就是为什么它对于大型数据集来说是一个快速的选择。一个例子
SELECT * FROM AUTHOR INNER HASH JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select()
.from(AUTHOR)
.hashJoin(BOOK)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.fetch();
方言支持
此示例使用 jOOQ
selectFrom(AUTHOR.hashJoin(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 HASH JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
Oracle
SELECT /*+leading(AUTHOR BOOK) use_hash(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) HashJoin(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
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！