MySQL的JOIN（一）：用法
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接。这里描述先甩出一张用烂了的图，然后插入测试数据。
1 CREATE TABLE t_blog( 2 id INT PRIMARY KEY AUTO_INCREMENT, 3 title VARCHAR(50), 4 typeId INT 5 ); 6 SELECT * FROM t_blog; 7 +----+-------+--------+ 8 | id | title | typeId | 9 +----+-------+--------+ 10 | 1 | aaa | 1 | 11 | 2 | bbb | 2 | 12 | 3 | ccc | 3 | 13 | 4 | ddd | 4 | 14 | 5 | eee | 4 | 15 | 6 | fff | 3 | 16 | 7 | ggg | 2 | 17 | 8 | hhh | NULL | 18 | 9 | iii | NULL | 19 | 10 | jjj | NULL | 20 +----+-------+--------+ 21 -- 博客的类别 22 CREATE TABLE t_type( 23 id INT PRIMARY KEY AUTO_INCREMENT, 24 name VARCHAR(20) 25 ); 26 SELECT * FROM t_type; 27 +----+------------+ 28 | id | name | 29 +----+------------+ 30 | 1 | C++ | 31 | 2 | C | 32 | 3 | Java | 33 | 4 | C# | 34 | 5 | Javascript | 35 +----+------------+
笛卡尔积：CROSS JOIN
要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。下面的例子，t_blog有10条记录，t_type有5条记录，所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。
1 SELECT * FROM t_blog CROSS JOIN t_type; 2 SELECT * FROM t_blog INNER JOIN t_type; 3 SELECT * FROM t_blog,t_type; 4 SELECT * FROM t_blog NATURE JOIN t_type; 5 select * from t_blog NATURA join t_type; 6 +----+-------+--------+----+------------+ 7 | id | title | typeId | id | name | 8 +----+-------+--------+----+------------+ 9 | 1 | aaa | 1 | 1 | C++ | 10 | 1 | aaa | 1 | 2 | C | 11 | 1 | aaa | 1 | 3 | Java | 12 | 1 | aaa | 1 | 4 | C# | 13 | 1 | aaa | 1 | 5 | Javascript | 14 | 2 | bbb | 2 | 1 | C++ | 15 | 2 | bbb | 2 | 2 | C | 16 | 2 | bbb | 2 | 3 | Java | 17 | 2 | bbb | 2 | 4 | C# | 18 | 2 | bbb | 2 | 5 | Javascript | 19 | 3 | ccc | 3 | 1 | C++ | 20 | 3 | ccc | 3 | 2 | C | 21 | 3 | ccc | 3 | 3 | Java | 22 | 3 | ccc | 3 | 4 | C# | 23 | 3 | ccc | 3 | 5 | Javascript | 24 | 4 | ddd | 4 | 1 | C++ | 25 | 4 | ddd | 4 | 2 | C | 26 | 4 | ddd | 4 | 3 | Java | 27 | 4 | ddd | 4 | 4 | C# | 28 | 4 | ddd | 4 | 5 | Javascript | 29 | 5 | eee | 4 | 1 | C++ | 30 | 5 | eee | 4 | 2 | C | 31 | 5 | eee | 4 | 3 | Java | 32 | 5 | eee | 4 | 4 | C# | 33 | 5 | eee | 4 | 5 | Javascript | 34 | 6 | fff | 3 | 1 | C++ | 35 | 6 | fff | 3 | 2 | C | 36 | 6 | fff | 3 | 3 | Java | 37 | 6 | fff | 3 | 4 | C# | 38 | 6 | fff | 3 | 5 | Javascript | 39 | 7 | ggg | 2 | 1 | C++ | 40 | 7 | ggg | 2 | 2 | C | 41 | 7 | ggg | 2 | 3 | Java | 42 | 7 | ggg | 2 | 4 | C# | 43 | 7 | ggg | 2 | 5 | Javascript | 44 | 8 | hhh | NULL | 1 | C++ | 45 | 8 | hhh | NULL | 2 | C | 46 | 8 | hhh | NULL | 3 | Java | 47 | 8 | hhh | NULL | 4 | C# | 48 | 8 | hhh | NULL | 5 | Javascript | 49 | 9 | iii | NULL | 1 | C++ | 50 | 9 | iii | NULL | 2 | C | 51 | 9 | iii | NULL | 3 | Java | 52 | 9 | iii | NULL | 4 | C# | 53 | 9 | iii | NULL | 5 | Javascript | 54 | 10 | jjj | NULL | 1 | C++ | 55 | 10 | jjj | NULL | 2 | C | 56 | 10 | jjj | NULL | 3 | Java | 57 | 10 | jjj | NULL | 4 | C# | 58 | 10 | jjj | NULL | 5 | Javascript | 59 +----+-------+--------+----+------------+
内连接：INNER JOIN
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN,JOIN(省略INNER)四种写法。至于哪种好我会在MySQL的JOIN（二）：优化讲述。示例如下。
1 SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id; 2 SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id; 3 SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线 4 SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id; 5 6 +----+-------+--------+----+------+ 7 | id | title | typeId | id | name | 8 +----+-------+--------+----+------+ 9 | 1 | aaa | 1 | 1 | C++ | 10 | 2 | bbb | 2 | 2 | C | 11 | 7 | ggg | 2 | 2 | C | 12 | 3 | ccc | 3 | 3 | Java | 13 | 6 | fff | 3 | 3 | Java | 14 | 4 | ddd | 4 | 4 | C# | 15 | 5 | eee | 4 | 4 | C# | 16 +----+-------+--------+----+------+
左连接：LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录（见最后三条）。
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
+----+-------+--------+------+------+
| id | title | typeId | id | name |
+----+-------+--------+------+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
+----+-------+--------+------+------+
1 SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id; 2 3 +----+-------+--------+------+------+ 4 | id | title | typeId | id | name | 5 +----+-------+--------+------+------+ 6 | 1 | aaa | 1 | 1 | C++ | 7 | 2 | bbb | 2 | 2 | C | 8 | 7 | ggg | 2 | 2 | C | 9 | 3 | ccc | 3 | 3 | Java | 10 | 6 | fff | 3 | 3 | Java | 11 | 4 | ddd | 4 | 4 | C# | 12 | 5 | eee | 4 | 4 | C# | 13 | 8 | hhh | NULL | NULL | NULL | 14 | 9 | iii | NULL | NULL | NULL | 15 | 10 | jjj | NULL | NULL | NULL | 16 +----+-------+--------+------+------+
右连接：RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录（见最后一条）。
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id; +------+-------+--------+----+------------+ | id | title | typeId | id | name | +------+-------+--------+----+------------+ | 1 | aaa | 1 | 1 | C++ | | 2 | bbb | 2 | 2 | C | | 3 | ccc | 3 | 3 | Java | | 4 | ddd | 4 | 4 | C# | | 5 | eee | 4 | 4 | C# | | 6 | fff | 3 | 3 | Java | | 7 | ggg | 2 | 2 | C | | NULL | NULL | NULL | 5 | Javascript | +------+-------+--------+----+------------+
外连接：OUTER JOIN
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现。
1 SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id 2 UNION 3 SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id; 4 5 +------+-------+--------+------+------------+ 6 | id | title | typeId | id | name | 7 +------+-------+--------+------+------------+ 8 | 1 | aaa | 1 | 1 | C++ | 9 | 2 | bbb | 2 | 2 | C | 10 | 7 | ggg | 2 | 2 | C | 11 | 3 | ccc | 3 | 3 | Java | 12 | 6 | fff | 3 | 3 | Java | 13 | 4 | ddd | 4 | 4 | C# | 14 | 5 | eee | 4 | 4 | C# | 15 | 8 | hhh | NULL | NULL | NULL | 16 | 9 | iii | NULL | NULL | NULL | 17 | 10 | jjj | NULL | NULL | NULL | 18 | NULL | NULL | NULL | 5 | Javascript | 19 +------+-------+--------+------+------------+
USING子句
MySQL中连接SQL语句中，ON子句的语法格式为：table1.column_name = table2.column_name。当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)。
所以，USING的功能相当于ON，区别在于USING指定一个属性名用于连接两个表，而ON指定一个条件。另外，SELECT *时，USING会去除USING指定的列，而ON不会。实例如下。
1 SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId =t_type.id; 2 +----+-------+--------+----+------+ 3 | id | title | typeId | id | name | 4 +----+-------+--------+----+------+ 5 | 1 | aaa | 1 | 1 | C++ | 6 | 2 | bbb | 2 | 2 | C | 7 | 7 | ggg | 2 | 2 | C | 8 | 3 | ccc | 3 | 3 | Java | 9 | 6 | fff | 3 | 3 | Java | 10 | 4 | ddd | 4 | 4 | C# | 11 | 5 | eee | 4 | 4 | C# | 12 +----+-------+--------+----+------+ 13 14 15 SELECT * FROM t_blog INNER JOIN t_type USING(typeId); 16 ERROR 1054 (42S22): Unknown column 'typeId' in 'from clause' 17 SELECT * FROM t_blog INNER JOIN t_type USING(id); -- 应为t_blog的typeId与t_type的id不同名，无法用Using，这里用id代替下。 18 +----+-------+--------+------------+ 19 | id | title | typeId | name | 20 +----+-------+--------+------------+ 21 | 1 | aaa | 1 | C++ | 22 | 2 | bbb | 2 | C | 23 | 3 | ccc | 3 | Java | 24 | 4 | ddd | 4 | C# | 25 | 5 | eee | 4 | Javascript | 26 +----+-------+--------+------------+
自然连接：NATURE JOIN
自然连接就是USING子句的简化版，它找出两个表中相同的列作为连接条件进行连接。有左自然连接，右自然连接和普通自然连接之分。在t_blog和t_type示例中，两个表相同的列是id，所以会拿id作为连接条件。
另外千万分清下面三条语句的区别 。
自然连接:SELECT * FROM t_blog NATURAL JOIN t_type;
笛卡尔积:SELECT * FROM t_blog NATURA JOIN t_type;
笛卡尔积:SELECT * FROM t_blog NATURE JOIN t_type;
1 SELECT * FROM t_blog NATURAL JOIN t_type; 2 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog,t_type WHERE t_blog.id=t_type.id; 3 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type ON t_blog.id=t_type.id; 4 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type USING(id); 5 6 +----+-------+--------+------------+ 7 | id | title | typeId | name | 8 +----+-------+--------+------------+ 9 | 1 | aaa | 1 | C++ | 10 | 2 | bbb | 2 | C | 11 | 3 | ccc | 3 | Java | 12 | 4 | ddd | 4 | C# | 13 | 5 | eee | 4 | Javascript | 14 +----+-------+--------+------------+ 15 16 SELECT * FROM t_blog NATURAL LEFT JOIN t_type; 17 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type ON t_blog.id=t_type.id; 18 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type USING(id); 19 20 +----+-------+--------+------------+ 21 | id | title | typeId | name | 22 +----+-------+--------+------------+ 23 | 1 | aaa | 1 | C++ | 24 | 2 | bbb | 2 | C | 25 | 3 | ccc | 3 | Java | 26 | 4 | ddd | 4 | C# | 27 | 5 | eee | 4 | Javascript | 28 | 6 | fff | 3 | NULL | 29 | 7 | ggg | 2 | NULL | 30 | 8 | hhh | NULL | NULL | 31 | 9 | iii | NULL | NULL | 32 | 10 | jjj | NULL | NULL | 33 +----+-------+--------+------------+ 34 35 SELECT * FROM t_blog NATURAL RIGHT JOIN t_type; 36 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type ON t_blog.id=t_type.id; 37 SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type USING(id); 38 39 +----+------------+-------+--------+ 40 | id | name | title | typeId | 41 +----+------------+-------+--------+ 42 | 1 | C++ | aaa | 1 | 43 | 2 | C | bbb | 2 | 44 | 3 | Java | ccc | 3 | 45 | 4 | C# | ddd | 4 | 46 | 5 | Javascript | eee | 4 | 47 +----+------------+-------+--------+
补充
博客开头给出的第一张图除去讲了的内连接、左连接、右连接、外连接，还有一些特殊的韦恩图，这里补充一下。
1 SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id 2 WHERE t_type.id IS NULL; 3 +----+-------+--------+------+------+ 4 | id | title | typeId | id | name | 5 +----+-------+--------+------+------+ 6 | 8 | hhh | NULL | NULL | NULL | 7 | 9 | iii | NULL | NULL | NULL | 8 | 10 | jjj | NULL | NULL | NULL | 9 +----+-------+--------+------+------+ 10 SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id 11 WHERE t_blog.id IS NULL; 12 +------+-------+--------+----+------------+ 13 | id | title | typeId | id | name | 14 +------+-------+--------+----+------------+ 15 | NULL | NULL | NULL | 5 | Javascript | 16 +------+-------+--------+----+------------+ 17 SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id 18 WHERE t_type.id IS NULL 19 UNION 20 SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id 21 WHERE t_blog.id IS NULL; 22 +------+-------+--------+------+------------+ 23 | id | title | typeId | id | name | 24 +------+-------+--------+------+------------+ 25 | 8 | hhh | NULL | NULL | NULL | 26 | 9 | iii | NULL | NULL | NULL | 27 | 10 | jjj | NULL | NULL | NULL | 28 | NULL | NULL | NULL | 5 | Javascript | 29 +------+-------+--------+------+------------+
写完这篇博客发现有点“孔乙己：茴字的四种写法的感觉”，但还是有收获的。另外，等三面通知等的好急啊!!
引用
http://www.cnblogs.com/fudashi/p/6572101.html
http://blog.csdn.net/wjc19911118/article/details/9716391
http://blog.csdn.net/taylor_tao/article/details/7068511
更多精彩
