SQL Statements (DML)

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

jOOQ currently supports 5 types of SQL statements. All of these statements are constructed from a DSLContext instance with an optional JDBC Connection or DataSource. If supplied with a Connection or DataSource, they can be executed. Depending on the query type, executed queries can return results.

Table of contents

3.5.1.

The WITH clause

3.5.2.

The WITH RECURSIVE clause

3.5.3.

The SELECT statement

3.5.3.1.

SELECT clause

3.5.3.1.1.

Projection type safety

3.5.3.1.2.

SelectField

3.5.3.1.3.

Tables as SelectField

3.5.3.1.4.

SELECT *

3.5.3.1.5.

SELECT * EXCEPT (...)

3.5.3.1.6.

SELECT DISTINCT

3.5.3.1.7.

SELECT DISTINCT ON

3.5.3.1.8.

Convenience methods

3.5.3.2.

FROM clause

3.5.3.2.1.

JOIN operator

3.5.3.2.2.

Implicit path JOIN

3.5.3.2.3.

Implicit to-many path JOIN

3.5.3.2.4.

Explicit path JOIN

3.5.3.2.5.

Implicit path correlation

3.5.3.3.

WHERE clause

3.5.3.4.

CONNECT BY clause

3.5.3.5.

GROUP BY clause

3.5.3.5.1.

GROUP BY columns

3.5.3.5.2.

GROUP BY column index

3.5.3.5.3.

GROUP BY tables

3.5.3.5.4.

GROUP BY ROLLUP

3.5.3.5.5.

GROUP BY CUBE

3.5.3.5.6.

GROUP BY GROUPING SETS

3.5.3.5.7.

GROUP BY empty grouping set

3.5.3.6.

HAVING clause

3.5.3.7.

WINDOW clause

3.5.3.8.

QUALIFY clause

3.5.3.9.

ORDER BY clause

3.5.3.9.1.

Ordering by field index

3.5.3.9.2.

Ordering and NULLS

3.5.3.9.3.

Ordering using CASE expressions

3.5.3.9.4.

Oracle's ORDER SIBLINGS BY clause

3.5.3.10.

LIMIT .. OFFSET clause

3.5.3.11.

WITH TIES clause

3.5.3.12.

SEEK clause

3.5.3.13.

FOR XML clause

3.5.3.13.1.

AUTO mode

3.5.3.13.2.

PATH mode

3.5.3.13.3.

EXPLICIT mode

3.5.3.13.4.

RAW mode

3.5.3.13.5.

ROOT directive

3.5.3.13.6.

ELEMENTS directive

3.5.3.14.

FOR JSON clause

3.5.3.14.1.

AUTO mode

3.5.3.14.2.

PATH mode

3.5.3.14.3.

ROOT directive

3.5.3.14.4.

INCLUDE_NULL_VALUES directive

3.5.3.14.5.

WITHOUT_ARRAY_WRAPPER directive

3.5.3.15.

FOR UPDATE clause

3.5.3.16.

Set operations

3.5.3.16.1.

Type safety

3.5.3.16.2.

Projection rowtype

3.5.3.16.3.

Differences to standard SQL

3.5.3.16.4.

UNION

3.5.3.16.5.

UNION ALL

3.5.3.16.6.

INTERSECT

3.5.3.16.7.

INTERSECT ALL

3.5.3.16.8.

EXCEPT

3.5.3.16.9.

EXCEPT ALL

3.5.3.17.

Lexical and logical SELECT clause order

3.5.4.

The INSERT statement

3.5.4.1.

INSERT .. VALUES

3.5.4.2.

INSERT .. DEFAULT VALUES

3.5.4.3.

INSERT .. SET

3.5.4.4.

INSERT .. SELECT

3.5.4.5.

INSERT .. ON DUPLICATE KEY UPDATE

3.5.4.6.

INSERT .. ON DUPLICATE KEY UPDATE .. EXCLUDED

3.5.4.7.

INSERT .. ON DUPLICATE KEY UPDATE .. SET ALL TO EXCLUDED

3.5.4.8.

INSERT .. ON DUPLICATE KEY IGNORE

3.5.4.9.

INSERT .. ON CONFLICT

3.5.4.10.

INSERT .. ON CONFLICT .. EXCLUDED

3.5.4.11.

INSERT .. ON CONFLICT .. SET ALL TO EXCLUDED

3.5.4.12.

INSERT .. RETURNING

3.5.5.

The UPDATE statement

3.5.5.1.

UPDATE .. SET

3.5.5.2.

UPDATE .. SET ROW

3.5.5.3.

UPDATE .. FROM

3.5.5.4.

UPDATE .. WHERE

3.5.5.5.

UPDATE .. ORDER BY .. LIMIT

3.5.5.6.

UPDATE .. RETURNING

3.5.6.

The DELETE statement

3.5.6.1.

DELETE .. USING

3.5.6.2.

DELETE .. WHERE

3.5.6.3.

DELETE .. ORDER BY .. LIMIT

3.5.6.4.

DELETE .. RETURNING

3.5.7.

The MERGE statement

3.5.7.1.

USING .. ON

3.5.7.2.

WHEN MATCHED THEN UPDATE

3.5.7.3.

WHEN MATCHED THEN DELETE

3.5.7.4.

WHEN MATCHED AND ..

3.5.7.5.

WHEN NOT MATCHED THEN INSERT