Connection query_difference between left connection, right connection, and full connection

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