On Tue, 9 Jun 2009, Simon Slavin wrote: >> SELECT id, url, selected, name FROM db1.test UNION >> SELECT id, url, selected, name FROM db2.test >> ORDER BY name ASC, id DESC LIMIT 100" > > I would add to Marin's answer that reading the above, I have no idea > which database the column 'name' is in. Suppose db1 and db2 both had > a 'name' column. Which one would it be. So it might be better to use > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected, name FROM db2.test > ORDER BY db1.name ASC, db2.id DESC LIMIT 100
The answer to the question 'If both had a name column, which one would it be?' is both. The UNION operator creates a resultset having a single name column which contains values from both tables. ORDER BY operates on this combined resultset, it cannot distinguish between the name values contributed by db1 and those contributed by db2. The ordering is only performed on the combined results, not on the separate SELECT clauses. Once the UNION is generated, there is only a single name column to be sorted. So the 'ORDER BY db1.name' shown above gives the same results as if you entered 'ORDER BY name'. The 'db1' prefix is ignored in this context. The question "suppose both had a name column" may reflect a misunderstanding of the UNION. Each SELECT clause of a union must contribute the same number of columns, thus, it is not possible to have 'name' coming only from one SELECT clause and not the other. For instance, this will fail: SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected FROM db2.test; It fails because the first SELECT has 4 cols, the 2nd has 3. If db2.test in fact does not have a name column, but you want include name values from db1, you need to use a placeholder like this: SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected, NULL FROM db2.test; or this SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected, ' ' FROM db2.test; The result of these queries will have values from db1 for name, but the name column for data contributed by db2 will be NULL/empty. Chris _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
