SQL left join, right join, full join

Http://blog.csdn.net/snowqoo/archive/2009/01/05/3708717.aspx

In simple terms, the left join, right join, and full join queries in SQL only select matching records in the two tables. 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 ).Copy content to clipboard
Code:

Party (Code, name, leader)
code: Political Party Code
Name: political party name
leader: political party leader
MSP (name, party, Constituency)
Name: member name
party: Member's political party Code
constituency: constituency

before introducing left join, right join, and full join, there is an important concept to introduce in the database, that is, null ).
sometimes, some field values may be null in the table, this is because the data does not know the value or does not exist at all.
the null value is not equal to the space in the string, nor is it a numerical value of 0. Therefore, = cannot be used to determine whether a field value is null. A special phrase is required: Is null is used to select records with null fields. Similarly, records that do not contain null values can be selected using is not null.
for example, the following statement selects a political party without a leader. (Don't be surprised, the Scottish Parliament does have such a political party) copy the content to the clipboard
code:

Select Code, name from party
Where leader is null

Another example: a Member is expelled from the party to see who he is. (That is, the political party of the member is null)Copy content to clipboard
Code:

select name from MSP
where party is null

well, let's get down to the truth and see what left connection, right connection, and full connection are.
A left join (left join) contains all records in the left table, or even records that do not match in the right table.
likewise, right join (right join ), that is, it contains all records in the right table or even records that do not match in the left table.
as the name suggests, full join Selects all records in the left and right tables.
at this point, someone may ask: contains all records in the left table, or even records that do not match in the right table.
let's look at an example: copy the content to the clipboard
code:

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). This statement is intended to list the names of all members and their political parties.
unfortunately, two members are missing from the query results: canavan MSP and Dennis. Why, because these two members do not belong to any political party, 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 null values are strange. Because the null value of the political party field (Party) 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 filtered 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.
now let's take a look at this sentence, "contains all the records in the left table or even the records that do not match the records in the right table", which means you should understand it. Execute the following statement, and the two members without political parties will not be able to miss it. copy the content to the clipboard
code:

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

For the right join, you can see the query:Copy content to clipboard
Code:

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 ).Copy content to clipboard
Code:

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