SQL multi-table join query implementation statement

1. Theory

As long as the public fields of the two tables have matched values, the records in the two tables are combined.

My personal understanding: Use a common field to calculate the intersection of the two tables that meet the requirements, and combine the records that meet the requirements of each table with the common fields.

Syntax

Select * FROM table1 inner join table2 ON table1. field1 compopr table2. field2

The inner join operation includes the following parts:

Part	Description
Table1, table2	The name of the table in which the record is to be combined.
Field1, field2	The name of the field to be joined. If they are not numbers, these fields must have the same data type and contain similar data, but they do not have to have the same name.
Compopr	Comparison operators for any link: "=", "<", ">", "<=", "> =", or "<> ".

Description

The inner join operation can be used in any FROM clause. This is the most common connection type. As long as the public fields of the two tables have matched values, the Inner join will combine the records in these tables.

Inner join can be used for the Departments and Employees tables to select all Employees in each department. To select all parts (even if some departments are not assigned employees) or all employees (even if some employees are not assigned to any department ), you can use left join or right join to create an external JOIN.

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

You can join any two numeric fields of similar types. For example, automatic numbers and long integer fields can be joined because they are of similar type. However, single-precision and double-precision fields cannot be connected.

The following example shows how to use the CategoryID field to join the Categories and Products tables:

SELECT CategoryName, ProductName

FROM Categories inner join 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 a joined field, include the field name in the SELECT statement. In this example, Categories. CategoryID.

You can also link Multiple ON clauses in a JOIN statement. Use the following syntax:

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 JOIN statements:

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;

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

2. Operate instances

Table A records the following:
AID aNum
1 a20050111
2 a20050112
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 statement is as follows:
Select * from
Left join B
On A. aID = B. bID

The result is as follows:
AID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL
(The number of affected rows is 5)

Result description:
Left join is based on the records of table A. A can be seen as the left table, B can be seen as the right table, and left join is based on the left table.
In other words, the records in the left table (A) are all expressed, while the right table (B) only displays records that meet the search criteria (in this example:. aID = B. bID ).
All records in Table B are NULL.

2. right join
The SQL statement is as follows:
Select * from
Right join B
On A. aID = B. bID
The result is as follows:
AID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL 8 2006032408
(The number of affected rows is 5)
Result description:
After careful observation, we will find that the result of left join is exactly the opposite. This time, it is based on the right table (B) and is filled with NULL when table A is insufficient.

3. inner join
The SQL statement is as follows:
Select * from
Innerjoin B
On A. aID = B. bID

The result is 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. aID = B. bID record. this indicates that inner join is not based on WHO, and only displays records that meet the conditions. in addition, inner join can be used in combination with where statements, for example: select * from A innerjoin B on. aID = B. bID where B. bname = '000000'. In this way, only one data record is returned.

My personal summary: Install the above three rules on multiple tables to join a table