Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
er, You can do pretty much exactly that in SQL with table value constructors. SELECT * FROM (VALUES('james', 'dean'), ('tom', 'cruisse')) T(FirstName, LastName) –  Martin Smith Jun 4 '12 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 '12 at 13:34
2  
You can also just use a bunch of UNION ALL statements. There is no real issue here. –  RedFilter Jun 4 '12 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 '12 at 14:19
2  
Creating tables and putting values into them - the horror! –  JeffO Jun 4 '12 at 18:56
add comment

migrated from stackoverflow.com Jun 4 '12 at 13:36

This question came from our site for professional and enthusiast programmers.

2 Answers

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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