List:General Discussion« Previous MessageNext Message »
From:Graham Anderson Date:May 24 2006 3:35pm
Subject:Re: Noob: Converting to Inner Join
View as plain text  
many thanks Chris :)
g


On May 24, 2006, at 1:19 AM, Chris Sansom wrote:

> 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