The jOOQ User Manual SQL building Procedural statements IF statement

previous : next

IF statement

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

Conditional branching is an essential feature of all languages. Procedural languages support the IF statement.

There are different styles of IF statements in dialects, including:

• Requiring a THEN clause for the body of a branch, in case of which no BEGIN .. END block is required for multi-statement bodies.
• Allowing a dedicated ELSIF clause for alternative, nested branches, to avoid nesting. This is mostly a syntax sugar feature only.

In jOOQ, an IF statement might look as follows:

-- PL/SQL syntax
IF i = 0 THEN
  INSERT INTO a (col) VALUES (1);
ELSIF i = 1 THEN
  INSERT INTO b (col) VALUES (2);
ELSE
  INSERT INTO c (col) VALUES (3);
END IF;

// All dialects
if_(i.eq(0)).then(
  insertInto(A).columns(A.COL).values(1)
).elsif(i.eq(1)).then(
  insertInto(B).columns(B.COL).values(2)
).else_(
  insertInto(C).columns(C.COL).values(3)
)

Notice that both if and else are reserved keywords in the Java language, so the jOOQ API cannot use them as method names. We've suffixed such conflicts with an underscore: if_() and else_().

Dialect support

This example using jOOQ:

if_(i.eq(0)).then(deleteFrom(BOOK)).else_(deleteFrom(AUTHOR))

Translates to the following dialect specific expressions:

-- AURORA_POSTGRES, DB2, EXASOL, HANA, HSQLDB, INFORMIX, MARIADB, MYSQL, ORACLE, POSTGRES, YUGABYTEDB
IF i = 0 THEN
  DELETE FROM BOOK;
ELSE
  DELETE FROM AUTHOR;
END IF

-- BIGQUERY
IF i = 0 THEN
  DELETE FROM BOOK
  WHERE TRUE;
ELSE
  DELETE FROM AUTHOR
  WHERE TRUE;
END IF

-- FIREBIRD
IF (:i = 0) THEN
  DELETE FROM BOOK;
ELSE
  DELETE FROM AUTHOR

-- H2
if (i = 0) {
  try (PreparedStatement s = c.prepareStatement(
    "DELETE FROM BOOK"
  )) {
    s.execute();
  }
} else {
  try (PreparedStatement s = c.prepareStatement(
    "DELETE FROM AUTHOR"
  )) {
    s.execute();
  }
}

-- SQLDATAWAREHOUSE, SQLSERVER
IF @i = 0
  DELETE FROM BOOK;
ELSE
  DELETE FROM AUTHOR

-- ACCESS, ASE, AURORA_MYSQL, COCKROACHDB, DERBY, DUCKDB, MEMSQL, REDSHIFT, SNOWFLAKE, SQLITE, SYBASE, TERADATA, TRINO, 
-- VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

The jOOQ User Manual SQL building Procedural statements IF statement

previous : next