Differences between left join, right join, and full join

This section describes table join, more specifically inner joins internal join.
Only two records matching each other are selected for the internal join. Therefore, sometimes the records we need are not included.
To better understand this concept, we will introduce two tables for demonstration. Party and MSP in the Scottish Parliament ).

Party (Code, name, leader)
Code: political partyCode
Name: name of a political party
LEADER: political party leader

MSP (name, party, Constituency)
Name: name of the member
Party: Member's political party code
Constituency: Constituency

Before introducing left join, right join, and full join, there is an important concept in the database, that is, null ).

A left join contains all records in the left table, even records that do not match in the right table.
Similarly, there is also a right join (right join) that contains all records in the right table or even records that do not match it in the left table.
Full join, as its name implies, selects all records from the left and right tables.

At this point, someone may ask, what is it: contains all records in the left table, or even records that do not match it in the right table.
OK. Let's look at an instance:

Select MSP. Name, party. Name
From MSP join Party on party = Code

This is the join we learned in the previous section (Note: This is also called inner join). The intention of this statement is to list the names of all members and their political parties.
You can execute this statement in http://sqlzoo.cn/4.htm to see what the result is.

Unfortunately, we found that the query results were missing two members: canavan MSP and Dennis. Why, because these two members do not belong to any
And political parties, that is, their political party field (Party) is null. So why can't we find it if it doesn't belong to any political party? This is because the null value is
Blame. Because the null value of the political party field in the member table cannot be found in the political party table for matching, that is
From MSP join Party on party = Code does not connect the record, but filters it out.
In this phrase, MSP is on the left of join, and all are called left tables. Party is on the right side of join. All Tables are called right tables.

OK. Now let's look at this sentence again. "It contains all the records in the left table or even the records in the right table that do not match it ",
It should be clear. Execute the following statement, and the two members without political parties will not be able to miss it.

Select MSP. Name, party. Name
From MSP left join Party on party = Code

For the right join, you can see the query:

Select MSP. Name, party. Name
From MSP right join Party on MSP. Party = party. Code

The results of this query list all Members and political parties, including those without members, but not those without members.

So what should we do if we want to include political parties without members and those without political parties? Right, full join ).

Select MSP. Name, party. Name
From MSP full join Party on MSP. Party = party. Code

**************************************** **************************************** *******

The second part.

**************************************** **************************************** ************

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. 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 of the returned field ID in the table votemaster and voter at the same time.

5: cross join (full join) cross join without the 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!

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/fpf_721521/archive/2010/04/14/5485262.aspx