List:General Discussion« Previous MessageNext Message »
From:Chris Sansom Date:May 24 2006 8:19am
Subject:Re: Noob: Converting to Inner Join
View as plain text  
At 23:17 -0700 23/5/06, Graham Anderson wrote:
>Are there any advantages to converting this 'working' query below to 
>use INNER JOIN ?
>If so, what would the correct syntax be ?
>
>Many thanks
>
>
>SELECT category.name, page.name, content.title, content.body
>FROM category, page, content
>WHERE content.page_id = page.id
>AND page.category_id = category.id
>AND category.id =1
>ORDER BY content.order_id ASC
>LIMIT 0 , 30

 From my limited knowledge (I'm a relative newbie and open to 
correction!) one syntax would be:

SELECT category.name, page.name, content.title, content.body
FROM category INNER JOIN page INNER JOIN content
WHERE content.page_id = page.id
AND page.category_id = category.id
AND category.id = 1
ORDER BY content.order_id ASC
LIMIT 0 , 30

...ie., simply replace your commas with 'INNER JOIN'. Or you could do this:

SELECT category.name, page.name, content.title, content.body
FROM category
INNER JOIN page ON page.category_id = category.id
INNER JOIN content ON content.page_id = page.id
WHERE category.id = 1
ORDER BY content.order_id ASC
LIMIT 0 , 30

As for advantages, I'm not sure there are any for this particular 
query. The advantages would arise if you were to combine it with 
different JOINs, eg LEFT JOIN, because (in MySQL 5.x anyway) 'INNER 
JOIN' has a higher syntactical priority than the comma, which is the 
lowest priority of all. In other words, if you were to put a LEFT 
JOIN after your comma joins, MySQL would try to execute the LEFT JOIN 
first, but if you used INNER JOIN, that would be done first.

I think that's about right. :-)

-- 
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

I wonder who discovered you could get milk from a cow...
and what on _earth_ did he think he was doing?
    -- Billy Connolly
Thread
Noob: Converting to Inner JoinGraham Anderson24 May
  • Re: Noob: Converting to Inner JoinChris Sansom24 May
    • Re: Noob: Converting to Inner JoinGraham Anderson24 May
    • Re: Noob: Converting to Inner JoinPeter Brawley24 May
      • Re: Noob: Converting to Inner JoinChris Sansom24 May
        • Re: Noob: Converting to Inner JoinPeter Brawley24 May