Aug 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

About the author

Timothy Haroutunian

Timothy Haroutunian is a ServiceNow Cloud Implementation Specialist at Acorio. ServiceNow is an IT Management solution that allows for a complete view of your IT and physical environment.

Permanent link to this article: http://www.armenianeagle.com/2008/08/02/mysql-5-does-not-work-with-comma-joins/



1 ping

Skip to comment form

  1. Matt

    That isn’t true. MySQL 5 supports both Theta and ANSI joins.

  2. Timothy Haroutunian

    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?

  3. Matt

    The first one looks fine but the second one is missing the “FROM” keyword.

  4. Zach

    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.

  5. al

    I have to agree with Matt and Zach.

  6. Timothy Haroutunian

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

  1. MySQL 5 Does Not Work With ‚??Comma Joins‚??

    […] Original Carbon Tax Center […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.