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:

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

INNER join joins three data table usage:

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 table usage:

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 table usage:

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

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

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

The INNER JOIN operation contains the following sections:

Part	Description
Table1, table2	The name of the table to which the records are 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:

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 with 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;

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

2. Operating example

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 statements are as follows:
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 Table A, a can be regarded as the right table, and B can be regarded as 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
The SQL statements are as follows:
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
The SQL statements are as follows:
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, 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 a single piece of data.

Reprinted from http://blog.csdn.net/steryzone/article/details/4997060/

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