MySQL基础-表连接

交叉连接

交叉连接（CROSS JOIN）一般用来直接返回连接表的笛卡尔积。
交叉连接的语法格式如下：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
#或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句] 

注意，

笛卡尔积示例：第一种方式为官方指定写法，语义更加清晰；第二种写法默认为交叉连接，如果想指定为内连接和外连接，就需要显式指定。

mysql> SELECT * FROM stuinfo;
+-------+------+-----------+------+
| name  | age  | course_id | sex  |
+-------+------+-----------+------+
| Jack  |   18 |        23 | M    |
| Mike  |   19 |        23 | M    |
| Frank |   20 |        14 | M    |
| Cling |   19 |        14 | W    |
| Daney |   18 |        14 | W    |
| Judy  |   20 |        23 | W    |
| Xuan  |   21 |        30 | W    |
+-------+------+-----------+------+
6 rows in set (0.00 sec)

mysql> SELECT* FROM course ;
+---------+----------+------+
| subject | capacity | id   |
+---------+----------+------+
| English |       32 |   23 |
| Math    |       18 |   14 |
+---------+----------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM stuinfo CROSS JOIN course;
+-------+------+-----------+------+---------+----------+------+
| name  | age  | course_id | sex  | subject | capacity | id   |
+-------+------+-----------+------+---------+----------+------+
| Jack  |   18 |        23 | M    | English |       32 |   23 |
| Jack  |   18 |        23 | M    | Math    |       18 |   14 |
| Mike  |   19 |        23 | M    | English |       32 |   23 |
| Mike  |   19 |        23 | M    | Math    |       18 |   14 |
| Frank |   20 |        14 | M    | English |       32 |   23 |
| Frank |   20 |        14 | M    | Math    |       18 |   14 |
| Cling |   19 |        14 | W    | English |       32 |   23 |
| Cling |   19 |        14 | W    | Math    |       18 |   14 |
| Daney |   18 |        14 | W    | English |       32 |   23 |
| Daney |   18 |        14 | W    | Math    |       18 |   14 |
| Judy  |   20 |        23 | W    | English |       32 |   23 |
| Judy  |   20 |        23 | W    | Math    |       18 |   14 |
| Xuan  |   21 |        30 | W    | English |       32 |   23 |
| Xuan  |   21 |        30 | W    | Math    |       18 |   14 |
+-------+------+-----------+------+---------+----------+------+

可见，笛卡尔积中大多数条目都没有意义，所以通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询。

内连接

内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。
内连接的语法格式如下：

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN … ON 语法是官方的标准写法，而且 WHERE 子句在某些时候可能会影响查询的性能。

有时以不同的方式编写查询可以提高速度，但多数情况下使用 WHERE 和 ON 实现的连接在底层的查询计划是相同的，理论上没有任何性能区别。

另一种说法是：ON 子句对每次笛卡儿积的中间结果进行过滤，而 WHERE 子句对最终结果进行过滤。该说法未查证真伪，但这似乎为采取社区的语法规范提供了理由：由该说法可得在外连接时，使用 ON 而不是 WHERE 来设置连接条件将提高效率

mysql> SELECT * FROM stuinfo INNER JOIN course
    -> ON stuinfo.course_id=course.id;
+-------+------+-----------+------+---------+----------+------+
| name  | age  | course_id | sex  | subject | capacity | id   |
+-------+------+-----------+------+---------+----------+------+
| Jack  |   18 |        23 | M    | English |       32 |   23 |
| Mike  |   19 |        23 | M    | English |       32 |   23 |
| Frank |   20 |        14 | M    | Math    |       18 |   14 |
| Cling |   19 |        14 | W    | Math    |       18 |   14 |
| Daney |   18 |        14 | W    | Math    |       18 |   14 |
| Judy  |   20 |        23 | W    | English |       32 |   23 |
+-------+------+-----------+------+---------+----------+------+
6 rows in set (0.00 sec)

外连接

外连接分为左连接和右连接 。在内连接中我们发现，当 ON 子句中的连接条件不成立时，就不会输出相应结果。但有时，我们也想输出未满足条件的那些记录，比如在上表中，我们以 stuinfo 和 course 表中的 id 为连接条件，只输出了满足条件的内容；但现在我们想查看所有参加了选课的同学，同时学生和所选课程也要在同一行，怎么办呢？这时就需要用到外连接。下面我们使用 左外连接 来完成这个需求：

mysql> SELECT * FROM stuinfo LEFT OUTER JOIN course
    -> ON stuinfo.course_id=course.id;
+-------+------+-----------+------+---------+----------+------+
| name  | age  | course_id | sex  | subject | capacity | id   |
+-------+------+-----------+------+---------+----------+------+
| Jack  |   18 |        23 | M    | English |       32 |   23 |
| Mike  |   19 |        23 | M    | English |       32 |   23 |
| Judy  |   20 |        23 | W    | English |       32 |   23 |
| Frank |   20 |        14 | M    | Math    |       18 |   14 |
| Cling |   19 |        14 | W    | Math    |       18 |   14 |
| Daney |   18 |        14 | W    | Math    |       18 |   14 |
| Xuan  |   21 |        30 | W    | NULL    |     NULL | NULL |
+-------+------+-----------+------+---------+----------+------+

可见，Xuan 同学所选的课程号是不存在的。

外连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN 。以上为左连接示例，右连接同理不再赘述。另外，MySQL 不支持全外连接，Oracle 支持全外连接。

自连接

自连接是指在同一张表上的连接查询。

mysql> SELECT * FROM emp;
+------+-------+------+
| id   | name  | mgn  |
+------+-------+------+
|    1 | Han   | NULL |
|    2 | Jack  |    1 |
|    3 | Tom   |    1 |
|    4 | Cling |    2 |
|    5 | Black |    3 |
+------+-------+------+
5 rows in set (0.00 sec)

现在我们有这样一个需求：打印出职员名和职员名对应的上级：

mysql> SELECT worker.name AS "职员名",manager.name AS "上级名"
    -> FROM emp AS worker, emp AS manager
    -> WHERE worker.mgr=manager.id;
+-----------+-----------+
| 职员名     | 上级名     |
+-----------+-----------+
| Jack      | Han       |
| Tom       | Han       |
| Cling     | Jack      |
| Black     | Tom       |
+-----------+-----------+

注意，如上，自连接必须指定别名以区分不同实例！
同时可见，Han是总裁，没有上级员工，但现在我们也想让 Han 出现在上表中，咋办？使用左连接即可：

mysql> SELECT worker.name AS "职员名",manager.name AS "上级名"
    -> FROM emp AS worker LEFT JOIN emp AS manager
    -> ON worker.mgr=manager.id;
+-----------+-----------+
| 职员名     | 上级名     |
+-----------+-----------+
| Jack      | Han       |
| Tom       | Han       |
| Cling     | Jack      |
| Black     | Tom       |
| Han       | NULL      |
+-----------+-----------+