Inner join usage and instance details

Inner Join Operation
Combine the records in two tables as long as there is a consistent value in the public field.

Syntax
Select (write the field you want, preferably not use *) from Table1 a inner join Table2 B on A. field1 compopr B. field2
Common usage can be written as: Select (write the field you want, it is best not to use *) from Table1 a inner join Table2 B on A. field1 = B. field2

Description
Table1 and Table2 record the names of the combined tables.
Name of the joined field of field1 and field2. If they are not composed of numbers, these fields must be of the same data type and contain similar data, but they do not need to have the same name.
Compopr any relational comparison operator: "=," "<," ">," "<=," ">=," or "<> ."
The inner join operation can be used in the from clause .. This is the most common connection type. As long as there are consistent values in the public fields of the two tables, the internal join will combine the records in the two tables.
You can use inner join to select all employees in each department from the department table and employee table. Instead, you can use left join or right join to create an outer join and select all departments (even if some employees do not exist) or all employees (even if some have not been assigned to the Department ).

If you try to join fields that contain memo or OLE object data, an error occurs.

You can join any two numeric fields of the same type. For example, you can join the autonumber and long fields because they have similar types. However, fields of the single and double types cannot be connected.

The following example shows how to join a class table and a product table in the Class Identifier Field:

Select categoryname, productname

From categories inner join Products

On categories. categoryid = products. categoryid;

In the preceding example, the class identifier is a joined field, but it is not included in the query output because it is not included in the SELECT statement. In this example, to include a join field, include the field name in the SELECT statement, categories. categoryid.

You can also use the following syntax to link Multiple on clauses in a join statement:

Select fields
From Table1 inner join Table2
On table1.field1 compopr table2.field1 and
On table1.field2 compopr table2.field2) or
On table1.field3 compopr table2.field3)];

You can also use the following syntax to nest a join statement:

Select fields
From Table1 inner join
(Table2 inner join [(] table3
[Inner join [(] tablex [inner join...)]
On table3.field3 compopr tablex. fieldx)]
On table2.field2 compopr table3.field3)
On table1.field1 compopr table2.field2;

In an inner join, left join or right join can be nested, but inner join cannot be nested in left join or right join.

The syntax format for connecting the three data tables can be summarized:
Select (write the field you want, it is best not to use *) from (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number

Instance:
Select (write the field you want, preferably not use *) from (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel

The syntax format for connecting the five data tables can be summarized:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number) Inner join table 5 on member. field number = table 5. field number

Instance:

From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity) Inner join wedlock on member. wedlock = wedlock. wedlock

The syntax format for connecting the four data tables can be summarized:
Select (corresponding field to be displayed) from (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on table 1. field number = table 4. field number

Example 1: select the field to be displayed.) from (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity

Example 2: Select (corresponding field name to be displayed) from table 1 A inner join Table 2 B on A. ID = B. ID and A. Enter the field to be queried =? Inner join Table 3 C on B. Aid = C. Aid inner join Table 4 D on C. Bid = D. Bid