SELECT * Column Sequence / Order (not ORDER BY)
Trying to verify that SELECT * will always return results based on the column order in the CREATE TABLE statement. From my reading, SQL should use the column ordinal position for SELECT * but I don't know how to check this in SQLite. Also, threads like this make me nervous:
See caution area. I realize this is SQLServer but I'd like to know without a doubt if SELECT * has a well defined returned or if I really need to list all the fields out.
You should list out all the columns you want in your application code. The "*" notation is useful when doing ad hoc queries from a command-line but should be avoided when constructing queries that are baked into an application because the order of the columns might change and/or new columns might be added.
The SQL standard dictates nothing to that effect, neither does Set theory (on which relational databasing is modeled).
It kinda mostly works that way, for now, but it's BAD and unsafe to use it like that, and you really shouldn't.
Firstly: You get a bag-o'-stuff if you ask for *, the order of neither the ROWs, nor the COLUMNs are guaranteed or implicit in any way, and the SQL engine du jour is free to return as they wish, and often will do it.
Secondly: The way to get your ROWs back in a specific order, is to use an ORDER BY statement. The way to get the COLUMNs back in a specific order, is to state them in a specific order. Most systems do not care very much about the order, they typically (and correctly) specify unambiguous names for columns, and read them by lookup of name reference.
How can you care very deeply about the order of the columns are coming in and then not wish to specify them?
The columns in a VIEW is guaranteed, because at its heart, a VIEW is a query result. Most engines do not allow ORDER BY clauses in their VIEWs, because of this. You might make a view for every table you have, though it's of no benefit unless you query the table like this 500 places in your code (which is bad design for other reasons I won't go into here).
Lastly: The hard rule: If you care about any ordering when asking for a bag of stuff, you must be specific which order you want them in. MUST.
That said, it is the current habit of choice of SQLite specifically to return the columns in the order they are created in the internal structure, I assume which is simply because it happens to be the order in which they are parsed, which happens to be the order in which they appear in the CREATE statement. So far, simple testing will show it is the case for most recent versions. You can use it at your own peril, if you update later and it suddenly no longer follows this rule, then you will have a LOT more work than now listing a few COLUMNs in your queries. (A thing for which every DB-manager out there has a button or single statement to produce, in an instant).
Thank you for the extra detail here. My bag of stuff is now specifically ordered!
The SQL standard dictates nothing to that effect
Actually, the SQL-92 specification says that
the <select list> "*" is equivalent to a <value expression> sequence in which each <value expression> is a <column reference> that references a column of T and each column of T is referenced exactly once. The columns are referenced in the ascending sequence of their ordinal position within T.
The problem with * is not that the database might reorder the columns, but that humans will tell the database to alter them in the table.
So I saw that in a post from section 7.9 3b of that standard. What I couldn't find was 1) where SQLite promises to meet that standard and 2) how to have SQLite tell me the ordinal position of the columns as part of the table definition.
Either way, it seems SELECT * is generally bad practice for at least the reason you mention about a programmatic change to the column order (which I don't have but over time you never know).
how to have SQLite tell me the ordinal position of the columns as part of the table definition
Assuming you have a table named "t1", this will do:
SELECT name, cid FROM pragma_table_info('t1'); --- or --- SELECT name FROM pragma_table_info('t1') ORDER BY cid ASC;
This will work with the pre-compiled binaries from the site, but I think only after 3.27 or so - not 100% sure, but if you use older versions in production, note that there was a time, not long ago, when this did not work yet.
Oh, I forgot to add...
SELECT * FROM pragma_table_info('t1');
Oh, thanks for posting this Clemens. Do you know if the SQL99 still had this, I thought it was redacted, or at least the reference to ordering.
I'm interested because, if not, I need to adjust my understanding and view on the subject in general.
Exactly the same text is still in SQL:2003 and SQL:2011.
Will there be a possibility for reordering columns? I.e. that one can use something like:
ALTER TABLE Employees CHANGE empName empName VARCHAR(50) NOT NULL AFTER department; or
ALTER TABLE UserOrder CHANGE order_id order_id INT(11) NOT NULL FIRST;
Are there any plans for the next releases?
The virtual table interface explicitly enumerates the columns in definition order and references the columns by number when calling the virtual table's methods. I think SQLite developers would be reluctant to change internal structures because of the extra work that would entail in preserving this documented interface.
The bigger risk for application coders using "*" is the table gets reconstructed with additional fields.
(13) By Gunter Hick (gunter_hick) on 2020-06-04 06:44:28 in reply to 1 [source]
SQL has some human friendly features which should not be used in application coding. Ever. SELECT * does not guarantee any specific order or even the names of columns in the result set, which in the case of a JOIN may not even be unique, and can change between releases. Likewise, INSERT without a column list assumes that the table definition is exactly the same as when the statement was designed. Any change in the ordinal position of any of the columns will silently insert values into "wrong" columns. Any change in the total number of columns will fail if the number of values no longer matches the number of columns.