How to select something “from dual” in a non-Oracle database

What is the dual equivelant in Postgres, MySQL, Sqlite, or Ms SQL Server?

I had someone ask me yesterday what Sqlite calls the dual pseudotable (for those who don’t know, dual is a heavily used, Oracle specific concept).

I started at him blankly for a minute and thought how best to answer without laughing. But honestly its a good question.

The answer is simple enough too:

You drop the FROM clause.

For Example

If you want to

select 'hello world'
from dual;

in any SQL dialect other than Oracle, you simply say this:

select 'hello world';

So, why does oracle need a from dual in the first place?

I don’t know, but I think it has something to do with a couple of water coolers (this is not a true story as far as I know).

You see, at oracle, when they were putting together early drafts of the SQL standard, they had their little select clauses, and in the original specification, some things were optional (like where clauses and order by clauses) but the from clause was not optional. I mean, why would you select something from nowhere.

That is where the water cooler comes in. A couple of developer guys down at the water cooler are taking a break because they are each struggling with something or other in the code, and the first guy vents his frustration I have to select something but it isn’t really coming from a specific table, what do I do for a from clause? — the other guy has his own equally perplexing problem, and he vents it out.

Then, they come up with a crazy idea to kill both birds with one stone. A dual solution. They will create a pseudo table named dual and it will fix both of their problems. And off they went.

The other half of the story happens at another water cooler at the development offices of another SQL database engine (not sure who). Two very similar programmers are at the water cooler, and the same question comes up. The other guy just says “Duh, let’s just make the from clause optional!” which they do.

So, the real question is not why don’t other databases have a dual pseudo table, the real question is this: why does Oracle still require the use of from dual when there isn’t a true target table? My guess is that, whoever came up with dual was so impressed with themselves, that they just can’t let it go – but that’s just a hunch.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.


No comments yet.

Leave a comment