Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8

SQL building

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

SQL is a declarative language that is hard to integrate into procedural, object-oriented, functional or any other type of programming languages. jOOQ's philosophy is to give SQL the credit it deserves and integrate SQL itself as an "internal domain specific language" directly into Java.

With this philosophy in mind, SQL building is the main feature of jOOQ. All other features (such as SQL execution and code generation) are mere convenience built on top of jOOQ's SQL building capabilities.

This section explains all about the various syntax elements involved with jOOQ's SQL building capabilities. For a complete overview of all syntax elements, please refer to the manual's sections about SQL to DSL mapping rules.

Table of contents

4.1.
The query DSL type
4.1.1.
DSL subclasses
4.2.
The DSLContext API
4.2.1.
SQL Dialect
4.2.2.
SQL Dialect Family
4.2.3.
Connection vs. DataSource
4.2.4.
Custom data
4.2.5.
Custom ExecuteListeners
4.2.6.
Custom Unwrappers
4.2.7.
Custom Settings
4.2.7.1.
Auto-attach Records
4.2.7.2.
Auto-inline bind values (new)
4.2.7.3.
Backslash Escaping
4.2.7.4.
Batch size
4.2.7.5.
Execute Logging
4.2.7.6.
Fetch Warnings
4.2.7.7.
Identifier style
4.2.7.8.
Implicit join type
4.2.7.9.
Inline Threshold
4.2.7.10.
IN-list Padding
4.2.7.11.
Interpreter Configuration
4.2.7.12.
JDBC Flags
4.2.7.13.
Keyword style
4.2.7.14.
Listener Invocation Order
4.2.7.15.
Locales
4.2.7.16.
Map JPA Annotations
4.2.7.17.
Object qualification
4.2.7.18.
Optimistic Locking
4.2.7.19.
Parameter name prefix
4.2.7.20.
Parameter types
4.2.7.21.
Parser Configuration
4.2.7.22.
Reflection caching
4.2.7.23.
Return All Columns On Store
4.2.7.24.
Return Identity Value On Store
4.2.7.25.
Runtime catalog, schema and table mapping
4.2.7.26.
Scalar subqueries for stored functions
4.2.7.27.
Statement Type
4.2.7.28.
Updatable Primary Keys
4.2.8.
Thread safety
4.3.
The DSL API
4.3.1.
Mutability (historic)
4.4.
The model API
4.4.1.
Design
4.4.2.
Traversal
4.4.3.
Replacement
4.4.4.
The historic model API
4.5.
SQL Statements (DML)
4.5.1.
The WITH clause
4.5.2.
The WITH RECURSIVE clause
4.5.3.
The SELECT statement
4.5.3.1.
SELECT clause
4.5.3.1.1.
Projection type safety
4.5.3.1.2.
SelectField
4.5.3.1.3.
SELECT *
4.5.3.1.4.
SELECT * EXCEPT (...)
4.5.3.1.5.
SELECT DISTINCT
4.5.3.1.6.
SELECT DISTINCT ON
4.5.3.1.7.
Convenience methods
4.5.3.2.
FROM clause
4.5.3.3.
JOIN operator
4.5.3.4.
Implicit JOIN
4.5.3.5.
WHERE clause
4.5.3.6.
CONNECT BY clause
4.5.3.7.
GROUP BY clause
4.5.3.8.
HAVING clause
4.5.3.9.
WINDOW clause
4.5.3.10.
QUALIFY clause
4.5.3.11.
ORDER BY clause
4.5.3.12.
LIMIT .. OFFSET clause
4.5.3.13.
WITH TIES clause
4.5.3.14.
SEEK clause
4.5.3.15.
FOR clause
4.5.3.16.
FOR UPDATE clause
4.5.3.17.
UNION, INTERSECTION and EXCEPT
4.5.3.18.
Oracle-style hints
4.5.3.19.
Lexical and logical SELECT clause order
4.5.4.
The INSERT statement
4.5.4.1.
INSERT .. VALUES
4.5.4.2.
INSERT .. DEFAULT VALUES
4.5.4.3.
INSERT .. SET
4.5.4.4.
INSERT .. SELECT
4.5.4.5.
INSERT .. ON DUPLICATE KEY
4.5.4.6.
INSERT .. RETURNING
4.5.5.
The UPDATE statement
4.5.6.
The DELETE statement
4.5.7.
The MERGE statement
4.6.
SQL Statements (DDL)
4.6.1.
The ALTER statement
4.6.1.1.
ALTER DATABASE
4.6.1.2.
ALTER DOMAIN
4.6.1.3.
ALTER INDEX
4.6.1.4.
ALTER SCHEMA
4.6.1.5.
ALTER SEQUENCE
4.6.1.6.
ALTER TABLE
4.6.1.7.
ALTER TYPE
4.6.1.8.
ALTER VIEW
4.6.2.
The COMMENT statement
4.6.3.
The CREATE statement
4.6.3.1.
CREATE DATABASE
4.6.3.2.
CREATE DOMAIN
4.6.3.3.
CREATE FUNCTION
4.6.3.3.1.
Scalar functions
4.6.3.3.2.
CREATE OR REPLACE FUNCTION
4.6.3.3.3.
SQL data access characteristics
4.6.3.3.4.
DETERMINISTIC characteristic
4.6.3.3.5.
ON NULL INPUT characteristic
4.6.3.4.
CREATE INDEX
4.6.3.5.
CREATE PROCEDURE
4.6.3.5.1.
CREATE OR REPLACE PROCEDURE
4.6.3.5.2.
SQL data access characteristics
4.6.3.6.
CREATE SCHEMA
4.6.3.7.
CREATE SEQUENCE
4.6.3.8.
CREATE TABLE
4.6.3.8.1.
Columns
4.6.3.8.2.
Nullability
4.6.3.8.3.
Defaults
4.6.3.8.4.
Identities
4.6.3.8.5.
Computed columns
4.6.3.8.6.
Primary key
4.6.3.8.7.
Unique constraints
4.6.3.8.8.
Foreign keys
4.6.3.8.9.
Check constraints
4.6.3.8.10.
From a SELECT
4.6.3.8.11.
Temporary tables
4.6.3.9.
CREATE TRIGGER
4.6.3.9.1.
Events
4.6.3.9.2.
REFERENCING clause
4.6.3.9.3.
STATEMENT vs ROW triggers
4.6.3.9.4.
WHEN clause
4.6.3.10.
CREATE TYPE
4.6.3.11.
CREATE VIEW
4.6.4.
The DROP statement
4.6.4.1.
DROP DATABASE
4.6.4.2.
DROP DOMAIN
4.6.4.3.
DROP FUNCTION
4.6.4.4.
DROP INDEX
4.6.4.5.
DROP PROCEDURE
4.6.4.6.
DROP SCHEMA
4.6.4.7.
DROP SEQUENCE
4.6.4.8.
DROP TABLE
4.6.4.9.
DROP TRIGGER
4.6.4.10.
DROP TYPE
4.6.4.11.
DROP VIEW
4.6.5.
The GRANT statement
4.6.6.
The REVOKE statement
4.6.7.
The SET statement
4.6.8.
The TRUNCATE statement
4.6.9.
Generating DDL from objects
4.7.
Procedural statements
4.7.1.
Block statement
4.7.2.
CALL statement
4.7.3.
CONTINUE statement
4.7.4.
EXECUTE statement
4.7.5.
EXIT statement
4.7.6.
FOR statement
4.7.7.
GOTO statement
4.7.8.
IF statement
4.7.9.
Labels
4.7.10.
LOOP statement
4.7.11.
REPEAT statement
4.7.12.
SIGNAL
4.7.13.
Variables
4.7.14.
WHILE statement
4.8.
Catalog and schema expressions
4.9.
Table expressions
4.9.1.
Generated Tables
4.9.2.
Aliased Tables
4.9.3.
Joined tables
4.9.3.1.
CROSS JOIN
4.9.3.2.
INNER JOIN
4.9.3.3.
OUTER JOIN
4.9.3.4.
SEMI JOIN
4.9.3.5.
ANTI JOIN
4.9.3.6.
ON clause
4.9.3.7.
ON KEY clause
4.9.3.8.
USING clause
4.9.3.9.
NATURAL clause
4.9.3.10.
APPLY or LATERAL
4.9.3.11.
PARTITION BY
4.9.4.
The VALUES() table constructor
4.9.5.
Derived tables
4.9.6.
The Oracle 11g PIVOT clause
4.9.7.
jOOQ's relational division syntax
4.9.8.
Array and cursor unnesting
4.9.9.
Table-valued functions
4.9.10.
JSON_TABLE
4.9.11.
XMLTABLE
4.9.12.
The DUAL table
4.9.13.
Temporal tables
4.9.14.
Data change delta tables
4.9.15.
Tables as SelectField (new)
4.9.16.
Tables as GroupField
4.10.
Column expressions
4.10.1.
Table columns
4.10.2.
Aliased columns
4.10.3.
Cast expressions
4.10.4.
Datatype coercions
4.10.5.
Readonly columns
4.10.6.
Computed columns
4.10.7.
Collations
4.10.8.
Arithmetic expressions
4.10.9.
String concatenation
4.10.10.
Case sensitivity with strings
4.10.11.
General functions
4.10.11.1.
COALESCE
4.10.11.2.
DECODE
4.10.11.3.
IIF
4.10.11.4.
NULLIF
4.10.11.5.
NVL
4.10.11.6.
NVL2
4.10.12.
Numeric functions
4.10.12.1.
ABS
4.10.12.2.
ACOS
4.10.12.3.
ASIN
4.10.12.4.
ATAN
4.10.12.5.
ATAN2
4.10.12.6.
CEIL
4.10.12.7.
COS
4.10.12.8.
COSH
4.10.12.9.
COT
4.10.12.10.
COTH
4.10.12.11.
DEG
4.10.12.12.
E
4.10.12.13.
EXP
4.10.12.14.
FLOOR
4.10.12.15.
GREATEST
4.10.12.16.
LEAST
4.10.12.17.
LN
4.10.12.18.
LOG
4.10.12.19.
LOG10
4.10.12.20.
NEG
4.10.12.21.
PI
4.10.12.22.
POWER
4.10.12.23.
RAD
4.10.12.24.
RAND
4.10.12.25.
ROUND
4.10.12.26.
SIGN
4.10.12.27.
SIN
4.10.12.28.
SINH
4.10.12.29.
SQRT
4.10.12.30.
SQUARE
4.10.12.31.
TAN
4.10.12.32.
TANH
4.10.12.33.
TRUNC
4.10.12.34.
WIDTH_BUCKET
4.10.13.
Bitwise functions
4.10.13.1.
BIT_AND
4.10.13.2.
BIT_COUNT
4.10.13.3.
BIT_NAND
4.10.13.4.
BIT_NOR
4.10.13.5.
BIT_NOT
4.10.13.6.
BIT_OR
4.10.13.7.
BIT_XNOR
4.10.13.8.
BIT_XOR
4.10.13.9.
SHL
4.10.13.10.
SHR
4.10.14.
String functions
4.10.14.1.
ASCII
4.10.14.2.
CHR
4.10.14.3.
CONCAT
4.10.14.4.
DIGITS
4.10.14.5.
LEFT
4.10.14.6.
LENGTH
4.10.14.7.
LOWER
4.10.14.8.
LPAD
4.10.14.9.
LTRIM
4.10.14.10.
MD5
4.10.14.11.
MID
4.10.14.12.
OVERLAY
4.10.14.13.
POSITION
4.10.14.14.
REGEXP_REPLACE
4.10.14.15.
REPEAT
4.10.14.16.
REPLACE
4.10.14.17.
REVERSE
4.10.14.18.
RIGHT
4.10.14.19.
RPAD
4.10.14.20.
RTRIM
4.10.14.21.
SPACE
4.10.14.22.
SPLIT_PART
4.10.14.23.
SUBSTRING
4.10.14.24.
SUBSTRING_INDEX
4.10.14.25.
TO_CHAR
4.10.14.26.
TO_HEX
4.10.14.27.
TRANSLATE
4.10.14.28.
TRIM
4.10.14.29.
UPPER
4.10.14.30.
UUID
4.10.15.
Datetime functions
4.10.15.1.
CENTURY
4.10.15.2.
CURRENT_DATE
4.10.15.3.
CURRENT_LOCALDATE
4.10.15.4.
CURRENT_LOCALDATETIME
4.10.15.5.
CURRENT_LOCALTIME
4.10.15.6.
CURRENT_OFFSETDATETIME
4.10.15.7.
CURRENT_OFFSETTIME
4.10.15.8.
CURRENT_TIME
4.10.15.9.
CURRENT_TIMESTAMP
4.10.15.10.
DATE
4.10.15.11.
DATEADD
4.10.15.12.
DATEDIFF
4.10.15.13.
DATESUB
4.10.15.14.
DAY
4.10.15.15.
DAY_OF_YEAR
4.10.15.16.
DECADE
4.10.15.17.
EPOCH
4.10.15.18.
EXTRACT
4.10.15.19.
HOUR
4.10.15.20.
ISO_DAY_OF_WEEK
4.10.15.21.
LOCALDATE
4.10.15.22.
LOCALDATEADD
4.10.15.23.
LOCALDATESUB
4.10.15.24.
LOCALDATETIME
4.10.15.25.
LOCALDATETIMEADD
4.10.15.26.
LOCALDATETIMESUB
4.10.15.27.
LOCALTIME
4.10.15.28.
MILLENNIUM
4.10.15.29.
MINUTE
4.10.15.30.
MONTH
4.10.15.31.
QUARTER
4.10.15.32.
SECOND
4.10.15.33.
TIME
4.10.15.34.
TIMESTAMP
4.10.15.35.
TIMESTAMPADD
4.10.15.36.
TIMESTAMPSUB
4.10.15.37.
TO_DATE
4.10.15.38.
TO_LOCALDATE
4.10.15.39.
TO_LOCALDATETIME
4.10.15.40.
TO_TIMESTAMP
4.10.15.41.
TRUNC
4.10.15.42.
YEAR
4.10.16.
ARRAY functions
4.10.16.1.
ARRAY_GET
4.10.16.2.
ARRAY constructor
4.10.16.3.
ARRAY constructor from subquery
4.10.16.4.
CARDINALITY
4.10.17.
JSON functions
4.10.17.1.
JSON_ARRAY
4.10.17.2.
JSON_OBJECT
4.10.17.3.
JSON_VALUE
4.10.18.
XML functions
4.10.18.1.
XMLATTRIBUTES
4.10.18.2.
XMLCOMMENT
4.10.18.3.
XMLCONCAT
4.10.18.4.
XMLDOCUMENT
4.10.18.5.
XMLELEMENT
4.10.18.6.
XMLFOREST
4.10.18.7.
XMLPARSE
4.10.18.8.
XMLPI
4.10.18.9.
XMLQUERY
4.10.18.10.
XMLSERIALIZE
4.10.19.
System functions
4.10.19.1.
CURRENT_SCHEMA
4.10.19.2.
CURRENT_USER
4.10.20.
Spatial functions
4.10.20.1.
ST_Area
4.10.20.2.
ST_AsText
4.10.20.3.
ST_Centroid
4.10.20.4.
ST_Difference
4.10.20.5.
ST_Distance
4.10.20.6.
ST_EndPoint
4.10.20.7.
ST_ExteriorRing
4.10.20.8.
ST_GeometryN
4.10.20.9.
ST_GeometryType
4.10.20.10.
ST_GeomFromText
4.10.20.11.
ST_InteriorRingN
4.10.20.12.
ST_Intersection
4.10.20.13.
ST_Length
4.10.20.14.
ST_NumGeometries
4.10.20.15.
ST_NumInteriorRings
4.10.20.16.
ST_NumPoints
4.10.20.17.
ST_PointN
4.10.20.18.
ST_SRID
4.10.20.19.
ST_StartPoint
4.10.20.20.
ST_Union
4.10.20.21.
ST_X
4.10.20.22.
ST_Y
4.10.20.23.
ST_Z
4.10.21.
Aggregate functions
4.10.21.1.
Grouping
4.10.21.2.
Distinctness
4.10.21.3.
Filtering
4.10.21.4.
Ordering
4.10.21.5.
Ordering WITHIN GROUP
4.10.21.6.
Keeping
4.10.21.7.
ANY_VALUE
4.10.21.8.
ARRAY_AGG
4.10.21.9.
AVG
4.10.21.10.
BIT_AND_AGG
4.10.21.11.
BIT_NAND_AGG (new)
4.10.21.12.
BIT_NOR_AGG (new)
4.10.21.13.
BIT_OR_AGG
4.10.21.14.
BIT_XOR_AGG
4.10.21.15.
BIT_XNOR_AGG (new)
4.10.21.16.
BOOL_AND
4.10.21.17.
BOOL_OR
4.10.21.18.
COLLECT
4.10.21.19.
COUNT
4.10.21.20.
CUME_DIST
4.10.21.21.
DENSE_RANK
4.10.21.22.
EVERY
4.10.21.23.
GROUP_CONCAT
4.10.21.24.
JSON_ARRAYAGG
4.10.21.25.
JSON_OBJECTAGG
4.10.21.26.
LISTAGG
4.10.21.27.
MAX
4.10.21.28.
MEDIAN
4.10.21.29.
MIN
4.10.21.30.
MODE
4.10.21.31.
MULTISET_AGG
4.10.21.32.
PERCENT_RANK
4.10.21.33.
PERCENTILE_CONT
4.10.21.34.
PERCENTILE_DISC
4.10.21.35.
PRODUCT
4.10.21.36.
RANK
4.10.21.37.
SUM
4.10.21.38.
XMLAGG
4.10.22.
Window functions
4.10.23.
Grouping functions
4.10.24.
User-defined functions
4.10.25.
User-defined aggregate functions
4.10.26.
The CASE expression
4.10.27.
Sequences and serials
4.10.28.
Scalar subqueries
4.10.29.
ARRAY value constructor
4.10.30.
MULTISET value constructor
4.10.31.
Tuples or row value expressions
4.10.32.
Nested records
4.11.
Conditional expressions
4.11.1.
Condition building
4.11.2.
TRUE and FALSE condition
4.11.3.
BOOLEAN columns
4.11.4.
AND, OR, NOT boolean operators
4.11.5.
Comparison predicate
4.11.6.
Boolean operator precedence
4.11.7.
Comparison predicate (degree > 1)
4.11.8.
Quantified comparison predicate
4.11.9.
BETWEEN predicate
4.11.10.
BETWEEN predicate (degree > 1)
4.11.11.
DISTINCT predicate
4.11.12.
DOCUMENT predicate
4.11.13.
EXISTS predicate
4.11.14.
IN predicate
4.11.15.
IN predicate (degree > 1)
4.11.16.
JSON predicate
4.11.17.
JSON_EXISTS predicate
4.11.18.
LIKE predicate
4.11.19.
NULL predicate
4.11.20.
NULL predicate (degree > 1)
4.11.21.
OVERLAPS predicate
4.11.22.
SIMILAR TO predicate
4.11.23.
Spatial predicates
4.11.23.1.
ST_Contains
4.11.23.2.
ST_Crosses
4.11.23.3.
ST_Disjoint
4.11.23.4.
ST_Equals
4.11.23.5.
ST_Intersects
4.11.23.6.
ST_IsClosed
4.11.23.7.
ST_IsEmpty
4.11.23.8.
ST_Overlaps
4.11.23.9.
ST_Touches
4.11.23.10.
ST_Within
4.11.24.
UNIQUE predicate
4.11.25.
XMLEXISTS predicate
4.11.26.
Query By Example (QBE)
4.12.
Synthetic SQL clauses
4.13.
Dynamic SQL
4.13.1.
Optional column expressions (new)
4.13.2.
Optional conditional expressions
4.14.
Plain SQL
4.15.
Plain SQL Templating Language
4.16.
SQL Parser
4.16.1.
SQL Parser API
4.16.2.
SQL Parser CLI
4.16.3.
SQL Parser Listener
4.16.4.
SQL Parser Grammar
4.17.
SQL interpreter
4.18.
Schema diff
4.19.
Schema diff CLI
4.20.
Names and identifiers
4.21.
Bind values and parameters
4.21.1.
Indexed parameters
4.21.2.
Named parameters
4.21.3.
Inlined parameters
4.21.4.
SQL injection
4.22.
QueryParts
4.22.1.
SQL rendering
4.22.2.
Declaration vs reference
4.22.3.
Pretty printing SQL
4.22.4.
Variable binding
4.22.5.
Custom data type bindings
4.22.6.
Custom syntax elements
4.22.7.
Plain SQL QueryParts
4.22.8.
Serializability
4.22.9.
SQL transformation
4.22.9.1.
ANSI JOIN to table lists
4.22.9.2.
Table lists to ANSI JOIN
4.22.9.3.
ROWNUM to LIMIT
4.22.9.4.
Unnecessary arithmetic expressions
4.22.9.5.
Pattern based transformation (new)
4.22.9.5.1.
AND to NOT IN (new)
4.22.9.5.2.
Arithmetic expressions (new)
4.22.9.5.3.
COUNT(*) scalar subquery comparison (new)
4.22.9.5.4.
Empty scalar subquery (new)
4.22.9.5.5.
Hyperbolic functions (new)
4.22.9.5.6.
Idempotent function repetition (new)
4.22.9.5.7.
Inverse hyperbolic functions (new)
4.22.9.5.8.
Logarithmic functions (new)
4.22.9.5.9.
Merge AND predicates (new)
4.22.9.5.10.
Merge BIT_NOT with BIT_NAND (new)
4.22.9.5.11.
Merge BIT_NOT with BIT_NOR (new)
4.22.9.5.12.
Merge BIT_NOT with BIT_XNOR (new)
4.22.9.5.13.
Merge IN predicates (new)
4.22.9.5.14.
Merge NOT with comparison predicates (new)
4.22.9.5.15.
Merge NOT with DISTINCT predicate (new)
4.22.9.5.16.
Merge OR predicates (new)
4.22.9.5.17.
Merge range predicates (new)
4.22.9.5.18.
Normalise associative operations (new)
4.22.9.5.19.
Normalise fields compared to values (new)
4.22.9.5.20.
Normalise IN list with single element to comparison (new)
4.22.9.5.21.
OR to IN (new)
4.22.9.5.22.
Repeated negation (new)
4.22.9.5.23.
Repeated negation (new)
4.22.9.5.24.
Repeated NOT (new)
4.22.9.5.25.
Trigonometric functions (new)
4.22.9.5.26.
Trim (new)
4.22.9.5.27.
Trivial case abbreviations (new)
4.22.9.5.28.
Trivial predicates (new)
4.22.10.
Custom SQL transformation
4.22.10.1.
Logging abbreviated bind values
4.23.
Zero-based vs one-based APIs
4.24.
SQL building in Kotlin
4.24.1.
Kotlin MULTISET Collectors (new)
4.24.2.
Kotlin BOOLEAN value expressions
4.24.3.
Kotlin ARRAY access
4.25.
SQL building in Scala

The jOOQ User Manual : SQL buildingprevious : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo