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.

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?

  • er, You can do pretty much exactly that in SQL with table value constructors. SELECT * FROM (VALUES('james', 'dean'), ('tom', 'cruisse')) T(FirstName, LastName) Jun 4, 2012 at 13:32
  • what are table constructors? is this part of the basic SQL specification, or is vendor-specific? do you have a reference about it?
    – David Portabella
    Jun 4, 2012 at 13:34
  • 2
    You can also just use a bunch of UNION ALL statements. There is no real issue here.
    – RedFilter
    Jun 4, 2012 at 13:34
  • 5
    SQl is not a poor language just because it doesn't do what you think it should do the way you think it should do it.
    – HLGEM
    Jun 4, 2012 at 14:19
  • 2
    Creating tables and putting values into them - the horror!
    – JeffO
    Jun 4, 2012 at 18:56

2 Answers 2


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


"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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.