Simple SQL Left, right and full connection

In the previous section we introduced the table connection, or rather the inner joins inner join.

The inner joins only select records that match each other in two tables. So, this leads to sometimes the records we need are not included.

For a better understanding of this concept, we introduce two tables for demonstration. The party table and the Parliamentarians (MSP) of the Scottish Parliament.

Party (Code,name,leader)
Code: Party Codes
Name: Party names
Leader: Party leader

MSP (Name,party,constituency)
Name: member name
Party: Members of the Political parties code
Constituency: Constituency

Before introducing left JOIN, right and full connections, there is an important concept in the database to introduce, that is, null values.

Sometimes a table, or rather some field value, may appear null because the data does not know what the value is or does not exist at all.

A null value is not equivalent to a space in a string, nor is it 0 of a numeric type. Therefore, you cannot use =,<> to determine whether a field is a null value. Must have a dedicated phrase: Is NULL to select a record with a null value field, and in the same vein, you can select records that do not contain null values using is instead null.

For example, the following statement selects a party without a leader. (Don't be surprised that there are such parties in the Scottish Parliament)

SELECT code, name from party
WHERE leader is NULL

Another example: A member was expelled from the party to see who he was. (That is, the member's party is a null value)

SELECT name from MSP
WHERE is NULL

Well, let's get to the bottom of this and see what's called LEFT JOIN, right and full connection.

A LEFT join, which contains all of the records in the left table, or even records in the right table that do not match it.

In the same way, there is a right join of the same reason (the right-hand connection), which contains all the records in the right table or even the table in the left side that does not match it.
Full join, as the name suggests, will be selected for all records in the left and right tables.

In this case, one might ask, what exactly is called: a record that contains all the tables in the left table and even a record in the right table that does not match it.

Let's take a 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: Also called INNER join), which is meant to list the names of all the members and the political parties.

Unfortunately, we found that the results of this query were less than two members: Canavan MSP, Dennis. Why, since these two members do not belong to any political party, their party field is null. So why not belong to any political party to find out? This is because the null value is at mischief. Because the null value of the party field in the member's table could not find matching records in the political party table, that is, the from MSP JOIN party on Party=code did not connect the record, but filtered it out.

In this phrase, the MSP is on the left side of the join, all called the left table. Party on the right side of the join, all called Right table.

Now take a look at the phrase, "include all the records in the left table or even the table on the right that does not match it," the meaning should be clear. By executing the following statement, the two members without a political party could not be missed.

SELECT Msp.name, Party.name
From MSP left JOIN party on Party=code

About the right connection, look at this query to understand:

SELECT Msp.name, Party.name
From MSP right JOIN party on Msp.party=party.code

The results of this enquiry list all members and political parties, including political parties without members, but not members without political parties. What about a party that has no members, and a member without a political party? Yes, full join.

SELECT Msp.name, Party.name
From MSP full JOIN party on Msp.party=party.code