2014-07-31
在What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? 给出了一个解释各种JOIN的图：
下面用示例解释一下它们在MySQL中的区别。
创建两个表
两个表分别命名为users和blogs。users表用来存储用户信息；blogs表用来存储用户写的博客，为了简化，列只有user_id和title，没有主键，content类似的字段也没有。
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `blogs` (
`user_id` int(11) NOT NULL,
`title` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
添加数据：
mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
| 1 | w |
| 2 | x |
| 3 | y |
| 4 | z |
+----+------+
mysql> SELECT * FROM blogs;
+---------+-------+
| user_id | title |
+---------+-------+
| 1 | r |
| 2 | s |
| 3 | t |
| 1 | q |
| 10 | p |
+---------+-------+
CROSS JOIN
CROSS JOIN就是对两个表求笛卡尔积（Cartesian product）：
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
CROSS JOIN blogs;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | 1 | r |
| 2 | x | 1 | r |
| 3 | y | 1 | r |
| 4 | z | 1 | r |
| 1 | w | 2 | s |
| 2 | x | 2 | s |
| 3 | y | 2 | s |
| 4 | z | 2 | s |
| 1 | w | 3 | t |
| 2 | x | 3 | t |
| 3 | y | 3 | t |
| 4 | z | 3 | t |
| 1 | w | 1 | q |
| 2 | x | 1 | q |
| 3 | y | 1 | q |
| 4 | z | 1 | q |
| 1 | w | 10 | p |
| 2 | x | 10 | p |
| 3 | y | 10 | p |
| 4 | z | 10 | p |
+----+------+---------+-------+
20 rows in set
也可以简写为：
SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users, blogs;
加上ON（或者WHERE）：
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
CROSS JOIN blogs
ON users.id = blogs.user_id;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | 1 | r |
| 1 | w | 1 | q |
| 2 | x | 2 | s |
| 3 | y | 3 | t |
+----+------+---------+-------+
4 rows in set
LEFT JOIN
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
LEFT JOIN blogs
ON users.id = blogs.user_id;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | 1 | r |
| 2 | x | 2 | s |
| 3 | y | 3 | t |
| 1 | w | 1 | q |
| 4 | z | NULL | NULL |
+----+------+---------+-------+
LEFT JOIN的过程如下（摘自《SQL编程风格》）：
(1) 构造两个表的CROSS JOIN，扫描结果集中的每一行。
(2) 如果对于那一行的谓词测试为TRUE，则保留它。
(3) 如果对于那一行的谓词测试为FALSE或UNKNOWN，则保留表users的列保持不变，将非保留表blogs的所有列都转化为NULL，然后删去重复的。
基本原则是保留表中的每一行在结果行中都至少出现一次。
users LEFT JOIN blogs意味着users中的每行都会在结果中出现（除非ON中还有其他的逻辑语句限制），blogs中符合要求的数据会出现。这里，users表称为保留表，blogs表称为非保留表。
LEFT JOIN 和 LEFT OUTER JOIN是一个意思。
LEFT ON + WHERE
在ON中可以添加多个条件语句进行过滤，例如：
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
LEFT JOIN blogs
ON users.id = blogs.user_id
AND blogs.user_id IS NULL;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | NULL | NULL |
| 2 | x | NULL | NULL |
| 3 | y | NULL | NULL |
| 4 | z | NULL | NULL |
+----+------+---------+-------+
4 rows in set
blogs.user_id IS NULL是的CROSS JOIN的结果据不符合要求，这也就导致了上面的运行结果。
而如果：
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
LEFT JOIN blogs
ON users.id = blogs.user_id
WHERE blogs.user_id IS NULL;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 4 | z | NULL | NULL |
+----+------+---------+-------+
1 row in set
可以看出WHERE是在LEFT ON生成结果后才进行的过滤。
RIGHT JOIN
就是LEFT JOIN的反转。
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM blogs
RIGHT JOIN users
ON users.id = blogs.user_id;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | 1 | r |
| 2 | x | 2 | s |
| 3 | y | 3 | t |
| 1 | w | 1 | q |
| 4 | z | NULL | NULL |
+----+------+---------+-------+
5 rows in set
INNER JOIN
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
INNER JOIN blogs;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | 1 | r |
| 2 | x | 1 | r |
| 3 | y | 1 | r |
| 4 | z | 1 | r |
| 1 | w | 2 | s |
| 2 | x | 2 | s |
| 3 | y | 2 | s |
| 4 | z | 2 | s |
| 1 | w | 3 | t |
| 2 | x | 3 | t |
| 3 | y | 3 | t |
| 4 | z | 3 | t |
| 1 | w | 1 | q |
| 2 | x | 1 | q |
| 3 | y | 1 | q |
| 4 | z | 1 | q |
| 1 | w | 10 | p |
| 2 | x | 10 | p |
| 3 | y | 10 | p |
| 4 | z | 10 | p |
+----+------+---------+-------+
20 rows in set
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
INNER JOIN blogs
ON users.id = blogs.user_id;
+----+------+---------+-------+
| id | name | user_id | title |
+----+------+---------+-------+
| 1 | w | 1 | r |
| 1 | w | 1 | q |
| 2 | x | 2 | s |
| 3 | y | 3 | t |
+----+------+---------+-------+
4 rows in set
由此可见INNER JOIN 和CROSS JOIN差不多，但是又有什么区别呢？先看下面这段话（来自官方文档）：
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
也就是说，在MySQL中JOIN, CROSS JOIN 和 INNER JOIN这三个是一个意思。但是要注意到它们和LEFT JOIN的不同，第(3)步要修改为：
(3) 如果对于那一行的谓词测试为FALSE或UNKNOWN，删去该行。
FULL OUTER JOIN
MySQL不支持FULL OUTER JOIN，但是可以使用UNION模拟：
mysql> SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
LEFT JOIN blogs
ON users.id = blogs.user_id
UNION
SELECT users.id, users.name, blogs.user_id, blogs.title
FROM users
RIGHT JOIN blogs
ON users.id = blogs.user_id;
+------+------+---------+-------+
| id | name | user_id | title |
+------+------+---------+-------+
| 1 | w | 1 | r |
| 2 | x | 2 | s |
| 3 | y | 3 | t |
| 1 | w | 1 | q |
| 4 | z | NULL | NULL |
| NULL | NULL | 10 | p |
+------+------+---------+-------+
6 rows in set
其他
如何查看表的创建语句：
SHOW CREATE TABLE <table name>;
参考
Full Outer Join in MySQL
13.2.8.2 JOIN Syntax
Join (SQL)
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
《SQL编程风格》 Joe Celko著