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，如需转载请自行联系原作者
