Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Aliased columns
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Just like tables, columns can be renamed using aliases. Here is an example:
SELECT FIRST_NAME || ' ' || LAST_NAME author, COUNT(*) books
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = AUTHOR_ID
GROUP BY FIRST_NAME, LAST_NAME;
Here is how it's done with jOOQ:
Record record = create.select(
concat(AUTHOR.FIRST_NAME, inline(" "), AUTHOR.LAST_NAME).as("author"),
count().as("books"))
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.fetchAny();
When you alias Fields like above, you can access those Fields' values using the alias name:
System.out.println("Author : " + record.getValue("author"));
System.out.println("Books : " + record.getValue("books"));
Unnamed column expressions
In most SQL databases, aliasing of column expressions in top level selects is optional. The database will generate a column name that is roughly based on the expression for documentation purposes (e.g. when running the query in a tool like SQL Developer), but applications cannot rely on the name explicitly. This is not a problem as columns can still be referenced by index.
In a similar fashion, jOOQ will assume an unspecified, generated column name for column expressions, based on their content.
-- Arithmetic expression 1 + 2 -- Correlated subquery (SELECT 1 AS a)
// Arithmetic expression
inline(1).plus(inline(2));
// Correlated subquery
field(select(inline(1).as("a")));
These unnamed expressions can be used both in SQL as well as with jOOQ. However, do note that jOOQ will use Field.getName() to extract this column name from the field, when referencing the field or when nesting it in derived tables. In order to stay in full control of any such column names, it is always a good idea to provide explicit aliasing for column expressions, both in SQL as well as in jOOQ.
Rendering declarations or references
The same aliased column instance is rendered differently depending on where it is placed in the jOOQ expression tree. See the manual's section about rendering declarations vs references for more details.
Feedback
Do you have any feedback about this page? We'd love to hear it!