Inner Join vs Left Join

delayedinsanity · 08-05-2008, 07:09 AM

Just curious if I have this correct?

An SQL inner join will return all records, but only if they match the conditions defined by the ON clause, and a left join will return all records, even if the conditions in the ON clause aren't met? Boiled down?
-m

CoryMathews · 08-05-2008, 03:26 PM

not quite, If i remember correctly the left join returns all records when the first table (or the left one) has a value that is not null and still meets the conditions of the ON clause. And the Inner join returns when both of them are not null. Outer returns all rows as long as one of them is not null and right does the opposite of left.

Pretty sure thats how it works.

buggabill · 08-05-2008, 05:51 PM

Here is a pretty nice illustration of the join types. (cool blog too...)

It's one of those "hard to explain with words" things I think. It is easier for me to visualize it.

tony · 08-05-2008, 06:12 PM

I read that blog, but I've never seen that. it's easy with illustrations for me.

buggabill · 08-05-2008, 07:16 PM

I have read that blog for a little while now. I may not always agree with some of his conclusions, but it is at least interesting and intelligent.

I'm kind of anxious to see what his new site is going to be like. I HATE experts exchange.

delayedinsanity · 08-05-2008, 08:22 PM

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

buggabill · 08-05-2008, 09:39 PM

With MySQL, the "OUTER" keyword is optional. It's provided for ODBC compatibility and doesn't add any extra capabilities.
MySQL JOIN reference

xenon · 08-05-2008, 09:39 PM

All joins and implicitly OUTER.

Thanks for the link bugabill. I think every subject in association with images becomes a little more clear than plain text alone.

delayedinsanity · 08-05-2008, 11:48 PM

Quote:

With MySQL, the "OUTER" keyword is optional. It's provided for ODBC compatibility and doesn't add any extra capabilities.

Okay, so yes, if I want to make my queries compatible with other SQL databases, I want to include the OUTER keyword, correcto? Eventually I want to build, or incorporate an abstraction layer, and I want to make sure it migrates with as little fuss as possible. I'd get it done now, but my to do list is about a mile long and lost somewhere in my bedroom. Which isn't a mile long. Nonetheless, it's lost.
-m