Detailed Explanation of SQL Statement Left JOIN Splicing Table

catalogue1. Grammar2

Left JOIN is a table join method in database operations that returns all records in the left table and matching records in the right table that meet the specified join criteria. If there is no matching data in the table on the right, the corresponding column will be filled with NULL values. Left JOIN is also known as Left Outer JOIN.

1. Grammar

SELECTcolumn_ name(s)FROMtable1LEFTJOINtable2ONtable1.column_ name=table2.column_ name;

2. Explanation

According to certain rules, concatenate table table1 and table table12. Taking the student management system as an example, the database tables of the student management system are as follows

Class Schedule Card:

Student Table

Gradesheet

Display all grades of all students

SELECTs.id,s.name,sc.subject,t.scoreFROMtranscriptAStLEFTJOINstudentASsONt.student_ id=s.idLEFTJOINscheduleASscONt.schedule_ id=sc.id;

The results are as follows:

3. The difference between LeftJOIN, RightJOIN, and INNER JOIN

First of all, note that sqlite does not support RIGHTJOIN

• Leftjoin returns all records in the left table and records with equal joined fields in the right table
• Rightjoin returns all records in the right table and records with equal joined fields in the left table
• Innerjoin only returns rows in two tables where the joined fields are equal

For example:

Table A records as follows:

aIDaNum1a200501112a200501123a200501134a200501145a20050115

Table B records as follows:

bIDbName1200603240122006032402320060324034200603240482006032408

The leftjoinsql statement is as follows:

select*fromAleftjoinBonA.aID=B.bID

The results are as follows:

aIDaNumbIDbName1a20050111120060324012a20050112220060324023a20050113320060324034a20050114420060324045a20050115NULLNULL

(The number of rows affected is 5)
Result description:
Leftjoin is based on the records of Table A, where A can be viewed as the left table and B as the right table. Leftjoin is based on the left table
In other words, all records in the left table (A) will be represented, while the right table (B) will only display records that meet the search criteria (in the example: A.aID=B.bID)
The areas with insufficient records in Table B are all NULL

The rightjoinsql statement is as follows:

select*fromArightjoinBonA.aID=B.bID

The results are as follows:

aIDaNumbIDbName1a20050111120060324012a20050112220060324023a20050113320060324034a2005011442006032404NULLNULL82006032408

(The number of rows affected is 5)
Result description:
Upon closer observation, it can be observed that, contrary to the results of leftjoin, this time it is based on the right table (B), and the areas where Table A is insufficient are filled with NULL

3.innerjoin  The SQL statement is as follows:

select*fromAinnerjoinBonA.aID=B.bID

The results are as follows:

aIDaNumbIDbName1a20050111120060324012a20050112220060324023a20050113320060324034a2005011442006032404

Result description:
Obviously, only records with A.aID=B.bID are displayed here. This indicates that the innerjoin is not based on who, it only displays records that meet the criteria

Note:

The leftJOIN operation is used to combine records from the source table in any from clause. Use the leftJOIN operation to create a left outer join. The left outer join will contain all records in the two tables starting from the first (left), even if there are no records with matching values in the second (right) table.

Syntax: FROMtable1LEFTJOINtable2ONtable1. field1compoprtable2. field2

explain:

The table1 and table2 parameters are used to specify the name of the table to combine records with.
The field1 and field2 parameters specify the name of the joined field. And these fields must have the same data type and contain the same type of data, but they do not need to have the same name.
The compatr parameter specifies the relationship comparison operator:"=& quot;& ldquo;& lt;& rdquo;& ldquo;& gt;& rdquo;& ldquo;& lt;=& rdquo;& ldquo;& gt;=& rdquo; Or&quo& lt;& gt;& rdquo;
If you want to join fields containing Memo or OLEObject data types in the INNERJOIN operation, an error will occur

4. Let's take another example to deepen our impression

Original table: Personnel list and purchase order are as follows

List all people and their order quantity - (if any).
You can use the following SELECT statement:

SELECTPersons.LastName,Persons.FirstName,Orders.OrderNoFROMPersonsLEFTJOINOrdersONPersons.id_ P=Orders.id_ PORDERBYPersons.LastName

The leftJOIN keyword returns all rows from the left table (Persons), even if there are no matching rows in the right table (Orders).

That's all for this article on the detailed explanation of the SQL statement LEFTJOIN concatenation table. For more information on the LEFTJOIN concatenation table, please search for previous articles of Script Home or continue browsing the following related articles. We hope everyone can support Script Home more in the future!