Original link: http://www.powerxing.com/sql-join/
In general, the use/difference of four joins can be described as:
The LEFT join returns all records from the table (shop), even if there are no matching rows in the right table (Sale_detail).
Right outer join, returns all records in the right table, even if no record matches it in the left table
Full outer join fully connected, returns all records in the left and right table
When there is at least one match in the table, the inner join returns rows. The keyword inner can be omitted.
You can see StackOverflow, difference between Inner join & full Join the problem, it is very clear, I will move the answer to this question.
Join
There are three types of OUTER
joins:
- Left OUTER JOIN
- Right OUTER JOIN
- Full OUTER JOIN
Keywords OUTER
are selectable, depending on the language, and they follow the standard in implementation, FULL JOIN
and therefore FULL OUTER JOIN
are the same.
These join statements are then described in a simplified set of data. Consider the following two datasets, noting that some elements are in a, not in B, and vice versa.
Set Set "B" AA BB -- ------ -------- 1 3 24 3 5 4 6
Left OUTER JOIN
Now execute the following SQL statement (left connection, OUTER join):
SELECT * from Left OUTER JOIN on = Bb
You will get the following result (blank element representation NULL
):
AA BB -- ------ -------- 1 2 3 3) 4 4
Left join (left OUTER JOIN) outputs all the results from the table on the right, and if there is a corresponding item in the table on theNULL
Therefore, if you want to find the data items that are in the AA (the table on the left) that are not in the BB (right table), you can use the following SQL statement:
SELECT * from Left OUTER JOIN on = BB WHERE is NULL
Right OUTER JOIN
If you use the right connection, the result will output all the data items in the BB and the corresponding matches in AA (note that you are now getting all the data items in the table to the right):
select * A right outer join B on AA = BB AA BB -- -------------- Item Span style= "color: #800000; Font-weight:bold; " >3 Item 3 item 4 Item 4 item 5 item 6
Full OUTER JOIN
If you want to get all the element items, you can use FULL JOIN
:
SELECT * fromA Full JOINB onAa=BB AA bb-------- --------Item1 <-----+Item2 |Item3Item3 |Item4Item4 |Item5 +---Empty holes is NULL ' sItem6 |^ || |+---------------------+
Again, note that the value of the missing data item is NULL
.
INNER JOIN
INNER JOIN
JOIN
as is the case, the INNER
general keyword can be omitted. INNER JOIN
only the matching element items will be returned, that is, the data item with the result will not be returned NULL
.
SELECT * from INNER JOIN on = BB AA bb -- ------ -------- 3 3 44
Cross JOIN
Finally, there is a CROSS JOIN
Cartesian product that will return each element in a to match the result of all elements in B, that is, the n*m combination.
SELECT * fromA Cross JOINB AA BB-------- --------Item1Item3 ^Item1Item4 +---the first element in a, matching all elements in BItem1Item5 |Item1Item6Vitem2Item3 ^Item2Item4 +---second element in a, matching all elements in BItem2Item5 |Item2Item6Vitem3Item3... andSo onItem3Item4Item3Item5Item3Item6Item4Item3Item4Item4Item4Item5Item4Item6
Graphical JOIN operation for SQL
There's also a diagram here that clearly illustrates each join operation. Suggested that the above content to browse one side, and then good looking at this picture, I believe that the operation of the join should be fully understood.
Graphical JOIN operation for SQL
Join syntax parsing for SQL (inner join, left JOIN, right join, full outer join difference)