SQL Trouble with dummy tables


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

Tags: , , , , , , , , , ,

Trackbacks / Pingbacks

  1. An MS Access SQL Transformation Odyssey | Java, SQL and jOOQ. - February 19, 2014

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,628 other followers

%d bloggers like this: