MySQL 5 - Time to stop being lazy

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

MySQL 5 - Time to stop being lazy

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Ahh, the benefits of not being lazy. :)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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"
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
Post Reply