Specific operations
MySQL supports the join syntax for select and some update and delete cases, with specific grammatical details:
Table_references:
Table_reference [, Table_reference] ...
Table_reference:
Table_factor
| Join_table
Table_factor:
Tbl_name [[as] alias]
[{use| ignore| FORCE} INDEX (Key_list)]
| (table_references)
| {OJ table_reference left OUTER JOIN table_reference
On conditional_expr}
Join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| Table_reference Straight_join Table_factor
| Table_reference straight_join Table_factor on condition
| Table_reference left [OUTER] JOIN table_reference join_condition
| Table_reference NATURAL [Left [OUTER]] JOIN table_factor
| Table_reference right [OUTER] JOIN table_reference join_condition
| Table_reference NATURAL [Right [OUTER]] JOIN table_factor
Join_condition:
On conditional_expr | USING (column_list)
In addition to the commonly used two table joins, the SQL (MySQL) JOIN syntax supports multiple table joins. The basic syntax for multiple table joins is as follows:
... From table1 inner| Left| Right JOIN table2 on condition inner| Left| Right JOIN table3 on condition ...
Join multiple table joins enable you to get related data from multiple tables, and here are three raw data tables:
Article Article table:
Aid |
title |
content |
UID |
Tid |
1 |
Article 1 |
Article 1 text content ... |
1 |
1 |
2 |
Article 2 |
Article 2 text content ... |
1 |
2 |
3 |
Article 3 |
Article 3 text content ... |
2 |
1 |
5 |
Article 5 |
Article 5 text content ... |
4 |
1 |
User table:
UID |
username |
Email |
1 |
Admin |
Admin@5idev.com |
2 |
Xiao ming |
Xiao@163.com |
3 |
Jack |
Jack@gmail.com |
Type Article Types table:
Tid |
TypeName |
1 |
General articles |
2 |
Elite Articles |
3 |
Draft |
code is as follows |
copy code |
SELECT * from T1 left JOIN (T2, T3, T4) on (t2.a=t1.a and t3.b=t1.b and t4.c=t1.c) |