Page 1 of 1

MySQL 5 - Time to stop being lazy

Posted: Mon Jan 08, 2007 7:07 am
by onion2k
Today I ran one of my sites using MySQL 5 as a backend.

Oh deary me.

That wasn't good. It turns out that MySQL 5 really doesn't like my lazy JOIN syntax at all. Throughout my sites I have tables joined together like:

Code: Select all

select * from user, country
LEFT JOIN privs on privs.user_id = user.user_id
WHERE
user.country_id = country.country_id
It's quick, it's easy to understand, and it doesn't work in MySQL 5. If you're mixing join types you have to specify all of them explicitly, eg:

Code: Select all

select * from user
INNER JOIN country ON user.country_id = country.country_id
LEFT JOIN privs ON privs.user_id = user.user_id
So... if, like me, you're used to using commas to join tables, I suggest you stop and start learning the proper syntax instead. Or you'll be rewriting a whole heap of SQL when you eventually come to upgrade.

Posted: Mon Jan 08, 2007 8:46 am
by feyd
Ahh, the benefits of not being lazy. :)

Posted: Mon Jan 08, 2007 9:50 am
by Chris Corbyn
You could actually just re-order your tables in that query:

Code: Select all

SELECT
    *
FROM
    country,
    user LEFT JOIN privs ON privs.user_id = user.user_id
WHERE
    user.country_id = country.country_id
As long as the JOIN follows the table it relates to you're OK: "FROM.., user left join privs"

Posted: Mon Jan 08, 2007 10:08 am
by CoderGoblin
Later versions of postgres are also the same. You need to include all tablenames in the select within the FROM rather than tagging them in the WHERE condition.