1. 背景
* 多表联合查询是把不同表的记录到一起的一种方式
* 在SQL标准中规划的联合(join)大致分内连接，外连接，全连接。其中外连接又分左外连接，右外连接。
2. 内连接例子 (inner join) [ 员工 --> 部门 ]
* 查看员工表[ employees ]和部门表[ departments ]结构
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> desc employees;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | |
| sex | enum('M','F') | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| depart_id | bigint(20) | NO | | NULL | |
+
5 rows in set (0.00 sec)
mysql> desc departments;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | |
+
2 rows in set (0.00 sec)
|
* 查看员工表[ employees ]和部门表[ departments ]数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> select * from employees;
+
| id | name | sex | age | depart_id |
+
| 1 | tom | M | 25 | 1 |
| 2 | jak | F | 35 | 2 |
| 3 | lisea | M | 22 | 3 |
+
3 rows in set (0.00 sec)
mysql> select * from departments;
+
| id | name |
+
| 1 | dev |
| 2 | test |
| 3 | ops |
+
3 rows in set (0.00 sec)
|
* 查询并显示所有员工id, 姓名，姓别，年龄，所在部门 (方法一)
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select e.id id, e.name name, IF(e.sex = 'M', 'male', 'female') sex, e.age age, d.name
-> from employees e, departments d
-> where e.depart_id = d.id;
+----+-------+--------+-----+------+
| id | name | sex | age | name |
+----+-------+--------+-----+------+
| 1 | tom | male | 25 | dev |
| 2 | jak | female | 35 | test |
| 3 | lisea | male | 22 | ops |
+----+-------+--------+-----+------+
3 rows in set (0.03 sec)
|
* 查询并显示所有员工id, 姓名，姓别，年龄，所在部门 (方法二) [ 方法一等价于方法二 ]
inner join 可以省写成 join
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select e.id id, e.name name, IF(e.sex = 'M', 'male', 'female') sex, e.age age, d.name
-> from employees e inner join departments d
-> on e.depart_id = d.id;
+----+-------+--------+-----+------+
| id | name | sex | age | name |
+----+-------+--------+-----+------+
| 1 | tom | male | 25 | dev |
| 2 | jak | female | 35 | test |
| 3 | lisea | male | 22 | ops |
+----+-------+--------+-----+------+
3 rows in set (0.00 sec)
|
3. 外连接例子
左外连接 [ 以左表为基础，左表的全部数据，右表有的组合。右表没有的为null ]
右外连接 [ 以右表为基础，右表的全部数据，左表有的组合。左表没有的为null ]
* 查看a表和b表结构
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> desc a;
+
| Field | Type | Null | Key | Default | Extra |
+
| data | int(11) | YES | | NULL | |
+
1 row in set (0.00 sec)
mysql> desc b;
+
| Field | Type | Null | Key | Default | Extra |
+
| data | int(11) | YES | | NULL | |
+
1 row in set (0.00 sec)
|
* 查看a表和b表数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> select * from a;
+------+
| data |
+------+
| 1 |
| 2 |
| 4 |
+------+
3 rows in set (0.00 sec)
mysql> select * from b;
+------+
| data |
+------+
| 2 |
| 4 |
| 5 |
| 6 |
+------+
4 rows in set (0.01 sec)
|
* 左外连接查询 (left join) 以a表为基础，显示a表所有数据，b表有的组合，没有显示NULLL
left outer join 可以写成 left join
|
1
2
3
4
5
6
7
8
9
|
mysql> select * from a left outer join b on a.data = b.data;
+
| data | data |
+
| 2 | 2 |
| 4 | 4 |
| 1 | NULL |
+
3 rows in set (0.00 sec)
|
* 右外连接查询 以b表为基础，显示b表所有数据，a表有的组合，没有显示NULLL
right outer join 可以写成 right join
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select * from a right outer join b on a.data = b.data;
+
| data | data |
+
| 2 | 2 |
| 4 | 4 |
| NULL | 5 |
| NULL | 6 |
+
4 rows in set (0.00 sec)
|
* 完全(交叉)连接查询
没有 where 子句的交叉联接将产生联接所涉及的表的笛卡尔积。
第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
（a和b交叉连接产生3*4=12条记录）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> select * from a corss join b;
+
| data | data |
+
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
| 1 | 4 |
| 2 | 4 |
| 4 | 4 |
| 1 | 5 |
| 2 | 5 |
| 4 | 5 |
| 1 | 6 |
| 2 | 6 |
| 4 | 6 |
+
12 rows in set (0.00 sec)
|
4. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943732，如需转载请自行联系原作者
网友评论