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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
