What’s the difference between
INNER JOIN and
OUTER JOIN ?
LEFT JOIN ,
RIGHT JOIN and
FULL JOIN mean?
Answer 1, authority 100%
You can also use this diagram to understand table joins:
Answer 2, authority 98%
Suppose you want to do a column join without duplicates, which is quite common:
Inner join of A and B: A intersects B, i.e. the interior of the intersection Venn diagrams .
Outer join A and B: A connects to B, i.e. the outer part of a connection in a Venn diagram.
Suppose you have two tables. Each consists of one column, with the following values:
A B - - 13 2 4 3 5 4 6
Note that (1,2) are unique to A, (3,4) are shared, and (5,6) are unique to B.
An inner join using one of the equivalent queries gives the intersection of two tables, that is, two rows common to each of them.
select * from a INNER JOIN b on a.a = b.b; select a. *, b. * from a, b where a.a = b.b; a | b - + - 3 | 3 4 | 4
Left Outer Join
The result of the left outer join is all rows of table A plus all rows of table B that match the rows of table A.
select * from a LEFT OUTER JOIN b on a.a = b.b; select a. *, b. * from a, b where a.a = b.b (+); a | b - + ----- 1 | null 2 | null 3 | 3 4 | 4
Right Outer Join
The result of the right outer join is all rows of table B plus all rows of table A that match the rows of table B.
select * from a RIGHT OUTER JOIN b on a.a = b.b; select a. *, b. * from a, b where a.a (+) = b.b; a | b ----- + ---- 3 | 3 4 | 4 null | 5 null | 6
Full Outer Join
The result of a full outer join is a join between tables A and B, i.e. all rows of A and all rows of B. If any element of table A does not have a match in table B, that part of B is empty, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b; a | b ----- + ----- 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5
Answer 3, authority 5%
INNER-inner join (intersection of sets) -selects common from sets
LEFT JOIN-left outer join-takes everything on the left and everything on the right, for which there is a match in the left set
Answer 4, authority 2%
What do the differences mean, and also why such names, for all types of table joins, can be clearly seen in the following graph:
[INNER] JOIN– internal
(LEFT | RIGHT) [OUTER] JOIN– outer, or right, or left
FULL [OUTER] JOIN– external, right and left, ie full
|INNER||JOIN||Returns records that match in both tables|
|OUTER||LEFT JOIN||Returns all records from the left table + records that match from the right|
|OUTER||RIGHT JOIN||Returns all records from the right table + records that match from the left|
|OUTER||FULL JOIN||Returns all records from both tables|