Join syntax parsing for SQL (inner join, left JOIN, right join, full outer join difference)

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 JOINJOINas is the case, the INNER general keyword can be omitted. INNER JOINonly 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)