外连接查询

1、外连接使用概述

当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。

2、外连接数据查询语法形式如下

SELECT field1,field2,…,fieldn
    FROM tablename1 LEFT|RIGHT [OUTER] JOIN tablename2
    ON CONDITION

在上述语句中，参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，关键字OUTER JOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。

外连接查询可以分为以下二类：

3、左外连接

外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。

4、数据表准备

DROP TABLE IF EXISTS `t_department`;
CREATE TABLE IF NOT EXISTS `t_department` (
  `department_no` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `department_name` varchar(128) NOT NULL DEFAULT  ''  COMMENT '部门名称',
  PRIMARY KEY (`department_no`),
  UNIQUE KEY `department_name` (`department_name`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE IF NOT EXISTS `t_employee` (
  `id` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `department_no` INT unsigned NOT NULL DEFAULT  '0'  COMMENT '部门表id',
  `name` varchar(128) NOT NULL DEFAULT  ''  COMMENT '登录名',
  `position` varchar(128) NOT NULL DEFAULT  ''  COMMENT '岗位',
  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',
  PRIMARY KEY (`id`),
  INDEX `name` (`name`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
ALTER TABLE t_department AUTO_INCREMENT = 10000;
insert into t_department(`department_name`) values('软件部');
insert into t_department(`department_name`) values('测试部');
insert into t_department(`department_name`) values('采购部');
insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10000','张三','开发','13533265485');
insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10000','李四','开发','13533265486');
insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10001','张五','测试','13533265487');
insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10001','李六','测试','13533265488');
insert into t_employee(`department_no`,`name`,`position`,`phone`) values('0','孙七','测试','13533265489');

5、查询所有员工的姓名、岗位、手机号、部门信息，具体SQL语句如下

select e.name,e.position,e.phone,d.department_name from t_employee as e left join t_department as d on e.department_no=d.department_no;

+--------+----------+-------------+-----------------+
| name   | position | phone       | department_name |
+--------+----------+-------------+-----------------+
| 张三   | 开发     | 13533265485 | 软件部          |
| 李四   | 开发     | 13533265486 | 软件部          |
| 张五   | 测试     | 13533265487 | 测试部          |
| 李六   | 测试     | 13533265488 | 测试部          |
| 孙七   | 测试     | 13533265489 | NULL            |
+--------+----------+-------------+-----------------+

6、右外连接

外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。

7、查询所有部门的员工信息，具体SQL语句如下

select e.name,e.position,e.phone,d.department_name from t_employee as e right join t_department as d on e.department_no=d.department_no;

+--------+----------+-------------+-----------------+
| name   | position | phone       | department_name |
+--------+----------+-------------+-----------------+
| 李六   | 测试     | 13533265488 | 测试部          |
| 张五   | 测试     | 13533265487 | 测试部          |
| 李四   | 开发     | 13533265486 | 软件部          |
| 张三   | 开发     | 13533265485 | 软件部          |
| NULL   | NULL     | NULL        | 采购部          |
+--------+----------+-------------+-----------------+