SQLite Forum

SELECT * Column Sequence / Order (not ORDER BY)
The SQL standard dictates nothing to that effect, neither does Set theory (on which relational databasing is modeled).

TLDR: -----------------

It kinda mostly works that way, for now, but it's BAD and unsafe to use it like that, and you really shouldn't.


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