The difference between Inner Join and Full join

Question

What is the difference between full join and inner join?

When I make a full join, I get 832 records and with the inner join I get 830 records.

Answer 1

NOTE All of this can be found on Wikipedia: Join (SQL) .

There are three types of OUTER connections:

• LEFT OUTER JOIN
• CORRECT ENTRY TOGETHER
• FULL BUILT-IN WORK

The OUTER keyword is optional in all implementations that follow the standard, so FULL JOIN matches FULL OUTER JOIN. (I left the word OUTER from SQL in the rest of this answer.)

Let's see what everyone does.

Consider the following two sets of input data:

  Set "A" Set "B" AA BB -------- -------- Item 1 Item 3 Item 2 Item 4 Item 3 Item 5 Item 4 Item 6 

Note that in there are some elements that are not in B, and vice versa.

Now, if we write an SQL statement like this, using a LEFT join:

 SELECT * FROM A LEFT JOIN B ON AA = BB 

You will get the following result (empty holes are actually NULL ):

  AA BB -------- -------- Item 1 Item 2 Item 3 Item 3 Item 4 Item 4 

Note that you will get all rows from AA, or rather, all rows from the left side of the join clause.

If you switch to using 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 

Note that you get all the rows from the right side of the join condition.

However, if you want all the lines of both, you will 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 pointed out in a comment, let me fill out other connection methods.

With INNER join:

 SELECT * FROM A INNER JOIN B ON AA = BB AA BB -------- -------- Item 3 Item 3 Item 4 Item 4 

With an INNER join, we get only rows that actually match, without holes due to the join.

The CROSS join creates a Cartesian product by matching each row from the first set with each 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 will not indicate which columns should match, as there is no corresponding match.

Finally, NATURAL join, in this syntax we do not specify which columns correspond, but correspond to the column names. In our far-fetched example, the column names do not match, but let's say for this specific example that the column names in both tables were XX, then we get the following result:

 SELECT * FROM A NATURAL JOIN B +----------+------- matches on the names, and then the data | | vv XX XX -------- -------- Item 3 Item 3 Item 4 Item 4 

As you can see, you get the same thing as INNER join, but you should not enter the matching part of the join clause.

Answer 2

A FULL OUTER JOIN is a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN .

(did that make sense?)

A good visual explanation describing joins (bottom left describes the complete outer join):

Link: codeproject

Answer 3

The difference is the behavior of unsurpassed strings.

For example, if table A has a row that does not have a match in table B in the field on which the join is defined, the inner join would drop the row altogether, while the full join would include the row, but with NULL values ​​for the fields of table B. Viceversa for incomparable rows of table B.

Answer 4

A full join will return rows from both tables, even if the other table does not have matching rows. A full join is like a right join and a left join at the same time. The inner join will only return rows that have at least 1 partner in another table.

Answer 5

In a fairly simple way, the main difference is:

INNER JOIN . Returns only matched rows. Therefore, unsurpassed lines are not included.

FULL JOIN . Returns the rows that exist in the right table, and not on the left, plus the rows that exist in the left table, and not on the right, outside the inner rows of the join.

Answer 6

An inner join will not cause NULL to appear in the target join field. If there is no corresponding entry, the original will not be in the table.

Answer 7

This means your tables go well together.

The Wiki page found here shows a good example of how they work.

Answer 8

Consider table A and table B

Table A - (key, name)

1, Data1 2, Data2 4, Data4 5, Data5

Table B - (Key, FKey, Name)

1, 1, DataA 2, 2, DataB 3, NULL, DataC 4, 4, DataD

The inner join will return

• 1, Data1, 1, 1, DataA
• 2, Data2, 2, 2, DataB
• 4, Data3, 4, 4, DataD

while a full outer join will return

• 1, Data1, 1, 1, DataA
• 2, Data2, 2, 2, DataB
• NULL, NULL, 3, NULL, DataC
• 4, Data4, 4, 4, DataD
• 5, Data5, NULL, NULL, NULL