Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
The WITH clause
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL:1999 standard specifies the WITH clause to be an optional clause for the SELECT statement, in order to specify common table expressions (also: CTE). Many other databases (such as PostgreSQL, SQL Server) also allow for using common table expressions also in other DML statements, such as the INSERT statement, UPDATE statement, DELETE statement, or MERGE statement.
The following example makes use of names to construct common table expressions, which can then be supplied to a WITH clause or a FROM clause of a SELECT statement:
-- Pseudo-SQL for a common table expression specification
"t1" ("f1", "f2") AS (SELECT 1, 'a')
// Code for creating a CommonTableExpression instance
name("t1").fields("f1", "f2").as(select(val(1), val("a")));
The above expression can be assigned to a variable in Java and then be used to create a full SELECT statement:
WITH "t1" ("f1", "f2") AS (SELECT 1, 'a'),
"t2" ("f3", "f4") AS (SELECT 2, 'b')
SELECT
"t1"."f1" + "t2"."f3" AS "add",
"t1"."f2" || "t2"."f4" AS "concat"
FROM "t1", "t2"
;
CommonTableExpression<Record2<Integer, String>> t1 =
name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression<Record2<Integer, String>> t2 =
name("t2").fields("f3", "f4").as(select(val(2), val("b")));
Result<?> result2 =
create.with(t1)
.with(t2)
.select(
t1.field("f1").add(t2.field("f3")).as("add"),
t1.field("f2").concat(t2.field("f4")).as("concat"))
.from(t1, t2)
.fetch();
See also the section about common table expressions.
Feedback
Do you have any feedback about this page? We'd love to hear it!