理解MySQL及其他关系数据库中JOINs用法

这篇文章包括大部分JOINs出现的情况，如果你是高手精通MySQL，请跳过，本文仅适合对JOINs用法不熟练的选手。

“JOIN" 是标准SQL的关键字常用于查询两个或多个关联数据表指定的数据。JOIN的几种用法在实际开发过程中很容易造成混淆，程序员开发过程中总是在处理混淆，下面将通过简洁的介绍这些用法。

一个很简单的例子，学生（user）和课程登记(course)

例子表

'user' table:

id	name	course
1	Alice	1
2	Bob	1
3	Caroline	2
4	David	5
5	Emma	(NULL)

MySQL table creation code:

CREATE TABLE `user` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(30) NOT NULL,
	`course` smallint(5) unsigned DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

'course'表

 
 
id
name
1
HTML5
2
CSS3
3
JavaScript
4
PHP
5
MySQL
MySQL table creation code:

CREATE TABLE `course` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;
由于我们用InnoDB表和知道user.course 和 course.id 是关联 的，我们能指定一个外键关联：
ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;
重要：这个数据库是无效的。这个示例很好,但学生只能进入零或一个课程。真实系统需要克服这一限制,可能使用一个中间的注册表映射任意数量的学生任意数量的课程。
连接允许我们以多种方式查询这些数据。
INNER JOIN (or just JOIN)
INNER JOIN用的比较频繁。这种关联常常在用户和课程表匹配的产生一组记录，比如users在course中关联。
SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;
Result:
   
   
user.name
course.name
Alice
HTML5
Bob
HTML5
Carline
CSS3
David
MySQL
LEFT JOIN
假如我们获取所有学生及他们的course即使他们没有登记课程。A LEFT JOIN会产生一个记录集合匹配在左表(user)每一个实体，不管在右表(course)是否有匹配项。
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;
Result:
   
   
user.name
course.name
Alice
HTML5
Bob
HTML5
Carline
CSS3
David
MySQL
Emma
(NULL)
RIGHT JOIN
假如我们需要所有课程和学生的列表即使是没有人选择的课程？A RIGHT JOIN会生成一组记录匹配每一个右表(course)不管左表(user)中是否匹配：
SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;
Result:
   
   
user.name
course.name
Alice
HTML5
Bob
HTML5
Carline
CSS3
(NULL)
JavaScript
(NULL)
PHP
David
MySQL
RIGHT JOINs 会很少用到，因为你可以使用 a LEFT JOIN表达相同的结果。这个更加有效和快速数据库解析。
SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;
举一个右连接的例子，我们能够计算每一门课程有多少学生选择：
SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;
Result:
   
   
course.name
count()
HTML5
2
CSS3
1
JavaScript
0
PHP
0
MySQL
1
OUTER JOIN（也叫全连接 FULL OUTER JOIN）
我们最后的选择是OUTER JOIN 其返回两个表里所有匹配的记录。若没有匹配项，则missing的一边回显示NULL。
OUTER JOIN 比INNER, LEFT 或者RIGHT 少用，它在MySQL中没有实施。然而你能够用UNION of a LEFT 和RIGHT JOIN在这个限制下工作。
例如：
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id

UNION

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;
Result:
   
   
user.name
course.name
Alice
HTML5
Bob
HTML5
Carline
CSS3
David
MySQL
Emma
(NULL)
(NULL)
JavaScript
(NULL)
PHP
希望这篇文章能够帮助到你，理解JOINs并且写出更高效的SQL 查询。