In a join, how to prefix all column names with the table they came from

Use Dynamic SQL to Auto-generate Table Prefixes

Why manually prefix columns when we can get our hands on some dynamic SQL magic? Let's create column aliases dynamically using the INFORMATION_SCHEMA.COLUMNS. But beware of SQL injections!

SELECT GROUP_CONCAT(CONCAT('t1.', COLUMN_NAME, ' AS `t1_', COLUMN_NAME, '`')) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1' INTO @sql1;
-- Our SQL has been to Hogwarts now, it's dynamic! 	🪄


Simply repeat for every single table substituting the @sql variables, then you can piece together your queries using the auto-generated aliases.

Organize Columns By Table Origin

To make our queries more readable and easy to analyze, let's put our columns in order by their origin table. This is the SQL equivalent of arranging your books by author - neat isn't it? A delimiter column, for instance NULL AS '--Boundary--', could be just the right marker we need to set apart between our columns and tables.

Clutter-free Code with Conditional Prefixing

While prefixes enhance clarity, there's no need to fight off windmills; if column names are unique across tables, prefixes may not be needed. They could turn into unnecessary clutter instead. To avoid long and winding output fields, use the EXCEPT keyword to exclude any non-essential fields. It's all about hitting the sweet spot between clarity and maintainability.

Code Faster with Tools and Scripts

Got a good text editor? Use its multiple cursors or column mode to supercharge your aliasing. With the right script, you can turbocharge this using powerful commands such as GROUP_CONCAT and CONCAT. Pro tip: Always remember to set group_concat_max_len to a suitable length if dealing with wide tables.

Future-proof Your Code

Proposals exist for ANSI SQL 'dot-star' (.*) with custom prefix/postfix that could make aliasing even more intuitive in the future. Whenever these changes come, they promise to enhance readability and smooth out our coding experience. Stay tuned!

Custom Prefixing

Different use cases may require different custom prefixes or postfixes. Not to worry, though! Dynamic SQL is flexible and allows for tailor-made solutions. Don't forget to override prefixes or postfixes as needed.