SQL Trouble with dummy tables

Posted on by lukaseder
As I’m mostly using Oracle for work projects, the concept of the DUAL dummy table has become quite intuitive. I hardly ever think about the days when I was playing around with that table to find out its purpose (e.g. writing into it when DUAL was still a physical object, and thus killing the whole database…) In many other RDBMS, there is no need for dummy tables, as you can issue statements like these: 

SELECT 1;
SELECT 1 + 1;
SELECT SQRT(2);
These are the RDBMS, where the above is generally possible:
  • H2
  • MySQL
  • Ingres
  • Postgres
  • SQLite
  • SQL Server
  • Sybase ASE
In other RDBMS, dummy tables are required, like in Oracle. Hence, you’ll need to write things like these: 

SELECT 1       FROM DUAL;
SELECT 1 + 1   FROM DUAL;
SELECT SQRT(2) FROM DUAL;
These are the RDBMS and their respective dummy tables:
  • DB2: SYSIBM.DUAL
  • Derby: SYSIBM.SYSDUMMY1
  • H2: Optionally supports DUAL
  • HSQLDB: INFORMATION_SCHEMA.SYSTEM_USERS
  • MySQL: Optionally supports DUAL
  • Oracle: DUAL
  • Sybase SQL Anywhere: SYS.DUMMY

Trouble when avoiding dummy tables

While some might find avoiding dummy tables in H2 or MySQL better as SQL becomes more readable, it is worth mentioning that you can run into trouble when doing so: MySQL’s avoiding DUAL Clauses such as the following one seem to cause trouble in MySQL in some contexts: 

-- this can cause trouble
exists (select 1 where 1 = 0)

-- this will work
exists (select 1 from dual where 1 = 0)
Other similar clauses exist Ingres has no DUAL, but would actually need it In Ingres, you cannot have a WHERE, GROUP BY or HAVING clause without a FROM clause. Without dummy tables, you will have to create your own dummy subquery: 

SELECT 1 WHERE 1 = 1

-- this is a workaround with a nested select
SELECT 1 FROM (SELECT 1) AS DUAL WHERE 1 = 1
In general, jOOQ will hide these facts from client code, allowing to always use the simple form without dummy table. You don’t have to worry about overly restrictive syntactic rules in some SQL dialects

Published by lukaseder

I made jOOQ

Leave a Reply