August 02, 2008
MySQL 5 Does Not Work With ‘Comma Joins’
At work, we are redesigning one of our websites and we couldn’t completely get rid of our old data since other websites/cms still use it. On the new site, we standardized our database structure, used transactions and started using views once we converted from mysql 4 to mysql 5. The change went pretty smooth until we realized one of the changes in mysql 5, caused a lot of problems with our old code. Luckily it was on our development server and were able to make the changes before we went live with mysql 5.
The code was originally written around 1999 and contained Comma Joins.
SELECT * FROM users as u, user_details as d WHERE u.id = d.user_id
A comma separated joins are not as efficient nor are they the norm in web development. The “as” in “users as u” is not required, but that is my style.
If you have any of those joins and you move to mysql 5, you will run into some issues. You will want to convert those to INNER JOINS or LEFT JOINS, etc…
SELECT * FROM users as u INNER JOIN user_details as d ON u.id = d.user_id
Hope this helps!
*Thanks Matt for noticing the missing FROM
