previous : next

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

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

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.

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, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, H2, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, 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"
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website

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