New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
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 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
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.
Feedback
Do you have any feedback about this page? We'd love to hear it!