In addition to the usual two table joins, the SQL (MySQL) JOIN syntax also supports multiple table joins. The basic syntax for multiple table joins is as follows:
1 |
... FROM table1 INNER | LEFT | RIGHT JOIN table2 ON condition INNER | LEFT | RIGHT JOIN table3 ON condition ... |
Join multi-table joins implement getting related data from multiple tables, here are three raw data tables:
Article Article table:
aid |
title |
content |
uid |
tid |
1 |
article 1 |
article 1 body content ... |
1 |
1 |
2 |
Article 2 |
article 2 body content ... |
1 |
2 |
3 |
Article 3 |
article 3 body content ... |
2 |
1 |
5 |
Article 5 |
Article 5 body content ... |
4 |
1 |
User table:
UID |
username |
Email |
1 |
Admin |
[Email protected] |
2 |
Xiao ming |
[Email protected] |
3 |
Jack |
[Email protected] |
Type table of article types:
Tid |
TypeName |
1 |
General articles |
2 |
Essence article |
3 |
Draft |
MySQL INNER JOIN Multiple table
We use INNER JOIN to list data in three tables that have an association relationship:
1 |
SELECT article.aid,article.title, user .username,type.typename FROM article INNER JOIN user |
2 |
ON article.uid= user .uid INNER JOIN type ON article.tid=type.tid |
The results of the returned query are as follows:
Aid |
title |
username |
TypeName |
1 |
Article 1 |
Admin |
General articles |
2 |
Article 2 |
Admin |
Essence article |
3 |
Article 3 |
Xiao ming |
General articles |
MySQL Left JOIN multiple table
Use the left JOIN three table query:
1 |
SELECT article.aid,article.title, user .username,type.typename FROM article LEFT JOIN user |
2 |
ON article.uid= user .uid LEFT JOIN type ON article.tid=type.tid |
The results of the returned query are as follows:
Aid |
title |
username |
TypeName |
1 |
Article 1 |
Admin |
General articles |
2 |
Article 2 |
Admin |
Essence article |
3 |
Article 3 |
Xiao ming |
General articles |
4 |
Article 4 |
Null |
General articles |
MySQL right JOIN multi-table
Use right JOIN three table query:
1 |
SELECT article.aid,article.title, user .username,type.typename FROM article RIGHT JOIN user |
2 |
ON article.uid= user .uid RIGHT JOIN type ON article.tid=type.tid |
The results of the returned query are as follows:
Aid |
title |
username |
TypeName |
1 |
Article 1 |
Admin |
General articles |
2 |
Article 2 |
Admin |
Essence article |
3 |
Article 3 |
Xiao ming |
General articles |
Null |
Null |
Null |
Draft |
As you can see, in right join, just list all data from the last right join table.
Description
For MySQL Multi-table joins, you can also mix INNER, left, and right, and its return results are related to the order of the keywords and are interesting to test on your own.
MySQL join multi-table connection