For more information, see SQL left join, right join, and full join (reprinted)

Only two matching records are selected for the internal join. As a result, 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 ).
Table party
-------------------
Code, Name

Only two matching records are selected for the internal join. As a result, 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 ).

Table party
-------------------
Code, Name, Leader
-------------------
Code: Political Party Code
Name: Name of a political party
Leader: political party Leader

Table 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 ). Sometimes, in a table, some field values may be null 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 or the value of the number type 0. Therefore, =, & lt;> 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)
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)
SELECT name FROM msp WHERE party IS NULL
Let's get down to the point and see what left connection, right connection, and full connection are.
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.
Let's look at an example:
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.
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 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 at odds. 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: "It contains all the records in the left table, or even the records that do not match it in the right table." The meaning 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
You can also write and fully connect the same effect.
SELECT msp. name, party. name FROM msp right join party ON msp. party = party. code
Union
SELECT msp. name, party. name FROM msp left join party ON party = code

 

Source Address: http://edu.codepub.com/2010/0410/21834.php