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, HSQLDB, Hana, 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;
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Derby, DuckDB, MemSQL, Redshift, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!