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

Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8

The DUAL table

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

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

Dialect support

This example using jOOQ:

select(inline(1))

Translates to the following dialect specific expressions:

-- ACCESS
SELECT 1
FROM (
  SELECT count(*) dual
  FROM MSysResources
) AS dual

-- ASE, AURORA_POSTGRES, BIGQUERY, COCKROACHDB, EXASOL, H2, MARIADB, MYSQL, POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, 
-- SQLITE, SQLSERVER, VERTICA, YUGABYTEDB
SELECT 1

-- AURORA_MYSQL, MEMSQL, ORACLE
SELECT 1
FROM DUAL

-- DB2
SELECT 1
FROM SYSIBM.DUAL

-- DERBY
SELECT 1
FROM SYSIBM.SYSDUMMY1

-- FIREBIRD
SELECT 1
FROM RDB$DATABASE

-- HANA, SYBASE
SELECT 1
FROM SYS.DUMMY

-- HSQLDB
SELECT 1
FROM (VALUES(1)) AS dual(dual)

-- INFORMIX
SELECT 1
FROM (
  SELECT 1 AS dual
  FROM systables
  WHERE (tabid = 1)
) AS dual

-- TERADATA
SELECT 1
FROM (
  SELECT 1 AS "dual"
) AS "dual"

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

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 : SQL building : Table expressions : The DUAL tableprevious : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

Community

Support

Legal

Documentation

The jOOQ Logo