Alias an entire table’s fields

Let’s say I am querying/joining two tables, each with certain identical field names:

SELECT * FROM sales1 s1 JOIN sales2 s2 USING (id)

Is it possible to prefix the columns for each table with the tablename (or some other prefix), so for example instead of getting data such as:

id   id    product     product    etc.

It looks like:

s1_id     s2_id     s1_product       s2_product     etc.

Without doing it manually for every single field?

Answer

In MySQL you can do:

EXPLAIN SELECT * FROM sales1 s1 JOIN sales2 s2 USING (id);

Followed by:

SHOW WARNINGS;

That will show you a rewritten version of the original query with quoted, fully qualified column and table names like so:

/* select#1 */ select `dbname`.`s1`.`id` AS `id`,`dbname`.`s1`.`product` AS `product`,`dbname`.`s2`.`product` AS `product` from `dbname`.`sales1` `s1` join `dbname`.`sales2` `s2` where (`dbname`.`s2`.`id` = `dbname`.`s1`.`id`)

You then need to do some regex find-replace afterwards. So yes, do it manually.