Difference between FULL JOIN & INNER JOIN
What is the difference between a FULL JOIN and an INNER JOIN?
When I do a FULL JOIN, I get 832 records and with an INNER JOIN, I get 830 records.
Solution 1:
NOTE: All of these can be found on Wikipedia: Join (SQL).
There are three types of OUTER joins:
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
The keyword OUTER is optional in all implementations that follow the standard, so FULL JOIN is the same as FULL OUTER JOIN. (I've omitted the word OUTER from the SQL in the rest of this answer.)
Let's look at what each does.
Consider the following two input data sets:
Set "A" Set "B"
AA BB
-------- --------
Item 1 Item 3
Item 2 Item 4
Item 3 Item 5
Item 4 Item 6
Notice that there are some items in A that aren't in B, and vice versa.
Now, if we write an SQL statement like this, using LEFT join:
SELECT * FROM A LEFT JOIN B ON AA = BB
You'll get the following result (the empty holes are actually NULL marks):
AA BB
-------- --------
Item 1
Item 2
Item 3 Item 3
Item 4 Item 4
Notice that you'll get all the rows from AA, or rather, all the rows from the left part of the join clause.
If you switch to using a RIGHT join:
SELECT * FROM A RIGHT JOIN B ON AA = BB
AA BB
-------- --------
Item 3 Item 3
Item 4 Item 4
Item 5
Item 6
Notice that you get all the rows from the right part of the join clause.
However, if you want all the rows of both, you'll use a FULL join:
SELECT * FROM A FULL JOIN B ON AA = BB
AA BB
-------- --------
Item 1 <-----+
Item 2 |
Item 3 Item 3 |
Item 4 Item 4 |
Item 5 +--- empty holes are NULL's
Item 6 |
^ |
| |
+---------------------+
As suggested in a comment, let me complete the other different ways to join.
With INNER join:
SELECT * FROM A INNER JOIN B ON AA = BB
AA BB
-------- --------
Item 3 Item 3
Item 4 Item 4
With INNER join we only get the rows that actually match up, no holes because of joining.
A CROSS join produces a cartesian product, by matching up every row from the first set with every row from the second set:
SELECT * FROM A CROSS JOIN B
AA BB
-------- --------
Item 1 Item 3 ^
Item 1 Item 4 +--- first item from A, repeated for all items of B
Item 1 Item 5 |
Item 1 Item 6 v
Item 2 Item 3 ^
Item 2 Item 4 +--- second item from A, repeated for all items of B
Item 2 Item 5 |
Item 2 Item 6 v
Item 3 Item 3 ... and so on
Item 3 Item 4
Item 3 Item 5
Item 3 Item 6
Item 4 Item 3
Item 4 Item 4
Item 4 Item 5
Item 4 Item 6
Also note that we don't specify which columns that match, since there is no matching done.
Finally, NATURAL join, in this syntax we don't specify which columns that match, but matches on column names. In our contrived example, no column names are the same, but let's say for this specific example that the column names was XX in both tables, then we would get the following result:
SELECT * FROM A NATURAL JOIN B
+----------+------- matches on the names, and then the data
| |
v v
XX XX
-------- --------
Item 3 Item 3
Item 4 Item 4
As you can see, you get the same as a INNER join, but don't have to type out the match part of the join clause.
Solution 2:
A FULL OUTER JOIN is a union of the LEFT OUTER JOIN and RIGHT OUTER JOIN.
(did that make sense?)
Nice visual explanation describing joins (bottom left describes full outer join):
Credits go to C.L. Moffatt's blogpost on codeproject
Solution 3:
In a pretty simple way, the main difference is:
INNER JOIN - Returns only matched rows. Therefore, unmatched rows are not included.
FULL JOIN - Returns those rows that exist in the right table and not in the left, plus the rows that exist in the left table and not in the right, beyond the inner join rows.
Solution 4:
The difference is in the behaviour of unmatched rows.
For example, if table A has a row which doesn't have a correspondence in table B in the field onto which the join is defined, an inner join would omit the row altogether, while a full join would include the row, but with NULL values for the fields of table B. Viceversa for unmatched rows of table B.
Solution 5:
A full join will return rows from both tables even if there are no matching rows in the other table. A full join is like a right join and a left join at the same time. An inner join will only return rows which have at least 1 partner in the other table.