MySQL join multi-table connection

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	... FROMtable1 INNER|LEFT|RIGHT JOIN table2 ON condition INNER|LEFT|RIGHTJOIN table3 ONcondition ...

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	SELECTarticle.aid,article.title,user.username,type.typename FROM articleINNER JOINuser

2	ONarticle.uid=user.uid INNER JOIN type ONarticle.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	SELECTarticle.aid,article.title,user.username,type.typename FROM articleLEFT JOINuser

2	ONarticle.uid=user.uid LEFT JOIN type ONarticle.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	SELECTarticle.aid,article.title,user.username,type.typename FROM articleRIGHT JOINuser

2	ONarticle.uid=user.uid RIGHT JOIN type ONarticle.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