SQL joins the join cases Urealyticum. (left connection, right connection, full connection, inner connection, cross connection, self-connection) recently the company is hiring, colleagues asked a few self-think database can be the candidate about the library connection problem, the answer is not ideal ~
Now write about their role here.
Assume the following table:
One for the voting main table, one for the voter information table ~ Record the voter IP and corresponding voting type, the left and right connection is actually the result of our joint query to which table is the ~
1: The right join, as in outer join:
We use the right voter table, then the records in the left table (Votemaster) will only be displayed if their ID is present in the right (voter), for example, the ID in the left is 3.4.5.6 because there are no corresponding records in the right table of these IDs, so it is not displayed!
2: So naturally we can understand left join the left join or the left outer join
Visible, now the ID in the right in the presence of the display, when there is no corresponding data in the right to use NULL instead!
3: Fully connected full join or complete outer JOIN, for the data in two tables come out, here to show the same effect!
4: INNER JOIN inner JOIN or join; it is a record that returns a field ID that exists in both tables Votemaster and voter
5: Cross-connect (fully connected) crosses join without a Where condition
A cross join without a WHERE clause will produce a 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 equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 6*3=18 Records)
Equivalent select Vm.id,vm.votetitle,vt.ip from Votemaster as vm,voter as VT
6: Self-connect. Here I use an example of a power project in my previous period (modified)
As the following table:
This is a department table, which stores the department and its superior departments, but all in the same table, we assume that now we need to use SQL to query out the departments and their superior departments! On how to do it,
Of course, the same can be done without self-connection, as follows:
We achieve the desired goal! In this query, a subquery is used to complete the query of the parent department name, and if self-connection is used, the structure feels much clearer.
Is not also completed the function, here in addition to using the self-connection, but also use the left connection, because the province of power without the superior department, he is the eldest, if the use of internal connection, will be the record filter out, because there is no match with his superior department.
Self-connection with more than the right-shaped structure of the query! Similar to the table above! Reference: http://www.cnblogs.com/eflylab/archive/2007/06/25/794278.html
