LOOP JOIN
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
LOOP JOIN（或嵌套循环连接）算法运行一个嵌套循环，以获取与第一个表的行对应的第二个表的所有行。这通常比线性算法具有更高的算法复杂度，这就是为什么对于较大的数据集来说，它可能是一个较慢的选择，但对于小型数据集来说，它是一个不错的选择 - 特别是当嵌套循环查找有索引可用时。一个例子
SELECT * FROM AUTHOR INNER LOOP JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select()
.from(AUTHOR)
.loopJoin(BOOK)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.fetch();
方言支持
此示例使用 jOOQ
selectFrom(AUTHOR.loopJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
翻译成以下特定方言的表达式
CockroachDB
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 LOOKUP JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
Oracle
SELECT /*+leading(AUTHOR BOOK) use_nl(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
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 LOOP JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
YugabyteDB
SELECT /*+leading(AUTHOR BOOK) NestLoop(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
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！