Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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 aWHERE
clause) - The ones that require a
FROM
clause only with aWHERE
clause,GROUP BY
clause, orHAVING
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 nestedSELECT
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, 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.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!