连接查询

在上一章中我们学习了子查询的相关内容，涉及标量子查询，列子查询，行子查询，表子查询，不相关子查询和相关子查询等内容。我们之前一致用student_info 和 student_score来储存学生的基本信息和成绩信息，其实也可以将这两张表合成一张表

连接的概念：

在前面介绍的子查询可以在一个查询语句中涉及多个表，但是整个查询语句最终产生的结果集还是用来展示外层查询的结果，子查询的结果只是被用来当作中间结果来使用。我们在需要展示子查询的结果时该怎么办呢？。这里我们就要学习连接查询这个查询方法。

首先我们先创建两个表：

mysql> create table t2(m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1, 'a'), (2, 'b'), (3, 'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(2, 'b'), (3, 'c'), (4, 'd');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.01 sec)

mysql> select * from t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

两个表连接的本质就是把一个表中的记录与另一个表中的记录两两结合，将组合后的记录加入到最后的结果集。其语法也很简单，只要在from语句后面跟多个用逗号隔开的表名就好了。

比如把t1和t2连接起来：
mysql> select * from t1,t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    2 | b    |
|    2 | b    |    2 | b    |
|    1 | a    |    2 | b    |
|    3 | c    |    3 | c    |
|    2 | b    |    3 | c    |
|    1 | a    |    3 | c    |
|    3 | c    |    4 | d    |
|    2 | b    |    4 | d    |
|    1 | a    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)

这里连接的结果集中有9行记录。在没有任何过滤条件的情况下，两个表连接起来生成的结果集也被称为笛卡尔集，因为t1中有三条记录，t2中有三条记录，所以这两个表连接之后的笛卡尔积就有3*3=9条记录。

连接查询过滤条件：

我们在展示结果集的时候，不可能需要查询所有的记录，这时候就涉及到了过滤条件的设置。在连接查询中过滤条件分为两种，一种是单表条件的过滤，一种是多表条件的过滤。

单表条件的过滤比较简单，着重介绍多表之间的过滤。

先来看看这个例子：

mysql> select * from t1,t2 where t1.m1>1 and t1.m1=t2.m2 and t2.n2<'d';
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

我们来分析这个多表查询的查询执行过程。

1.首先确定第一个需要查询的表，这个表称为驱动表。这里假设使用t1作为驱动表，那么就需要在t1表中查找满足t1.m1>1的记录。

2.针对满足条件t1.m1>1的驱动表中的每条记录，都需要到t2表中查找匹配的记录。因为是根据t1表中的记录去查找t2表中的记录。所以t2表也可以称为被驱动表。满足t1.m1>1条件的驱动表中的记录总共有两条，这也就意味着需要查询2次t2

在上面两个步骤可以看出在两表连接查询中，驱动表只需要查询一次。而被驱动表可能会被查询多次。

内连接和外连接：

在来连接查询中使用where条件来限制条件，一旦是where条件所过滤的内容一律都不会在结果集中展示，这样呈现的连接称为内连接。上面的这个例子就是内连接。内连接的查询方式有很多，可以如同上面一样简单的用，隔开也可以想这样来查询：

select * from t1 join t2；

select * from t1 inner join t2；

select * from t1 cross join t2；

这里比较推荐的写法是第二种 inner join，语义明显。

外连接又是什么呢？
举一个实际情况，比如一个表中是学生信息表，另一个是学生成绩表。有些一小部分学生缺考，所以成绩表中没有他们的信息，但在使用连接查询的结果集中不仅需要展示考了的成绩还要展示缺考的信息，我们在where条件的过滤又只能使用number来过滤，成绩表中有一部分学生没有考，所以他们的number不在里面，简单的内连接无法展示所需要的结果集。所以这个时候涉及了外连接的使用。

语法如下：

select * from t1 left/right 【outer】join t2 on 过滤条件 【where 过滤条件】

outer可写可不写；

我们在on子句过滤条件：

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配on子句中过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

需要注意的是，这个on子句的特殊作用只有在外连接查询中才会得以体现。如果把on子句放到内连接中，mysql会把它和where子句一样对待，也就是说在内连接中on子句和where子句的作用是等价的。

在外连接中驱动表和被驱动表的区别显得格外突出。所以确定驱动表和被驱动表也是非常的重要。所以在外连接中又分为左连接和右连接，显而易见左连接就是在【outer】 join 左边的表是驱动表，在outer join右边的是被驱动表，右连接相反。

小结：

mysql> select * from t1 inner join t2 on t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
3 rows in set (0.01 sec)

mysql> select * from t1 right join t2 on t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)

多表连接：

不止可以连接两个表，也可连接多个表，具体实现就不展示了。不论是内连接还是外连接所产生的笛卡尔积肯定是一样的，无非就是过滤条件的影响结果集的呈现。在内连接中驱动表和被驱动表的关系不明显，可以相互交换位置，但外连接却不能。在执行连接查询时不是先生成笛卡尔积再过滤。

表的别名：

在连接查询中from语句后的表名可以接上as来定义别名，在这一句查询语句中都可以用这个别名。

自连接：

如果我们想在一个表中查询一些信息，我们就可以使用自连接。但自连接需要注意的就是需要给这个表名设置两个别名，不然就会报错。这里设置的两个别名相当于两个副本。可以看作两个数据相同的表，比如这样：

mysql> select * from t1 as table1, t2 as table2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    2 | b    |
|    2 | b    |    2 | b    |
|    1 | a    |    2 | b    |
|    3 | c    |    3 | c    |
|    2 | b    |    3 | c    |
|    1 | a    |    3 | c    |
|    3 | c    |    4 | d    |
|    2 | b    |    4 | d    |
|    1 | a    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)