SQL syntax: Inner join on, left join in, right join on specific usage

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

Left join returns records containing all the records in the left table and the equivalent of the junction fields in the right table

Right join returns records that contain all the records in the right table and the same join fields in the left table

INNER JOIN Syntax:

INNER join joins two data tables using the method:

SELECT * FROM table 1 INNER JOIN table 2 on table 1. Field number = Table 2. Field number

INNER join joins three data tables using the method:

SELECT * 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 joins four data tables using the method:

SELECT * 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 joins five data tables using the method:

SELECT * 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) in NER JOIN table 5 on Member. Field number = Table 5. Field number

Connect six data tables using: Slightly, similar to the above join method, everyone extrapolate it:)

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 creating a data table, assuming that a table joins multiple tables, the fields in this table must be "number" data types, and the same field in multiple tables must be the primary key and the "self-active number" data type. Otherwise, it is very difficult to join successfully.
Code nesting High-speed method: For example, if you want to connect five tables, you simply add a front and back bracket to the code that joins the four tables (the parentheses are appended to the from, the parentheses are appended to the end of the code), and then continue to join the "INNER join table name x on table 1. Field number = Table X. Field number" Code, so you can join the data table indefinitely:)

1. Theory

The records in the two tables are combined by simply having a matching value for the public fields of the two tables.

Personal understanding: In a common field, two tables meet the required intersection, and each table meets the required records in a common field for traction combined together.

Grammar

SELECT * FROM table1 INNER joins table2 on table1. Field1 compopr table2. Field2

The INNER JOIN operation includes the following sections:

Part	Description
Table1, table2	The name of the table in which to combine the records.
Field1,field2	The name of the field to join. Assuming they are not numbers, the data types of the fields must be the same, and include the same data, but they do not have to have the same name.
Compopr	Whatever the relationship comparison operator: "=", "<", ">", "<=", ">=", or "<>".

Description

The ability to use the INNER JOIN operation in whatever from clause. This is the most frequently used join type. The Inner join combines the records in these tables only if there is a matching value on the public field of the two tables.

The ability to use INNER JOIN for departments and Employees tables to select all employees in each department. Instead, you can create an outer join through a LEFT JOIN or right JOIN operation to select all the parts (even if there are no employees assigned to them in some departments) or all employees (even if some employees are not assigned to whatever department).

Suppose you try to join a field that includes notes or OLE object data, an error occurs.

The ability to join no matter what two similar types of numeric fields. For example, you can join your own active number and long integer fields, because they are all 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 demo sample, 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, for example:

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 nest JOIN statements by, for example, the following syntax:

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;

The LEFT JOIN or right join can be nested within the INNER join, but the INNER join cannot be nested inside a left JOIN or a right join.

2. Operating example

Table A records such as the following:
aid               aNum
1                  a20050111
2                   a20050112
3                   a20050113
4                   a20050114
5                   a20050115

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

Experiments such as the following:
1.left Join

SQL statements such as the following:
SELECT * FROM A
Left JOIN B
On 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
5 a20050115 NULL NULL
(The number of rows affected is 5 rows)

Result Description :
The left join is based on the records of a table, a can be regarded as the right table, B can be regarded as the table, and left join is 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).
The low-record of table B is null.

2.right Join
SQL statements such as the following:
SELECT * FROM A
Right Join B
On 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
SQL statements such as the following:
SELECT * FROM A
Innerjoin B
On 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, this shows only the records of A.aid = B.bid. This shows that inner join is not based on who, it only shows records that match the criteria. There is also 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 ' that would just put back a piece of data

SQL syntax: Inner join on, left join in, right join on specific usage