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, ClickHouse, CockroachDB, Exasol, H2, HSQLDB, Hana, Informix, Postgres, Redshift, 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, DuckDB, Firebird, MariaDB, MemSQL, MySQL, Oracle, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase
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
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!