SQL syntax: Inner join on, left join in, right join on detailed usage method

INNER JOIN (equivalent join) returns only rows that have the same join field in two tables

Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right table
Right join returns records that include all records in the right table and the junction fields in the left table

INNER JOIN Syntax:

INNER join joins two data table usage:

JOIN table 2 on table 1. Field number = Table 2. Field number

INNER join joins three data table usage:

JOIN JOIN table 3 on table 1. Field number = Table 3. Field number

INNER join joins four data table usage:

JOIN JOIN JOIN

Table 4 on Member. Field number = Table 4. Field number

INNER join joins five data table usage:

JOIN JOIN JOIN JOIN table 5 on Member. Field number = Table 5. Field number

Connection six data Tables usage: slightly, similar to the above join method, everybody extrapolate bar:)

Precautions:
In the process of entering letters, be sure to use the English half-width punctuation marks, between the words left half the space between the corners;
When you create a data table, if a table joins multiple tables, the fields in this table must be numeric data types, and the same fields in multiple tables must be primary keys and AutoNumber data types. Otherwise, it is difficult to join successfully.
Code nesting Quick Method: For example, if you want to connect five tables, just add a front and back bracket to the code that joins the four tables (the parentheses are appended to the end of the code), and then continue adding "INNER JOIN table name x on table 1 after the parentheses." Field number = table X. Field number " Code so that you can join the data table indefinitely:)
1. Theory
As long as the public fields of the two tables have matching values, the records in the two tables are grouped together.
Personal understanding: Find the intersection of two tables in a common field, and combine each table with the required records in a common field for traction.
Grammar

JOIN table2 on table1. Field1 compopr table2. Field2

the INNER JOIN operation contains the following sections:
Section Description

table1, table2 the name of the table to which the record is to be combined.
Field1,field2 The name of the field to join. If they are not numbers, these fields must have the same data type and contain homogeneous data, but they do not have to have the same name.
COMPOPR
any relational comparison operator: "=", "<", ">", "<=", ">=", or "<>".

Description
You can use the INNER JOIN operation in any FROM clause. This is the most common type of join. The Inner join combines the records in these tables as long as there is a matching value on the public field of the two tables.
You can use INNER JOIN for departments and Employees tables to select all employees in each department. Instead, you can create an outer join by using a LEFT JOIN or right JOIN operation to select all the parts (even if there are no employees assigned in some departments) or all employees (even if some employees are not assigned to any department).
If you attempt to join a field that contains Memo or OLE object data, an error occurs.
you can join any two similar types of numeric fields. For example, you can join both AutoNumber and long fields, because they are similar types. However, you cannot join single-precision and double-type fields.
The following example shows how to join Categories and the Products table through the CategoryID field:
SELECT CategoryName, ProductName
From Categories INNER joins products
On Categories.CategoryID = Products.CategoryID;
in the previous example, CategoryID is a joined field, but it is not included in the query output because it is not included in the SELECT statement. To include the joined field, include the field name in the SELECT statement, which in this case refers to Categories.CategoryID.
You can also link multiple on clauses in a JOIN statement, using the following syntax:

JOIN table2on table1. field1 compopr table2. field1 ANDON table1. field2 compopr table2. field2 ORON table1. field3 compopr table2.field3;

You can also nest JOIN statements with the following syntax:

 select fieldsfrom table1 INNER  join  Span style= "COLOR: #000000" > (table2 INNER  join   [(]table3[inner  join  [(]tablex [INNER] Span style= "COLOR: #008080" >join  ... )]on table3 . field3 compopr TableX. FIELDX)] On Table2 . field2 compopr table3. field3) On table1 . field1 compopr table2.field2; 

The

left join or RIGHT join can be nested within a INNER join, but the INNER join cannot be nested inside a left or right join Among

2. Action instance

aid anum
1 a20050111
2 a20050112
Span style= "font-size:14px" >3 a20050113
4 a20050114
5 a20050115

Table B records the following:
BID bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

The experiment is as follows:
1.left Join
The SQL statements are as follows:

Join Bon A. AID = B.bid

The result is as follows:
aid anum bID bname
Span style= "font-size:14px" >1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 null null
(the number of rows affected is 5 rows)
result description:
left join is based on the records of a table, a can be regarded as the left table, B can be regarded as the right table, and left join is based on left table.
in other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
b table records are not sufficient for the place are null.
2.right join
sql statements are as follows:

Join Bon A. AID = B.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(The number of rows affected is 5 rows)
Result Description:
Looking closely, you will find that the result of the left join is exactly the opposite, this time it is based on the right table (B), where a table is not enough to fill with null.

3.inner Join
The SQL statements are as follows:

Select from Ainnerjoin Bon A. AID = B.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
Result Description:
Obviously, only a.aid = B.bid records are shown here. This shows that inner join is not based on who, it only shows records that match the criteria. And the inner join can be used in conjunction with the where statement, such as: Select from A innerjoin B on a.aid = b.bid where b.bname= ' 2006032401 ' This will only put back one piece of data.

SQL syntax: Inner join on, left join in, right join on detailed usage method