SQL-SQL join example solution. (Left join, right join, full join, inner join, cross join, self join)

Recently, the company is recruiting people. My colleagues asked a few questions about database connection that I think the database can be applied ~
Now I want to write about their functions here.
Assume that the following table is used:

One is the voting master table, and the other is the voter information table ~ Record the IP address of the voter and the corresponding voting type. The left-right connection is actually the result of our joint query. Which table prevails ~
1: For example, right join or right outer join:

Take the voter table on the right as the standard. The record in the left table (votemaster) is displayed only when its ID exists in the right table (voter). For example, id 3.4.5.6 on the left is not displayed because the IDS do not have corresponding records in the right table!
2: Therefore, we can naturally understand left join or left Outer Join.

It can be seen that the ID on the right is displayed only when it exists. If there is no corresponding data on the right, use null instead!
3: Full join or full outer join. The data in both tables is displayed. The effect is the same as that in the previous example!
4: Inner join or join; It is the record that the returned field ID exists in both the table votemaster and voter.

5: Cross join (full join) with no where Condition
A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table 1 and Table 2 generate 6*3 = 18 Records)

Equivalent to select VM. ID, VM. votetitle, vt. IP from votemaster as Vm, voter as vt
6: Self-connection. Here I used an example from a previous power project (transformed)
See the following table:

This is a department table that stores departments and their upper-level departments, but all of them are placed in the same table. We assume that you need to use SQL to query the departments and their upper-level departments! How to do it,
Of course, the same can be done without self-connection:

We have achieved the expected goal! In this query, A subquery is used to query the names of the upper-level departments. If you use a self-connection, the structure is much clearer.

Is the function also completed? In addition to the self-connection, the left connection is also used, because the provincial power has no upper-level department, and he is the boss. If the internal connection is used, this record is filtered out because there is no higher-level department that matches the record.
Self-connection uses a lot of queries on the weight structure! Similar to the above table!

 

From: http://www.cnblogs.com/eflylab/archive/2007/06/25/794278.html