what is the difference between inner and outer joins sql server

Question

I tried to search in the google. But everyone is explaining about inner join and when comes to outer join they are including full outer join /left/right. I just want to know only the difference between inner join and outer join.

Answer 1

An inner join is where the columns match in both tables involved in the join, and will only return results where that condition is true. An outer join will return results even when there is no match, and will return nulls when the join condition is not met.

Sql fiddle link

Answer 2

Inner Join

"Get all of the rows from both tables that match the join condition."

Outer Join

"Get all of the rows from the [left/right] table and join any rows that match the join condition from the other table."

Full Outer Join

"Get all of the rows from both tables and join any rows to each that match the join condition."

In the case of outer join if you say:

SELECT m.*, mo.*
FROM myTable m
LEFT OUTER JOIN myOtherTable mo ON m.Id = mo.myTableId

This will return all the rows from the left table (myTable) and any matching rows from myOtherTable. Any rows from myTable without a match will have the values of mo.* set to NULL.

In the case of inner join if you say:

SELECT m.*, mo.*
FROM myTable m
INNER JOIN myOtherTable mo ON m.Id = mo.myTableId

You will only get the rows that match in both tables, so for instance if you have a myTable record with Id 15 and no myOtherTable records with the myTableId of 15 then no rows will be returned for myTable row with an Id 15.

There is another point to recognize that if there are multiple matches multiple rows will be returned. So if you have 2 items in myOtherTable with a myTableId of 10 then the myTable row with Id of 10 will be duplicated, one row with the first record from myOtherTable joined, and one with the second record.