Tables as SelectField
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An org.jooq.Table expression extends the org.jooq.SelectField type, and as such, can be used in the SELECT clause directly, as well as everywhere else a SelectField is accepted, e.g. in nested records. This is specifically useful for (generated) table references. The following shows how to project a nested org.jooq.TableRecord:
Result<Record2<AuthorRecord, BookRecord>> result =
create.select(AUTHOR, BOOK)
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.fetch();
This plays very well together with implicit joins:
Result<Record2<AuthorRecord, BookRecord>> result =
create.select(BOOK.author(), BOOK)
.from(BOOK)
.fetch();
Behind the scenes, the implementation may either be native in dialects that support this kind of projection (e.g. PostgreSQL), or emulated using the usual nested records emulations.
Nesting tables using this syntax is different from using the TABLE.asterisk() syntax, which doesn't nest the table but flattens its fields into the surrounding projection.
Dialect support
This example using jOOQ:
select(BOOK).from(BOOK)
Translates to the following dialect specific expressions:
ASE, Access, SQLDataWarehouse, SQLServer, Sybase
SELECT BOOK.ID [BOOK__ID], BOOK.AUTHOR_ID [BOOK__AUTHOR_ID], BOOK.TITLE [BOOK__TITLE], BOOK.PUBLISHED_IN [BOOK__PUBLISHED_IN], BOOK.LANGUAGE_ID [BOOK__LANGUAGE_ID] FROM BOOK
Aurora MySQL, BigQuery, MariaDB, MemSQL, MySQL, SQLite
SELECT BOOK.ID `BOOK__ID`, BOOK.AUTHOR_ID `BOOK__AUTHOR_ID`, BOOK.TITLE `BOOK__TITLE`, BOOK.PUBLISHED_IN `BOOK__PUBLISHED_IN`, BOOK.LANGUAGE_ID `BOOK__LANGUAGE_ID` FROM BOOK
Aurora Postgres, CockroachDB, DuckDB, Informix, Postgres, YugabyteDB
SELECT ROW ( BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE, BOOK.PUBLISHED_IN, BOOK.LANGUAGE_ID ) BOOK FROM BOOK
ClickHouse
SELECT TUPLE ( BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE, BOOK.PUBLISHED_IN, BOOK.LANGUAGE_ID ) BOOK FROM BOOK
Databricks
SELECT ( coalesce(BOOK.ID), coalesce(BOOK.AUTHOR_ID), coalesce(BOOK.TITLE), coalesce(BOOK.PUBLISHED_IN), coalesce(BOOK.LANGUAGE_ID) ) BOOK FROM BOOK
DB2, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Oracle, Redshift, Snowflake, Teradata, Trino, Vertica
SELECT BOOK.ID "BOOK__ID", BOOK.AUTHOR_ID "BOOK__AUTHOR_ID", BOOK.TITLE "BOOK__TITLE", BOOK.PUBLISHED_IN "BOOK__PUBLISHED_IN", BOOK.LANGUAGE_ID "BOOK__LANGUAGE_ID" FROM BOOK
Generated with jOOQ 3.21. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!