On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the wall:
> "select *" is shorthand for "all columns". You'll note that what's > returned isn't some kind of special '*' column, but all columns. The > order in which the columns are returned isn't meaningful because the > colums have labels -- names -- to tell you which is which. Rearranging > the column order doesn't change the answer. That's not quite true. What you say is more or less true in pure Relational Theory. Under Relational Theory, relational attributes (columns) are a proper set. The columns have no defined order (just as rows have no defined order), and can only be definitively reference by name. In SQL, columns are *not* a set. The order of the columns in any SQL query or operation is strictly defined. Columns cannot be referenced by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and multiple columns with the same name (SELECT 1 A, 1 A, 1 A;). SQL doesn't even strictly define the column name for a calculated column (SELECT avg( 1 )) and allows the DB to make up its own names. SQLite used to have several PRAGMAs to control short and long column names. Rather, in SQL, a column is definitively defined by its positional index in the table or result set. This is also why so many SQL APIs allow you to fetch column values by index, rather than by name (which would be a totally broken and dangerous API if columns could move around). It gets pretty messy... The SQL standard goes to some length to define a specific column order for stuff like JOIN operations, including edge-case details like NATURAL JOINs where the number of columns is reduced and somewhat ambiguously named. While rearranging the column order may not functionally change the answer, a database is not given that flexibility in SQL. For example, "SELECT *" *must* return the columns in the order they are defined in the table definition. It isn't that most databases just happen to do this-- the column order is actually predicated by the standard. > "sort by *" would imply that the order of the columns returned by '*' is > meaningful, which it is not. "sort by the arbitrary order produced by > 'select *'" isn't even deterministic. In SQL column order *is* deterministic, so the sort order would also be deterministic. Likely meaningless, but still deterministic. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
