Avoiding MySQL ERROR 1052 by Prefixing Column Names in Multi Table Queries
MySQL is fairly relaxed about prefixing column names with a table name or alias in a query involving multiple tables. As long as the column name you use only exists in one of the tables in your query, MySQL does not require you to prefix it, regardless of SQL mode.
My policy has always been to prefix all column names with the table name/alias in a multiple table query. This is not simply a syle choice. If I reference a column name without prefixing it the query may work today, but it is not future-proof. If the schema changes in the future and I add a column with the same name to one of the other tables involved in that query, then the query will fail with ERROR 1052:
Therefore I recommend always prefixing all column names in a multi table query to protect your queries against future additive schema changes.
Here’s some code to illustrate the problem:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19