previous : next

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

The DUAL table

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

The SQL standard specifies that the FROM clause is mandatory in a SELECT statement. However, 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, DUCKDB, EXASOL, H2, MARIADB, MYSQL, ORACLE, POSTGRES, REDSHIFT, SNOWFLAKE, 
-- SQLDATAWAREHOUSE, SQLITE, SQLSERVER, TRINO, VERTICA, YUGABYTEDB
SELECT 1

-- AURORA_MYSQL, MEMSQL
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.19, 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.


previous : next

References to this page

Feedback

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

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo