August 02, 2008

MySQL 5 Does Not Work With ‘Comma Joins’

Posted at: 4:11 pm by Timothy Haroutunian
Categories: Tech and Web

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

Text Link Ads

Post A Comment

    7 Responses to “MySQL 5 Does Not Work With ‘Comma Joins’”

    1. pingback pingback:
      Posted: Aug 2nd, 2008 at 4:53 pm
      1
      MySQL 5 Does Not Work With ‘Comma Joins’

      [...] Original Carbon Tax Center [...]
    2. AvatarMatt
      Posted: Aug 2nd, 2008 at 7:52 pm
      2

      That isn’t true. MySQL 5 supports both Theta and ANSI joins.
    3. AvatarTimothy Haroutunian
      Posted: Aug 3rd, 2008 at 3:49 pm
      3
      Author Comment

      As soon as we made those changes to the query, it worked and there weren’t any errors. What about the query would have caused the problem?
    4. AvatarMatt
      Posted: Aug 5th, 2008 at 7:09 am
      4

      The first one looks fine but the second one is missing the “FROM” keyword.
    5. AvatarZach
      Posted: Aug 6th, 2008 at 7:59 am
      5

      I’m with Matt on this. Both of those queries are valid in MySQL 5. If it wasn’t working, there was something else wrong.
    6. Avataral
      Posted: Aug 6th, 2008 at 7:58 pm
      6

      I have to agree with Matt and Zach.
    7. AvatarTimothy Haroutunian
      Posted: Aug 6th, 2008 at 8:27 pm
      7
      Author Comment

      I will try to find out from the code, and post it. Thanks for the help
    SexyComments by BorkWeb

Leave a Comment



graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.graph element.

292 total reads, averaging 3 daily
stats powered by bSuite bStat