Available in versions: Dev (3.20) | Latest (3.19) | 3.18

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, 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, DuckDB, Firebird, MariaDB, MemSQL, MySQL, Oracle, SQLDataWarehouse, SQLServer, SQLite, Snowflake

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.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!

The jOOQ Logo