Drop Shadow

JOINs in MySQL 5.0

I strongly recommend testing all of your pages that use MySQL, especially if you have complex JOINs. MySQL has slightly changed the way that it manages JOINs in a manner that may require rewriting your queries. In MySQL 4.0, comma joins had the same precedence as JOIN ONs. This has changed: commas now have lesser precedence, as the SQL standard requires.

You won’t have to worry about this unless all of the following apply:

  • You are joining three or more tables in one query;
  • You are using both commas and JOINs to join the tables;
  • You have a JOIN after you have a comma;
  • Your later JOIN(s) reference a table from before the comma.

If you have a query that exhibits this problem, it is relatively easy to fix. In most, if not all, cases you will be able to apply the fix ahead of time without harming your query behavior in MySQL 4.0.

Example

Here is an example taken from the class listings. The following query will fail, because MySQL 5 tries to join Classes up with InstructorInfo before it joins Classes up with the Sections/Instructors join:

SELECT count(*) FROM Sections, Classes LEFT JOIN Instructors ON Instructors.SectionID=Sections.ID WHERE Sections.ClassID = Classes.ID;

Where MySQL 4.0 would join Sections with Classes and then Classes with Instructors, MySQL 5.0 will join Classes with InstructorInfo before joining Sections with Classes. It will thus not know what Sections.ID is when it tries to perform that part of this query.

Solutions

There are several solutions. The most readable solution is probably to replace the comma with a JOIN:

SELECT count(*) FROM Sections JOIN Classes ON Sections.ClassID=Classes.ID LEFT JOIN Instructors ON Instructors.SectionID=Sections.ID;

Another solution is to use parentheses to force MySQL to join in a specific order:

SELECT count(*) FROM (Sections, Classes) LEFT JOIN Instructors ON Instructors.SectionID=Sections.ID WHERE Sections.ClassID = Classes.ID;

And yet another solution would be to change the order of the joins:

SELECT count(*) FROM Sections LEFT JOIN Instructors ON Instructors.SectionID=Sections.ID, Classes WHERE Sections.ClassID = Classes.ID;

All three of these solutions will work in MySQL 4.0. You can apply either of these solutions before we go live with 5.0.