I'm wondering if this is possible in SQL. Say you have two tables A and B, and you do a select on table A and join on table B:
SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);
If table A has columns 'a_id', 'name', and 'some_id', and table B has 'b_id', 'name', and 'some_id', the query will return columns 'a_id', 'name', 'some_id', 'b_id', 'name', 'some_id'. Is there any way to prefix the column names of table B without listing every column individually? The equivalent of this:
SELECT a.*, b.b_id as 'b.b_id', b.name as 'b.name', b.some_id as 'b.some_id'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);
But, as mentioned, without listing every column, so something like:
SELECT a.*, b.* as 'b.*'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);
Basically something to say, "prefix every column returned by b.* with 'something'". Is this possible or am I out of luck?
Advice on not using SELECT * and so on is valid advice but not relevant in my context, so please stick to the problem at hand -- is it possible to add a prefix (a constant specified in the SQL query) to all the column names of a table in a join?
My ultimate goal is to be able to do a SELECT * on two tables with a join, and be able to tell, from the names of the columns I get in my result set, which columns came from table A and which columns came from table B. Again, I don't want to have to list columns individually, I need to be able to do a SELECT *.
I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.