New versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3
Column expressions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Column expressions can be used in various SQL clauses in order to refer to one or several columns. This chapter explains how to form various types of column expressions with jOOQ. A particular type of column expression is given in the section about tuples or row value expressions, where an expression may have a degree of more than one.
Using column expressions in jOOQ
jOOQ allows you to freely create arbitrary column expressions using a fluent expression construction API. Many expressions can be formed as functions from DSL methods, other expressions can be formed based on a pre-existing column expression. For example:
// A regular table column expression
Field<String> field1 = BOOK.TITLE;
// A function created from the DSL
Field<String> field2 = trim(BOOK.TITLE);
// More complex function with advanced DSL syntax
Field<String> field4 = listAgg(BOOK.TITLE)
.withinGroupOrderBy(BOOK.ID.asc())
.over().partitionBy(AUTHOR.ID);
Table of contents
- 4.8.1.
- Table columns
- 4.8.2.
- Aliased columns
- 4.8.3.
- Cast expressions
- 4.8.4.
- Datatype coercions
- 4.8.5.
- Collations
- 4.8.6.
- Arithmetic expressions
- 4.8.7.
- String concatenation
- 4.8.8.
- Case sensitivity with strings
- 4.8.9.
- General functions
- 4.8.9.1.
- COALESCE
- 4.8.9.2.
- DECODE
- 4.8.9.3.
- IIF
- 4.8.9.4.
- NULLIF
- 4.8.9.5.
- NVL
- 4.8.9.6.
- NVL2
- 4.8.10.
- Numeric functions
- 4.8.10.1.
- ABS
- 4.8.10.2.
- ACOS
- 4.8.10.3.
- ASIN
- 4.8.10.4.
- ATAN
- 4.8.10.5.
- ATAN2
- 4.8.10.6.
- CEIL
- 4.8.10.7.
- COS
- 4.8.10.8.
- COSH
- 4.8.10.9.
- COT
- 4.8.10.10.
- COTH
- 4.8.10.11.
- DEG
- 4.8.10.12.
- E
- 4.8.10.13.
- EXP
- 4.8.10.14.
- FLOOR
- 4.8.10.15.
- GREATEST
- 4.8.10.16.
- LEAST
- 4.8.10.17.
- LN
- 4.8.10.18.
- LOG
- 4.8.10.19.
- NEG
- 4.8.10.20.
- PI
- 4.8.10.21.
- POWER
- 4.8.10.22.
- RAD
- 4.8.10.23.
- RAND
- 4.8.10.24.
- ROUND
- 4.8.10.25.
- SIGN
- 4.8.10.26.
- SIN
- 4.8.10.27.
- SINH
- 4.8.10.28.
- SQRT
- 4.8.10.29.
- TAN
- 4.8.10.30.
- TANH
- 4.8.10.31.
- TRUNC
- 4.8.10.32.
- WIDTH_BUCKET
- 4.8.11.
- Bitwise functions
- 4.8.11.1.
- BIT_COUNT
- 4.8.11.2.
- BIT_AND
- 4.8.11.3.
- BIT_NAND
- 4.8.11.4.
- BIT_NOR
- 4.8.11.5.
- BIT_NOT
- 4.8.11.6.
- BIT_OR
- 4.8.11.7.
- SHL
- 4.8.11.8.
- SHR
- 4.8.11.9.
- BIT_XNOR
- 4.8.11.10.
- BIT_XOR
- 4.8.12.
- String functions
- 4.8.12.1.
- ASCII
- 4.8.12.2.
- CONCAT
- 4.8.12.3.
- LEFT
- 4.8.12.4.
- LENGTH
- 4.8.12.5.
- LOWER
- 4.8.12.6.
- LPAD
- 4.8.12.7.
- LTRIM
- 4.8.12.8.
- MD5
- 4.8.12.9.
- MID
- 4.8.12.10.
- POSITION
- 4.8.12.11.
- REPEAT
- 4.8.12.12.
- REPLACE
- 4.8.12.13.
- REVERSE
- 4.8.12.14.
- RIGHT
- 4.8.12.15.
- RPAD
- 4.8.12.16.
- RTRIM
- 4.8.12.17.
- SPACE
- 4.8.12.18.
- SUBSTRING
- 4.8.12.19.
- TRANSLATE
- 4.8.12.20.
- TRIM
- 4.8.12.21.
- UPPER
- 4.8.13.
- Datetime functions
- 4.8.13.1.
- CENTURY
- 4.8.13.2.
- CURRENT_DATE
- 4.8.13.3.
- CURRENT_LOCALDATE
- 4.8.13.4.
- CURRENT_LOCALDATETIME
- 4.8.13.5.
- CURRENT_LOCALTIME
- 4.8.13.6.
- CURRENT_OFFSETDATETIME
- 4.8.13.7.
- CURRENT_OFFSETTIME
- 4.8.13.8.
- CURRENT_TIME
- 4.8.13.9.
- CURRENT_TIMESTAMP
- 4.8.13.10.
- DATE
- 4.8.13.11.
- DATEADD
- 4.8.13.12.
- DATEDIFF
- 4.8.13.13.
- DATESUB
- 4.8.13.14.
- DAY
- 4.8.13.15.
- DAY_OF_YEAR
- 4.8.13.16.
- DECADE
- 4.8.13.17.
- EPOCH
- 4.8.13.18.
- EXTRACT
- 4.8.13.19.
- HOUR
- 4.8.13.20.
- ISO_DAY_OF_WEEK
- 4.8.13.21.
- LOCALDATE
- 4.8.13.22.
- LOCALDATEADD
- 4.8.13.23.
- LOCALDATESUB
- 4.8.13.24.
- LOCALDATETIME
- 4.8.13.25.
- LOCALDATETIMEADD
- 4.8.13.26.
- LOCALDATETIMESUB
- 4.8.13.27.
- LOCALTIME
- 4.8.13.28.
- MILLENNIUM
- 4.8.13.29.
- MINUTE
- 4.8.13.30.
- MONTH
- 4.8.13.31.
- QUARTER
- 4.8.13.32.
- SECOND
- 4.8.13.33.
- TIME
- 4.8.13.34.
- TIMESTAMP
- 4.8.13.35.
- TIMESTAMPADD
- 4.8.13.36.
- TIMESTAMPSUB
- 4.8.13.37.
- TO_DATE
- 4.8.13.38.
- TO_LOCALDATE
- 4.8.13.39.
- TO_LOCALDATETIME
- 4.8.13.40.
- TO_TIMESTAMP
- 4.8.13.41.
- TRUNC
- 4.8.13.42.
- YEAR
- 4.8.14.
- ARRAY functions
- 4.8.14.1.
- ARRAY constructor
- 4.8.15.
- JSON functions
- 4.8.15.1.
- JSON_ARRAY
- 4.8.15.2.
- JSON_OBJECT
- 4.8.16.
- System functions
- 4.8.16.1.
- CURRENT_SCHEMA
- 4.8.16.2.
- CURRENT_USER
- 4.8.17.
- Aggregate functions
- 4.8.17.1.
- Grouping
- 4.8.17.2.
- Distinctness
- 4.8.17.3.
- Filtering
- 4.8.17.4.
- Ordering
- 4.8.17.5.
- Ordering WITHIN GROUP
- 4.8.17.6.
- Keeping
- 4.8.17.7.
- ARRAY_AGG
- 4.8.17.8.
- AVG
- 4.8.17.9.
- BOOL_AND
- 4.8.17.10.
- BOOL_OR
- 4.8.17.11.
- COLLECT
- 4.8.17.12.
- COUNT
- 4.8.17.13.
- CUME_DIST
- 4.8.17.14.
- DENSE_RANK
- 4.8.17.15.
- EVERY
- 4.8.17.16.
- GROUP_CONCAT
- 4.8.17.17.
- LISTAGG
- 4.8.17.18.
- MAX
- 4.8.17.19.
- MEDIAN
- 4.8.17.20.
- MIN
- 4.8.17.21.
- MODE
- 4.8.17.22.
- PERCENT_RANK
- 4.8.17.23.
- PERCENTILE_CONT
- 4.8.17.24.
- PERCENTILE_DISC
- 4.8.17.25.
- PRODUCT
- 4.8.17.26.
- RANK
- 4.8.17.27.
- SUM
- 4.8.18.
- Window functions
- 4.8.19.
- Grouping functions
- 4.8.20.
- User-defined functions
- 4.8.21.
- User-defined aggregate functions
- 4.8.22.
- The CASE expression
- 4.8.23.
- Sequences and serials
- 4.8.24.
- Scalar subqueries
- 4.8.25.
- Tuples or row value expressions
| The jOOQ User Manual : SQL building : Column expressions | previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!