多表联查的几种方式

有如下两张表

mysql> select * from teacher;
+------+-----------+
| t_id | t_name    |
+------+-----------+
|    1 | 张雪峰    |
|    2 | 老子      |
|    3 | 墨子      |
|    4 | 谢逊      |
+------+-----------+
-- ----------------------
mysql> select * from course;
+------+-----------------------+------+
| c_id | c_name                | t_id |
+------+-----------------------+------+
|    1 | python全栈工程师      |    1 |
|    2 | Java全栈工程师        |    2 |
|    3 | linux工程师           |    3 |
|    4 | web                   |    2 |
+------+-----------------------+------+

先来介绍两种常用的
多表联查

mysql> select * from  teacher t,course c where  t.t_id = c.t_id;
+------+-----------+------+-----------------------+------+
| t_id | t_name    | c_id | c_name                | t_id |
+------+-----------+------+-----------------------+------+
|    1 | 张雪峰    |    1 | python全栈工程师      |    1 |
|    2 | 老子      |    2 | Java全栈工程师        |    2 |
|    2 | 老子      |    4 | web                   |    2 |
|    3 | 墨子      |    3 | linux工程师           |    3 |
+------+-----------+------+-----------------------+------+

左连接

保留左表全部数据,以右表填充左表,没有以null填充

mysql> select * from  teacher t left join course c on t.t_id = c.t_id;
+------+-----------+------+-----------------------+------+
| t_id | t_name    | c_id | c_name                | t_id |
+------+-----------+------+-----------------------+------+
|    1 | 张雪峰    |    1 | python全栈工程师      |    1 |
|    2 | 老子      |    2 | Java全栈工程师        |    2 |
|    3 | 墨子      |    3 | linux工程师           |    3 |
|    2 | 老子      |    4 | web                   |    2 |
|    4 | 谢逊      | NULL | NULL                  | NULL |
+------+-----------+------+-----------------------+------+
 
 
mysql> select * from  teacher t left join course c on t.t_id = c.t_id where c_id = '2';
+------+--------+------+---------------------+------+
| t_id | t_name | c_id | c_name              | t_id |
+------+--------+------+---------------------+------+
|    2 | 老子   |    2 | Java全栈工程师      |    2 |
+------+--------+------+---------------------+------+
 
 
mysql> select * from  teacher t left join course c on t.t_id = c.t_id and c_id = '2';
+------+-----------+------+---------------------+------+
| t_id | t_name    | c_id | c_name              | t_id |
+------+-----------+------+---------------------+------+
|    1 | 张雪峰    | NULL | NULL                | NULL |
|    2 | 老子      |    2 | Java全栈工程师      |    2 |
|    3 | 墨子      | NULL | NULL                | NULL |
|    4 | 谢逊      | NULL | NULL                | NULL |
+------+-----------+------+---------------------+------+

后面来不常用的
右连接 -- 左连接和右连接成为外连接

保留右表全部数据,以左表填充右表,没有以null填充 -- 其实就是把左连接的两个表的位置互换

mysql> select * from  teacher t right join course c on t.t_id = c.t_id;
+------+-----------+------+-----------------------+------+
| t_id | t_name    | c_id | c_name                | t_id |
+------+-----------+------+-----------------------+------+
|    1 | 张雪峰    |    1 | python全栈工程师      |    1 |
|    2 | 老子      |    2 | Java全栈工程师        |    2 |
|    2 | 老子      |    4 | web                   |    2 |
|    3 | 墨子      |    3 | linux工程师           |    3 |
+------+-----------+------+-----------------------+------+

里连接
只连接匹配的行

mysql> select * from  teacher t inner join course c on t.t_id = c.t_id;
+------+-----------+------+-----------------------+------+
| t_id | t_name    | c_id | c_name                | t_id |
+------+-----------+------+-----------------------+------+
|    1 | 张雪峰    |    1 | python全栈工程师      |    1 |
|    2 | 老子      |    2 | Java全栈工程师        |    2 |
|    2 | 老子      |    4 | web                   |    2 |
|    3 | 墨子      |    3 | linux工程师           |    3 |
+------+-----------+------+-----------------------+------+

交叉连接-- 然并卵 ,相当于mysql中不存在的 cross join

产生笛卡尔乘积,两个表的数据交叉匹配--得到的结果没有用

mysql> select * from  teacher,course;
+------+-----------+------+-----------------------+------+
| t_id | t_name    | c_id | c_name                | t_id |
+------+-----------+------+-----------------------+------+
|    1 | 张雪峰    |    1 | python全栈工程师      |    1 |
|    2 | 老子      |    1 | python全栈工程师      |    1 |
|    3 | 墨子      |    1 | python全栈工程师      |    1 |
|    4 | 谢逊      |    1 | python全栈工程师      |    1 |
|    1 | 张雪峰    |    2 | Java全栈工程师        |    2 |
|    2 | 老子      |    2 | Java全栈工程师        |    2 |
|    3 | 墨子      |    2 | Java全栈工程师        |    2 |
|    4 | 谢逊      |    2 | Java全栈工程师        |    2 |
|    1 | 张雪峰    |    3 | linux工程师           |    3 |
|    2 | 老子      |    3 | linux工程师           |    3 |
|    3 | 墨子      |    3 | linux工程师           |    3 |
|    4 | 谢逊      |    3 | linux工程师           |    3 |
|    1 | 张雪峰    |    4 | web                   |    2 |
|    2 | 老子      |    4 | web                   |    2 |
|    3 | 墨子      |    4 | web                   |    2 |
|    4 | 谢逊      |    4 | web                   |    2 |
+------+-----------+------+-----------------------+------+

全外连接-- full join
full join在mysql中并不存在,他是产生a表和b表的并集,没有匹配的则以null值取代。

在mysql中可以使用union 或union all 来实现(union all 会显示所有的数据,union去掉重复的数据)

mysql> select * from teacher1;
+------+-----------+
| t_id | t_name    |
+------+-----------+
|    1 | 张雪峰    |
|    2 | 老子      |
|    3 | 墨子      |
|    4 | 谢逊      |
|    5 | 林冲      |
|    6 | 杨枭      |
+------+-----------+

union和union all 内部的 SELECT 语句必须拥有相同数量的列，每条 SELECT 语句中的列的顺序必须相同，不然会混乱。

mysql> select * from ((select * from teacher) union all (select * from  teacher1))s;
+------+-----------+
| t_id | t_name    |
+------+-----------+
|    1 | 张雪峰    |
|    2 | 老子      |
|    3 | 墨子      |
|    4 | 谢逊      |
|    1 | 张雪峰    |
|    2 | 老子      |
|    3 | 墨子      |
|    4 | 谢逊      |
|    5 | 林冲      |
|    6 | 杨枭      |
+------+-----------+
-- union
mysql> select * from ((select * from teacher) union (select * from  teacher1))s;
+------+-----------+
| t_id | t_name    |
+------+-----------+
|    1 | 张雪峰    |
|    2 | 老子      |
|    3 | 墨子      |
|    4 | 谢逊      |
|    5 | 林冲      |
|    6 | 杨枭      |
+------+-----------+