Outer Joins, Geek To English Translation, Please!

[Dimitri Vorontzov]

Can someone - (Larry?) - please explain in lay terms the esoteric Paragraph 1 from Chapter 7, sub-chapter "Outer Joins":

 

"Whereas an inner join returns records based upon making matches between two tables, an outer join will return records that are matched by both tables, and will return records that don’t match. In other words, an inner join is exclusive but an outer join is inclusive."

 

It's the second time I'm trying to go through this chapter, and the second time I stumble at that paragraph!

 

Would be grateful for help from knowledgeable person!

[HartleySan]

Here's the top-rated explanation on Stack Overflow:

http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join

 

An inner join returns ONLY the exact matches between two tables, whereas an outer join returns the exact matches PLUS any other records in one (or both) of the tables that didn't match something in the other table. (Please note the difference between a left/right outer join and a full outer join, and also note why left/right/full matters for an outer join, but not for an inner join).

[Dimitri Vorontzov]

Thank you, HartleySan.

 

Would it be possible to see an example of the difference between inner and outer joins, using the forum database from the book?

 

Motivated by my curiosity, I tried to run the queries from the "Outer Joins" sub-chapter, replacing left and right joins with inner joins, and they return exactly the same results, whichever type of join, right, left, or inner, is used!

[Antonio Conte]

Inner joins are exclusive. Outer joins are inclusive. That's the most important. A join and Inner Join is the same.

 

Here's an example on OUTER JOIN:

 

Users: (user_id, username)

1, Antonio Conte

2, Dimitri Vorontzov

 

Posts: (post_id, user_id, content)

5345, 2, "Thank you, HartleySan....."

5453, 1, "Inner joins are exlusive...."

5335, 10, "Love this forum

 

With Inner joins/joins, the post with ID 5335 won't be included (exlusive - because user_id 10 does not exist). With Outer joins, post 5445 will be included with null as user_id and username. (inclusive)

 

Edit: Corrected misinformation.

Edited December 7, 2012 by Antonio Conte

[Dimitri Vorontzov]

Antonio, you're saying that INNER JOIN and LEFT JOIN are the same - however, according to Larry's book, and to the StackOverflow post mentioned above by HartleySan, there's a difference.

 

So, which is true?

 

If my understanding is correct, inner join will return only the matching results; left join will return all the results from the "left" table, and those results from the "right" table that match the results from the "left" table, populating the rest of the results on the right with NULL value. Right join, if I'm understanding correctly, would do the opposite: get all the results from the "right" table with missing results on the "left" populated by NULL.

 

Could the respected members of this Forum please confirm whether I get this right, or am missing something?

[Antonio Conte]

Sorry for misinforming you. Good example of source credibility here. When a forum user like me tells you one thing and Stack Overflow and Larry another, listen to the big guns. Always be critical.

 

The examples and how INNER/OUTER joins work should be correct, though. Inner join and Join is also identical.

 

Sorry again. I thought I knew this stuff good enough to not confirm my answer by other souces. Clearly not.

[Dimitri Vorontzov]

Got it. Thanks, Antonio. No need to beat yourself up, the examples do work, and that's important!