MERGE JOIN
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The MERGE JOIN algorithm has similar characteristics as the HASH JOIN algorithm in that it reads all data of the tables involved (after applying predicates on them). But it does not require storing that data in memory, as it can "merge" all the tables' data in cases where it is fetched in a pre-sorted order, e.g. due to a covering index on the join key. An example:
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();
Dialect support
This example using jOOQ:
selectFrom(AUTHOR.mergeJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
Translates to the following dialect specific expressions:
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, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!