The jOOQ User Manual. Multiple Pages : SQL building : Table expressions : The DUAL tableprevious : next

All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6

The SQL standard specifies that the FROM clause is optional in a SELECT statement. However, according to the standard, you may then no longer use some other clauses, such as the WHERE clause. In the real world, there exist three types of databases:

  • The ones that always require a FROM clause (as required by the SQL standard)
  • The ones that never require a FROM clause (and still allow a WHERE clause)
  • The ones that require a FROM clause only with a WHERE clause, GROUP BY clause, or HAVING clause

With jOOQ, you don't have to worry about the above distinction of SQL dialects. jOOQ never requires a FROM clause, but renders the necessary "DUAL" table, if needed. The following program shows how jOOQ renders "DUAL" tables 

SELECT 1 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
SELECT 1
SELECT 1 FROM "db_root"
SELECT 1 FROM "SYSIBM"."DUAL"
SELECT 1 FROM "SYSIBM"."SYSDUMMY1"
SELECT 1 FROM "RDB$DATABASE"
SELECT 1 FROM dual
SELECT 1 FROM "SYS"."DUMMY"
SELECT 1 FROM "INFORMATION_SCHEMA"."SYSTEM_USERS"
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE tabid = 1)
SELECT 1 FROM (SELECT 1 AS dual) AS dual
SELECT 1 FROM dual
SELECT 1 FROM dual
SELECT 1 FROM dual
SELECT 1
SELECT 1
SELECT 1
SELECT 1 FROM [SYS].[DUMMY]

DSL.using(SQLDialect.ACCESS   ).selectOne().getSQL();
DSL.using(SQLDialect.ASE      ).selectOne().getSQL();
DSL.using(SQLDialect.CUBRID   ).selectOne().getSQL();
DSL.using(SQLDialect.DB2      ).selectOne().getSQL();
DSL.using(SQLDialect.DERBY    ).selectOne().getSQL();
DSL.using(SQLDialect.FIREBIRD ).selectOne().getSQL();
DSL.using(SQLDialect.H2       ).selectOne().getSQL();
DSL.using(SQLDialect.HANA     ).selectOne().getSQL();
DSL.using(SQLDialect.HSQLDB   ).selectOne().getSQL();
DSL.using(SQLDialect.INFORMIX ).selectOne().getSQL();
DSL.using(SQLDialect.INGRES   ).selectOne().getSQL();
DSL.using(SQLDialect.MARIADB  ).selectOne().getSQL();
DSL.using(SQLDialect.MYSQL    ).selectOne().getSQL();
DSL.using(SQLDialect.ORACLE   ).selectOne().getSQL();
DSL.using(SQLDialect.POSTGRES ).selectOne().getSQL();
DSL.using(SQLDialect.SQLITE   ).selectOne().getSQL();
DSL.using(SQLDialect.SQLSERVER).selectOne().getSQL();
DSL.using(SQLDialect.SYBASE   ).selectOne().getSQL();

Note, that some databases (H2, MySQL) can normally do without "DUAL". However, there exist some corner-cases with complex nested SELECT statements, where this will cause syntax errors (or parser bugs). To stay on the safe side, jOOQ will always render "dual" in those dialects.


The jOOQ User Manual. Multiple Pages : SQL building : Table expressions : The DUAL tableprevious : next

Community

Support

Legal

Documentation

The jOOQ Logo