sql语句中关于join的基本相关（一）

一直以来，对于sql都是并没有花费时间与精力在上面，所以对于sql语句，一般都是秉承着会使用基本的增删查改就可以了，但是最近同事遇到了一个sql优化的问题，搞了好久，虽然最终废了好大的劲搞定了，但是还是感觉到了sql基础的薄弱，这才打算把sql相关的重新学习一遍，而关于join，这是第一篇。

首先，先甩出来一张已经被用烂了的图，如下：


由图可知，join大致分为内连接，外连接，右连接，左连接，自然连接。

首先，建两张表，emp、dep：

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `empName` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `dep` (
  `id` int(11) NOT NULL,
  `deptName` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `locAdd` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

插入对应的数据：

insert into `emp` (`id`, `empName`, `deptId`) values('1','e1','1');
insert into `emp` (`id`, `empName`, `deptId`) values('2','e2','1');
insert into `emp` (`id`, `empName`, `deptId`) values('3','e3','1');
insert into `emp` (`id`, `empName`, `deptId`) values('4','e4','2');
insert into `emp` (`id`, `empName`, `deptId`) values('5','e5','2');
insert into `emp` (`id`, `empName`, `deptId`) values('6','e6','3');
insert into `emp` (`id`, `empName`, `deptId`) values('7','e7','4');
insert into `emp` (`id`, `empName`, `deptId`) values('8','e8','51');


insert into `dep` (`id`, `deptName`, `locAdd`) values('1','RD','11');
insert into `dep` (`id`, `deptName`, `locAdd`) values('2','HR','12');
insert into `dep` (`id`, `deptName`, `locAdd`) values('3','MK','13');
insert into `dep` (`id`, `deptName`, `locAdd`) values('4','MIS','14');
insert into `dep` (`id`, `deptName`, `locAdd`) values('5','FD','15');

笛卡尔积：CROSS JOIN：

要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。下面的例子，emp有8条记录，dep有5条记录，所有他们俩的笛卡尔积有40条记录。有五种产生笛卡尔积的方式如下：

    SELECT * FROM emp CROSS JOIN dep;
    SELECT * FROM emp INNER JOIN dep;
    SELECT * FROM emp ,dep;
    SELECT * FROM emp NATURE JOIN dep;
    SELECT * FROM emp NATURA JOIN dep;

查询结果如下：

id	empName	deptId	id	deptName	locAdd
1	e1	1	1	RD	11
1	e1	1	2	HR	12
1	e1	1	3	MK	13
1	e1	1	4	MIS	14
1	e1	1	5	FD	15
2	e2	1	1	RD	11
2	e2	1	2	HR	12
2	e2	1	3	MK	13
2	e2	1	4	MIS	14
2	e2	1	5	FD	15
3	e3	1	1	RD	11
3	e3	1	2	HR	12
3	e3	1	3	MK	13
3	e3	1	4	MIS	14
3	e3	1	5	FD	15
4	e4	2	1	RD	11
4	e4	2	2	HR	12
4	e4	2	3	MK	13
4	e4	2	4	MIS	14
4	e4	2	5	FD	15
5	e5	2	1	RD	11
5	e5	2	2	HR	12
5	e5	2	3	MK	13
5	e5	2	4	MIS	14
5	e5	2	5	FD	15
6	e6	3	1	RD	11
6	e6	3	2	HR	12
6	e6	3	3	MK	13
6	e6	3	4	MIS	14
6	e6	3	5	FD	15
7	e7	4	1	RD	11
7	e7	4	2	HR	12
7	e7	4	3	MK	13
7	e7	4	4	MIS	14
7	e7	4	5	FD	15
8	e8	51	1	RD	11
8	e8	51	2	HR	12
8	e8	51	3	MK	13
8	e8	51	4	MIS	14
8	e8	51	5	FD	15

内连接：INNER JOIN:

内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN,JOIN(省略INNER)四种写法,示例如下:

    SELECT * FROM emp INNER JOIN dep ON emp.`deptId`=dep.`id`;
    SELECT * FROM emp,dep WHERE emp.`deptId`=dep.`id`;
    SELECT * FROM emp STRAIGHT_JOIN dep ON emp.`deptId`=dep.`id`;
    SELECT * FROM emp JOIN dep ON emp.`deptId`=dep.`id`

查询结果如下：

id	empName	deptId	id	deptName	locAdd
1	e1	1	1	RD	11
2	e2	1	1	RD	11
3	e3	1	1	RD	11
4	e4	2	2	HR	12
5	e5	2	2	HR	12
6	e6	3	3	MK	13
7	e7	4	4	MIS	14

左连接：LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，写法如下：

 SELECT * FROM emp LEFT JOIN dep ON emp.`deptId`=dep.`id`;

查询结果如下,见最后一条：

id	empName	deptId	id	deptName	locAdd
1	e1	1	1	RD	11
2	e2	1	1	RD	11
3	e3	1	1	RD	11
4	e4	2	2	HR	12
5	e5	2	2	HR	12
6	e6	3	3	MK	13
7	e7	4	4	MIS	14
8	e8	51	NULL    NULL	NULL

右连接：RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录，写法如下：

  SELECT * FROM emp RIGHT JOIN dep ON emp.`deptId`=dep.`id`;

查询结果如下,见最后一条：

id	empName	deptId	id	deptName	locAdd
1	e1	1	1	RD	11
2	e2	1	1	RD	11
3	e3	1	1	RD	11
4	e4	2	2	HR	12
5	e5	2	2	HR	12
6	e6	3	3	MK	13
7	e7	4	4	MIS	14
NULL	NULL	NULL	5	FD	15

外连接：OUTER JOIN
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现，写法如下：

SELECT * FROM emp LEFT JOIN dep ON emp.`deptId`=dep.`id`  
 
 UNION 
 
 SELECT * FROM emp RIGHT JOIN dep ON emp.`deptId`=dep.`id`;

查询结果如下：

id	empName	deptId	id	deptName	locAdd
1	e1	1	1	RD	11
2	e2	1	1	RD	11
3	e3	1	1	RD	11
4	e4	2	2	HR	12
5	e5	2	2	HR	12
6	e6	3	3	MK	13
7	e7	4	4	MIS	14
8	e8	51	NULL	NULL	NULL
NULL	NULL	NULL	5	FD	15

自然连接：NATURE JOIN
自然连接就是找出两个表中相同的列作为连接条件进行连接，写法如下：

 SELECT * FROM emp NATURAL JOIN dep; 

查询结果如下：

id	empName	deptId	deptName	locAdd
1	e1	1	RD	11
2	e2	1	HR	12
3	e3	1	MK	13
4	e4	2	MIS	14
5	e5	2	FD	15

下一篇具体介绍一下，开始的时候那张图的几种具体的sql写法。