In-depth introduction to SQL left join, right join, and full join

In-depth introduction to SQL left join, right join, and full join

We introduced 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 ). 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, =, <> 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