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, CockroachDB, DB2, Exasol, HSQLDB, Hana, Informix, MariaDB, MySQL, Oracle, Postgres, Trino, 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();
  }
}

Snowflake

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

SQLDataWarehouse, SQLServer

IF @i = 0
  DELETE FROM BOOK;
ELSE
  DELETE FROM AUTHOR;

ASE, Access, Aurora MySQL, ClickHouse, Databricks, Derby, DuckDB, MemSQL, Redshift, SQLite, Sybase, Teradata, Vertica

/* UNSUPPORTED */

The jOOQ User Manual SQL building Procedural statements IF statement

previous : next