Aliased joined tables
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some SQL dialects follow the SQL standard that allows for aliasing also joined tables, i.e. the product of a JOIN operator, not just the individual JOIN operands. If that isn't available, jOOQ will try to emulate the feature assuming there can't be any column ambiguities.
Table<?> t = AUTHOR.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)).as("t");
Dialect support
This example using jOOQ:
selectFrom(AUTHOR.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)).as("t"))
Translates to the following dialect specific expressions:
-- ACCESS
SELECT
t.ID,
t.FIRST_NAME,
t.LAST_NAME,
t.DATE_OF_BIRTH,
t.YEAR_OF_BIRTH,
t.DISTINGUISHED,
t.ID,
t.AUTHOR_ID,
t.TITLE,
t.PUBLISHED_IN,
t.LANGUAGE_ID
FROM (
AUTHOR
INNER JOIN BOOK
ON AUTHOR.ID = BOOK.AUTHOR_ID
) t
-- ASE, AURORA_POSTGRES, COCKROACHDB, DUCKDB, EXASOL, H2, HANA, HSQLDB, INFORMIX, POSTGRES, REDSHIFT, SYBASE, TERADATA,
-- TRINO, VERTICA, YUGABYTEDB
SELECT
t.ID,
t.FIRST_NAME,
t.LAST_NAME,
t.DATE_OF_BIRTH,
t.YEAR_OF_BIRTH,
t.DISTINGUISHED,
t.ID,
t.AUTHOR_ID,
t.TITLE,
t.PUBLISHED_IN,
t.LANGUAGE_ID
FROM (
AUTHOR
JOIN BOOK
ON AUTHOR.ID = BOOK.AUTHOR_ID
) t
-- AURORA_MYSQL, BIGQUERY, DB2, DERBY, FIREBIRD, MARIADB, MEMSQL, MYSQL, ORACLE, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE,
-- SQLSERVER
SELECT
t.ID,
t.FIRST_NAME,
t.LAST_NAME,
t.DATE_OF_BIRTH,
t.YEAR_OF_BIRTH,
t.DISTINGUISHED,
t.ID,
t.AUTHOR_ID,
t.TITLE,
t.PUBLISHED_IN,
t.LANGUAGE_ID
FROM (
SELECT *
FROM AUTHOR
JOIN BOOK
ON AUTHOR.ID = BOOK.AUTHOR_ID
) t
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!