ANSI SQL/Foundation

I have believed for some time that the SQL FROM clause is optional, that is optional from the standpoint of ANSI/IEC/ISO standards, not from the standpoint of Oracle of course.

For example, in SQL Server, MySQL, PostgreSQL, and then some

SELECT 1 x;

is grammatically fine, whereas other RDBMS vendor implementations require a dummy table or sorts. IBM DB2 for example has the SYSIBM.SYSDUMMY1 table, where even the name gives you an indication that it’s a dummy table required for some reason. Oracle has the peculiarly named DUAL table, where DUAL as a word in English indicates two parts to something (but with true Oracle clarity it is a table containing one row and a one column), that exists for the same purpose.

In Oracle, the special DUAL table can be used as, for example:

SELECT 1 x FROM DUAL;

I decided to purchase and read the ANSI/IEC/ISO SQL standards, partly out of curiosity, and partly to formalise my understanding of the programming language. The document set is a very dry read. With respect to the FROM clause, I am not sure whether the current Standard Definitions were the same as they are now when the various vendors implemented their RDBMS implementations.

I have included inline below an excerpt from ISO/IEC 9075-2:2011(E)

Currently, as defined in SQL/Foundation,  the FROM clause is not optional. The <table expression> must contain a FROM clause, and as far as I can fathom, the <table expression> is not optional.

I often use PostgreSQL (as a back-end DBMS/I develop in C# via. an ORM). I’ve never had a problem – it appears a great RDBMS implementation. The “About” page of PostgreSQL even boasts “PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard ….”. Perhaps this was one of the changes approved between the 2008 and 2011 Standards. I don’t know. I do know I was surprised to find Oracle adhering to the current reference Standard document implementation (and to be fair IBM DB2 and good old Faircom c-treeACE adhere to the current Standard too), whereas most of the other RDBMS vendors were the violators.

This does not fit with my preconceived views.

— Published by Mike, 17:05:13 21 August 2016