0

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

enter image description here

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.

Katsu
  • 8,479
  • 3
  • 15
  • 16
  • The 2nd link is helpful but does not answer how to prefix all columns automatically once joined as `table2_col1`, `table2_col2` if you shouldn't use SELECT *? – Katsu May 17 '23 at 05:15
  • The 1st link sadly does not answer it either. – Katsu May 17 '23 at 05:16
  • 3
    there is no "automatic" (at least without dynamic sql). select only the columns you actually want, and alias them appropriately. – ysth May 17 '23 at 05:39
  • worth noting that if you `inner join b using (product_id)` instead of `inner join b on b.product_id=a.product_id`, you will only get one product_id column from `select *` (but still, don't do `select *`) – ysth May 17 '23 at 05:42
  • @ysth thank you. i wonder how people that do sql in production overcome this hurdle if they actually need to select 100+ relevant rows – Katsu May 18 '23 at 06:06
  • If you are only selecting the columns you actually need, there is little extra difficulty in adding aliases – ysth May 18 '23 at 06:34

1 Answers1

0

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
Shachar297
  • 599
  • 2
  • 7
  • you can't use an expression as an alias, only an identifier or string – ysth May 17 '23 at 07:25
  • Thank you, but sadly this means if you had 100 columns you would be typing `CONCAT('prefix_', column_n)` 100 times – Katsu May 18 '23 at 06:04