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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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
- 3.11.1.
- Table columns
- 3.11.1.1.
- Generated table columns
- 3.11.1.2.
- Dereferenced table columns
- 3.11.1.3.
- Named table columns
- 3.11.2.
- Aliased columns
- 3.11.3.
- Cast expressions
- 3.11.4.
- Cast expressions (with TRY_CAST)
- 3.11.5.
- Datatype coercions
- 3.11.6.
- Hidden columns (new)
- 3.11.7.
- Readonly columns
- 3.11.8.
- Computed columns
- 3.11.9.
- Collations
- 3.11.10.
- Arithmetic expressions
- 3.11.11.
- String concatenation
- 3.11.12.
- Case sensitivity with strings
- 3.11.13.
- General functions
- 3.11.13.1.
- CHOOSE
- 3.11.13.2.
- COALESCE
- 3.11.13.3.
- DECODE
- 3.11.13.4.
- IIF
- 3.11.13.5.
- NULLIF
- 3.11.13.6.
- NVL
- 3.11.13.7.
- NVL2
- 3.11.14.
- Numeric functions
- 3.11.14.1.
- ABS
- 3.11.14.2.
- ACOS
- 3.11.14.3.
- ASIN
- 3.11.14.4.
- ATAN
- 3.11.14.5.
- ATAN2
- 3.11.14.6.
- CEIL
- 3.11.14.7.
- COS
- 3.11.14.8.
- COSH
- 3.11.14.9.
- COT
- 3.11.14.10.
- COTH
- 3.11.14.11.
- DEG
- 3.11.14.12.
- E
- 3.11.14.13.
- EXP
- 3.11.14.14.
- FLOOR
- 3.11.14.15.
- GREATEST
- 3.11.14.16.
- LEAST
- 3.11.14.17.
- LN
- 3.11.14.18.
- LOG
- 3.11.14.19.
- LOG10
- 3.11.14.20.
- NEG
- 3.11.14.21.
- PI
- 3.11.14.22.
- POWER
- 3.11.14.23.
- RAD
- 3.11.14.24.
- RAND
- 3.11.14.25.
- ROUND
- 3.11.14.26.
- SIGN
- 3.11.14.27.
- SIN
- 3.11.14.28.
- SINH
- 3.11.14.29.
- SQRT
- 3.11.14.30.
- SQUARE
- 3.11.14.31.
- TAN
- 3.11.14.32.
- TANH
- 3.11.14.33.
- TRUNC
- 3.11.14.34.
- WIDTH_BUCKET
- 3.11.15.
- Bitwise functions
- 3.11.15.1.
- BIT_AND
- 3.11.15.2.
- BIT_COUNT
- 3.11.15.3.
- BIT_GET
- 3.11.15.4.
- BIT_NAND
- 3.11.15.5.
- BIT_NOR
- 3.11.15.6.
- BIT_NOT
- 3.11.15.7.
- BIT_OR
- 3.11.15.8.
- BIT_SET
- 3.11.15.9.
- BIT_XNOR
- 3.11.15.10.
- BIT_XOR
- 3.11.15.11.
- SHL
- 3.11.15.12.
- SHR
- 3.11.16.
- String functions
- 3.11.16.1.
- ASCII
- 3.11.16.2.
- BIN_TO_UUID (new)
- 3.11.16.3.
- BIT_LENGTH
- 3.11.16.4.
- CHR
- 3.11.16.5.
- CONCAT (|| operator)
- 3.11.16.6.
- DIGITS
- 3.11.16.7.
- LEFT
- 3.11.16.8.
- LENGTH
- 3.11.16.9.
- LOWER
- 3.11.16.10.
- LPAD
- 3.11.16.11.
- LTRIM
- 3.11.16.12.
- MD5
- 3.11.16.13.
- MID
- 3.11.16.14.
- OCTET_LENGTH
- 3.11.16.15.
- OVERLAY
- 3.11.16.16.
- POSITION
- 3.11.16.17.
- REGEXP_REPLACE
- 3.11.16.18.
- REPEAT
- 3.11.16.19.
- REPLACE
- 3.11.16.20.
- REVERSE
- 3.11.16.21.
- RIGHT
- 3.11.16.22.
- RPAD
- 3.11.16.23.
- RTRIM
- 3.11.16.24.
- SPACE
- 3.11.16.25.
- SPLIT_PART
- 3.11.16.26.
- SUBSTRING
- 3.11.16.27.
- SUBSTRING_INDEX
- 3.11.16.28.
- TO_CHAR
- 3.11.16.29.
- TO_HEX
- 3.11.16.30.
- TRANSLATE
- 3.11.16.31.
- TRIM
- 3.11.16.32.
- UPPER
- 3.11.16.33.
- UUID
- 3.11.16.34.
- UUID_TO_BIN (new)
- 3.11.17.
- Binary functions (new)
- 3.11.17.1.
- BIT_LENGTH (binary) (new)
- 3.11.17.2.
- CONCAT (binary, || operator) (new)
- 3.11.17.3.
- LENGTH (binary) (new)
- 3.11.17.4.
- LTRIM (binary) (new)
- 3.11.17.5.
- MD5 (binary) (new)
- 3.11.17.6.
- OCTET_LENGTH (binary) (new)
- 3.11.17.7.
- OVERLAY (binary) (new)
- 3.11.17.8.
- POSITION (binary) (new)
- 3.11.17.9.
- RTRIM (binary) (new)
- 3.11.17.10.
- TRIM (binary) (new)
- 3.11.18.
- Datetime functions
- 3.11.18.1.
- CENTURY
- 3.11.18.2.
- CURRENT_DATE
- 3.11.18.3.
- CURRENT_LOCALDATE
- 3.11.18.4.
- CURRENT_LOCALDATETIME
- 3.11.18.5.
- CURRENT_LOCALTIME
- 3.11.18.6.
- CURRENT_OFFSETDATETIME
- 3.11.18.7.
- CURRENT_OFFSETTIME
- 3.11.18.8.
- CURRENT_TIME
- 3.11.18.9.
- CURRENT_TIMESTAMP
- 3.11.18.10.
- DATE
- 3.11.18.11.
- DATEADD
- 3.11.18.12.
- DATEDIFF
- 3.11.18.13.
- DATESUB
- 3.11.18.14.
- DAY
- 3.11.18.15.
- DAY_OF_YEAR
- 3.11.18.16.
- DECADE
- 3.11.18.17.
- EPOCH
- 3.11.18.18.
- EXTRACT
- 3.11.18.19.
- HOUR
- 3.11.18.20.
- ISO_DAY_OF_WEEK
- 3.11.18.21.
- LOCALDATE
- 3.11.18.22.
- LOCALDATEADD
- 3.11.18.23.
- LOCALDATESUB
- 3.11.18.24.
- LOCALDATETIME
- 3.11.18.25.
- LOCALDATETIMEADD
- 3.11.18.26.
- LOCALDATETIMESUB
- 3.11.18.27.
- LOCALTIME
- 3.11.18.28.
- MILLENNIUM
- 3.11.18.29.
- MINUTE
- 3.11.18.30.
- MONTH
- 3.11.18.31.
- QUARTER
- 3.11.18.32.
- SECOND
- 3.11.18.33.
- TIME
- 3.11.18.34.
- TIMESTAMP
- 3.11.18.35.
- TIMESTAMPADD
- 3.11.18.36.
- TIMESTAMPSUB
- 3.11.18.37.
- TO_DATE
- 3.11.18.38.
- TO_LOCALDATE
- 3.11.18.39.
- TO_LOCALDATETIME
- 3.11.18.40.
- TO_TIMESTAMP
- 3.11.18.41.
- TRUNC
- 3.11.18.42.
- YEAR
- 3.11.19.
- ARRAY functions
- 3.11.19.1.
- ARRAY_ALL_MATCH (new)
- 3.11.19.2.
- ARRAY_ANY_MATCH (new)
- 3.11.19.3.
- ARRAY_APPEND (|| operator)
- 3.11.19.4.
- ARRAY_CONCAT (|| operator)
- 3.11.19.5.
- ARRAY_FILTER (new)
- 3.11.19.6.
- ARRAY_GET
- 3.11.19.7.
- ARRAY_MAP (new)
- 3.11.19.8.
- ARRAY_NONE_MATCH (new)
- 3.11.19.9.
- ARRAY_OVERLAP
- 3.11.19.10.
- ARRAY_PREPEND (|| operator)
- 3.11.19.11.
- ARRAY_REMOVE
- 3.11.19.12.
- ARRAY_REPLACE
- 3.11.19.13.
- ARRAY constructor
- 3.11.19.14.
- ARRAY constructor from subquery
- 3.11.19.15.
- CARDINALITY
- 3.11.20.
- JSON functions
- 3.11.20.1.
- JSON_ARRAY
- 3.11.20.2.
- JSON_ARRAY_LENGTH (new)
- 3.11.20.3.
- JSON_INSERT
- 3.11.20.4.
- JSON_KEY_EXISTS (new)
- 3.11.20.5.
- JSON_KEYS
- 3.11.20.6.
- JSON_OBJECT
- 3.11.20.7.
- JSON_REMOVE
- 3.11.20.8.
- JSON_REPLACE
- 3.11.20.9.
- JSON_SET
- 3.11.20.10.
- JSON_VALUE
- 3.11.20.11.
- JSON array element access with -> or ->>
- 3.11.20.12.
- JSON object attribute access with -> or ->>
- 3.11.21.
- XML functions
- 3.11.21.1.
- XMLATTRIBUTES
- 3.11.21.2.
- XMLCOMMENT
- 3.11.21.3.
- XMLCONCAT
- 3.11.21.4.
- XMLDOCUMENT
- 3.11.21.5.
- XMLELEMENT
- 3.11.21.6.
- XMLFOREST
- 3.11.21.7.
- XMLPARSE
- 3.11.21.8.
- XMLPI
- 3.11.21.9.
- XMLQUERY
- 3.11.21.10.
- XMLSERIALIZE
- 3.11.22.
- System functions
- 3.11.22.1.
- CURRENT_SCHEMA
- 3.11.22.2.
- CURRENT_USER
- 3.11.23.
- Spatial functions
- 3.11.23.1.
- ST_Area
- 3.11.23.2.
- ST_AsText
- 3.11.23.3.
- ST_Boundary (new)
- 3.11.23.4.
- ST_Centroid
- 3.11.23.5.
- ST_Difference
- 3.11.23.6.
- ST_Dimension (new)
- 3.11.23.7.
- ST_Distance
- 3.11.23.8.
- ST_EndPoint
- 3.11.23.9.
- ST_ExteriorRing
- 3.11.23.10.
- ST_GeometryN
- 3.11.23.11.
- ST_GeometryType
- 3.11.23.12.
- ST_GeomFromText
- 3.11.23.13.
- ST_InteriorRingN
- 3.11.23.14.
- ST_Intersection
- 3.11.23.15.
- ST_Length
- 3.11.23.16.
- ST_NumGeometries
- 3.11.23.17.
- ST_NumInteriorRings
- 3.11.23.18.
- ST_NumPoints
- 3.11.23.19.
- ST_Perimeter (new)
- 3.11.23.20.
- ST_PointN
- 3.11.23.21.
- ST_SRID
- 3.11.23.22.
- ST_StartPoint
- 3.11.23.23.
- ST_Transform (new)
- 3.11.23.24.
- ST_Union
- 3.11.23.25.
- ST_X
- 3.11.23.26.
- ST_XMax (new)
- 3.11.23.27.
- ST_XMin (new)
- 3.11.23.28.
- ST_Y
- 3.11.23.29.
- ST_YMax (new)
- 3.11.23.30.
- ST_YMin (new)
- 3.11.23.31.
- ST_Z
- 3.11.23.32.
- ST_ZMax (new)
- 3.11.23.33.
- ST_ZMin (new)
- 3.11.24.
- Aggregate functions
- 3.11.24.1.
- Grouping
- 3.11.24.2.
- Distinctness
- 3.11.24.3.
- Filtering
- 3.11.24.4.
- Ordering
- 3.11.24.5.
- Ordering WITHIN GROUP
- 3.11.24.6.
- Keeping
- 3.11.24.7.
- ANY_VALUE
- 3.11.24.8.
- ARRAY_AGG
- 3.11.24.9.
- AVG
- 3.11.24.10.
- LISTAGG (binary) (new)
- 3.11.24.11.
- BIT_AND_AGG
- 3.11.24.12.
- BIT_NAND_AGG
- 3.11.24.13.
- BIT_NOR_AGG
- 3.11.24.14.
- BIT_OR_AGG
- 3.11.24.15.
- BIT_XOR_AGG
- 3.11.24.16.
- BIT_XNOR_AGG
- 3.11.24.17.
- BOOL_AND
- 3.11.24.18.
- BOOL_OR
- 3.11.24.19.
- COLLECT
- 3.11.24.20.
- COUNT
- 3.11.24.21.
- CUME_DIST
- 3.11.24.22.
- DENSE_RANK
- 3.11.24.23.
- EVERY
- 3.11.24.24.
- GROUP_CONCAT
- 3.11.24.25.
- JSON_ARRAYAGG
- 3.11.24.26.
- JSON_OBJECTAGG
- 3.11.24.27.
- LISTAGG
- 3.11.24.28.
- MAX
- 3.11.24.29.
- MAX_BY (new)
- 3.11.24.30.
- MEDIAN
- 3.11.24.31.
- MIN
- 3.11.24.32.
- MIN_BY (new)
- 3.11.24.33.
- MODE
- 3.11.24.34.
- MULTISET_AGG
- 3.11.24.35.
- PERCENT_RANK
- 3.11.24.36.
- PERCENTILE_CONT
- 3.11.24.37.
- PERCENTILE_DISC
- 3.11.24.38.
- PRODUCT
- 3.11.24.39.
- RANK
- 3.11.24.40.
- SUM
- 3.11.24.41.
- XMLAGG
- 3.11.25.
- Window functions
- 3.11.25.1.
- PARTITION BY
- 3.11.25.2.
- ORDER BY
- 3.11.25.3.
- ROWS, RANGE, GROUPS (frame clause)
- 3.11.25.4.
- EXCLUDE
- 3.11.25.5.
- NULL treatment
- 3.11.25.6.
- FROM FIRST, FROM LAST
- 3.11.25.7.
- Nested aggregate functions
- 3.11.25.8.
- Window aggregation
- 3.11.25.9.
- Window ordered aggregate
- 3.11.25.10.
- ROW_NUMBER
- 3.11.25.11.
- RANK
- 3.11.25.12.
- DENSE_RANK
- 3.11.25.13.
- PERCENT_RANK
- 3.11.25.14.
- CUME_DIST
- 3.11.25.15.
- NTILE
- 3.11.25.16.
- LEAD
- 3.11.25.17.
- LAG
- 3.11.25.18.
- FIRST_VALUE
- 3.11.25.19.
- LAST_VALUE
- 3.11.25.20.
- NTH_VALUE
- 3.11.26.
- User-defined functions
- 3.11.27.
- User-defined aggregate functions
- 3.11.28.
- User-defined type attribute paths
- 3.11.29.
- The CASE expression
- 3.11.30.
- Sequences and serials
- 3.11.31.
- Scalar subqueries
- 3.11.32.
- ARRAY value constructor
- 3.11.33.
- MULTISET value constructor
- 3.11.34.
- Tuples or row value expressions
- 3.11.35.
- Nested records
previous : next |
References to this page
- jOOQ as a SQL builder without code generation
- jOOQ and backwards-compatibility
- The query DSL type
- Mutability (historic)
- The WITH RECURSIVE clause
- SelectField
- Convenience methods
- ORDER BY clause
- Ordering using CASE expressions
- CREATE FUNCTION
- Scalar functions
- Variables
- Derived tables
- Table columns
- Generated table columns
- User-defined functions
- The CASE expression
- Scalar subqueries
- Conditional expressions
- Condition building
- Comparison predicate (degree > 1)
- LIKE predicate (binary)
- LIKE predicate
- NULL predicate
- SIMILAR TO predicate
- Dynamic SQL
- Optional column expressions
- Optional conditional expressions
- Plain SQL
- SQL Parser Listener
- Names and identifiers
- Custom data type bindings
- Normalise fields compared to values
- Custom data type conversion
- Stored procedures and functions
- Client side computed columns
- BOOLEAN data type
Feedback
Do you have any feedback about this page? We'd love to hear it!