SQL language drawbacks, The Third Manifesto

Question

Sometime ago I read about SQL language drawbacks (the basic language specification, not vendor specific),

and one of the drawbacks was that the language does not allow to create a set of tuples that don't come from a table.
For instance,

SELECT firstName, lastName from people;

this creates a set of tuples coming from the table people.

Now, if I don't have this table people, and I want to return a constant, I'd need something like this to return a set of two tuples (this would not require to have a table):

SELECT VALUES('james', 'dean'), ('tom', 'cruisse');

Why I would need that?
Because of the same reasons that we can define constants (not only basic types, but objects and arrays also) in any advanced programming language.

Workarounds,
Yes, I could create a temporal table, fill the data, and SELECT from that table. This is a hack, to overcome the drawbacks of the poor SQL language.

I think that I read about this somewhere in "The Third Manifesto",
but I don't find the paragraph/example talking about this concrete drawback anymore.

Do you know a reference about it?

Answer 1

As far as I know the SQL standard defines a "row constructor".

So in standard SQL (and e.g. supported by PostgreSQL and DB2) it's valid to write

VALUES ('james', 'dean'), ('tom', 'cruise');

anyhwere a table reference is allowed

Answer 2

"that the language does not allow to create a set of tuples that don't come from a table. "

This is not entirely correct. The standard SQL language has both ROW constructors - ROW('james','dean') - and table constructors - VALUES ( (col1v1, col2v1) (col1v2, col2v2) ). I'm not entirely certain of the VALUES syntax, so take with a grain of salt here.

Thus, standard SQL indeed has things that resemble

SELECT * FROM VALUES((...),(...));

Standard SQL also has the extremely verbose

SELECT col1v1 AS c1, col2v1 as c2 UNION SELECT col1v2 AS c1, col2v2 as c2 UNION ...

I cannot point you to a reference yet (other than the SQL standard itself), but reliable sources tell me that a new book is in preparation that will discuss exactly this whole issue of SQL's deficiencies against Third Manifesto norms.