What is the difference between Left, Right, Outer and Inner Joins?

Question

I am wondering how to differentiate all these different joins ...

Answer 1

The main difference between RIGHT OUTER join and LEFT OUTER join, as there name suggest, is the inclusion of non-matched rows. ... In short result of LEFT outer join is INNER JOIN + unmatched rows from LEFT table and RIGHT OUTER join is INNER JOIN + unmatched rows from the right-hand side table.

Answer 2

An INNER JOIN means that a row is selected as part of the resultset only if the qualifying criteria is met. It can be specified overtly such as

FROM table_a a INNER JOIN table_b b ON a.somecolumn = b.someothercolumn

or implied by the WHERE clause such as

FROM table_a a, table_b b WHERE a.somecolumn = b.someothercolumn

In either case, two rows from tables a and b contribute to a row from the resultset only if the relation criteria was met. If the JOIN clause is specified but not what type, INNER is assumed.

An OUTER JOIN can only be specified by an overt join. Two rows both contribute to the resultset if the criteria is met just as in an INNER JOIN, but additionally a row in one table with no corresponding row in the other might still contribute to the resultset even if the criteria is not met. Which table(s) contribute to the resultset is controlled by whether it's LEFT, RIGHT or FULL. If LEFT, the table to the left of the JOIN keyword contributes without a partner; if RIGHT, the table to the right of the JOIN keyword does so and if FULL, both can contribute without a match. To illustrate (n indicates NULL):

table_a (id, first_name)

1 Fred

3 Sally

5 Harold

table_b (id, last_name)

1 Jones

2 Smith

3 Baker

SELECT a.id, b.id, first_name, last_name FROM table_a a JOIN table_b b ON a.id = b.id

1 1 Fred Jones

3 3 Sally Baker

SELECT a.id, b.id, first_name, last_name FROM table_a a LEFT OUTER JOIN table_b b ON a.id = b.id

1 1 Fred Jones

3 3 Sally Baker

5 n Harold n

SELECT a.id, b.id, first_name, last_name FROM table_a a RIGHT OUTER JOIN table_b b ON a.id = b.id

1 1 Fred Jones

n 2 n Smith

3 3 Sally Baker

SELECT a.id, b.id, first_name, last_name FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id

1 1 Fred Jones

n 2 n Smith

3 3 Sally Baker

5 n Harold n