I might've said that a little wrong, but okies, I think I get it. I was a little confused at first because I'm just learning about joins - I have to at the moment, because while I'm relatively familiar with the idea of using multiple tables to present information in the most optimal format (such as using a primary table to hold data, with corresponding column ids to pull information out of other tables, such as related categories etc), I'm not so up to speed on how to connect all that data inside of a single query.
My first simple query was to pull out a news item from the main table, and then get the appropriate category name based on it's ID from another table. So I did this:
Code:
SELECT news.id, news_categories.name AS category, news.uid, news.headline, news.body
FROM `news`
INNER JOIN `news_categories` ON news.cid = news_categories.cid
WHERE news.id = '%d'
LIMIT 1
Worked fine for me, first try. Then I moved on to optimizing my next query, which already used a LEFT JOIN (credits to Orc for helping me on that one) and I thought wait a minute... so I went back to the first, and switched the INNER with a LEFT and got the exact same result. At that point I wasn't clear what I had just done... so I went on to the next query to see if it produced the same results;
Code:
SELECT news.id, news.uid, news_categories.name AS category, news.headline, news.body, count( DISTINCT news_comments.id ) AS total_comments
FROM `news`
LEFT JOIN `news_categories` ON news.cid = news_categories.cid
LEFT JOIN `news_comments` ON news_comments.nid = news.id
WHERE news.cid = '%d'
GROUP BY news.id
ORDER BY news.id DESC
...and I switched the LEFT with an INNER. At first I thought I had gotten the same result set back, and was starting to think that there was something suspicious going on here. Then I took a closer looked and realized the second query, when done with an INNER only returned results from `news` when there was associated comments in `news_comments`.
So that's why I boiled it down to what I did. LEFT appeared to be pulling out all results from the primary table, whether or not it could find matching results in the second table (returning NULL where there were none). INNER seemed to be only returning results from the first table that could find corresponding results in the second.
So that brings up a small second question - is a LEFT JOIN and LEFT OUTER JOIN the exact same thing? If so, are both fully acceptable by general SQL syntax, or is the shortened version something that only MySQL allows?
-m