Use a RIGHT JOIN operator to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.
Use a FULL JOIN operator to create a full outer join. Full outer joins include all of the records from both tables, even if there are no matching values for records in the other table.
It's simple enough. But here's a analogy--family trip: the Li family has two boys and the Huang two girls. A trip with the Li family plus the two Huang girls (+ sign here) will be a left outer join on kids; a trip with the Huang family plus the two Li boys (+ sign here) will be right outer join on kids; a full join will be the two families go together. An inner join for kids will be the two boys and the two girls. If only one family goes then no join is needed. That's all.
2 comments:
The analogy sucks!
Let's try again in another way. A join collects information from two or more tables, and compares values in specific columns. It's probably best explained in pseudo-codes (NOT optimized for performance):
INNER JOIN:
1) for each value from column1 table1;
2) search for the same value in column 2 table2;
3) if match found, include data from both tables;
4) else, skip this row
5) continue looping;
LEFT OUTER JOIN:
1) for each value from column1 table1;
2) search for the same value in column 2 table2;
3) if match found in table2, include data from both tables;
4) else, include data from table1 only;
5) continue looping;
RIGHT OUTER JOIN:
1) for each value from column2 table2;
2) search for the same value in column1 table1;
3) if match found in table1, include data from both tables;
4) else, include data from table2 only;
5) continue looping;
FULL OUTER JOIN:
1) for each value from either column1 table1 or column2 table2;
2) search for the matching value in the other table;
3) if found, join the data from both tables;
4) else, include the data from the one table;
Logically, Inner Join is the intersection and Full Outer Join the union.
Post a Comment