Automatically add a prefix or suffix onto all columns after a JOIN

Question

I have a sql query that joins 2 tables on the column product_id. Pretty straight forward. Result is below:

However, after joining, I realized I have to do a subquery with all the columns listed here. I obviously need to remove or alias one of the product_id columns to prevent ambiguity. Since this is a small table, its not that bad in terms of efficiency to just SELECT the relevant columns (7 total). However, this led to me wondering, what if I had 2 giant production-level tables with 100 columns each? It would be incredibly inefficient to type out and SELECT 199 columns.

So, thats a long winded way of asking: Is there a way to add a prefix or a suffix to the joined table automatically when joining it (eg. it would say table2_product_id)? Or is there a faster way I am unaware of?

Edit: Theres actually 2 customer_id's too which further supports my question.

Answer 1

As someone said in the comments, there is no automatic. there's a function you can add to your query to do it.

you can use a concat("str", "suffix") or even concat("prefix_", "str", "_suffix") and you the as to alias your column name for the result

SELECT
    CONCAT('prefix_', t1.column1) AS column1,
    CONCAT(t1.column2, '_suffix') AS column2,
    CONCAT('prefix_', t2.column3, '_suffix') AS column3