sql连接查询

简述

　　　　简单回顾并总结下不同的表连接语句有什么异同之处以及一些概念。

　　　　建库语句如下

DROP DATABASE IF EXISTS `demo`;
CREATE DATABASE `demo`;
USE `demo`;
/*部门表*/
CREATE TABLE department (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50)
);
INSERT INTO department VALUES(DEFAULT, '财务'),(DEFAULT, '客服'),(DEFAULT, '技术');
/*雇员表*/
CREATE TABLE employee (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50),
    `department_id` INT,
    CONSTRAINT fk_employee_department FOREIGN KEY (id) REFERENCES department(id)
);
INSERT INTO employee VALUES(DEFAULT, '张三', 1),(DEFAULT, '李四', 2),(DEFAULT, '王五', 2);

 　　　　由上代码可知，分别创建了部门表和雇员表，雇员表和部门表是多对一的关系，技术部没有雇员。

---

内连接

　　　　语法：inner join ... on ... inner可以省略。

　　　　概念：内连接查询的结果是从两个或两个以上的表的组合中挑选出符合连接条件的数据，如果数据无法满足连接条件则将其忽略。在内连接查询中，参与连接的表的地位是平等的。

　　　　例：现在要查询员工和其所在的部门，sql语句如下

SELECT e.id, e.`name`, d.`name` '部门' FROM employee e JOIN department d ON e.`department_id`=d.`id`

　　　　结果如下：

　　　　

　　　　可以看出，部门一列只是将有员工的部门查询了出来，而没有员工的技术部则没有查询出来。

外连接

　　　　概念：上文提到：在内连接查询中，参与连接的表的地位是平等的。而在外连接中参与连接的表有主从之分。以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；对那些不符合连接条件的列，将被填上空值后再返回到结果集中。

左外连接

　　　　语法：left  outer join ... on ... outer可以省略。

　　　　概念：左外连接查询的结果集包括左表中的所有行，而不仅仅是连接匹配的行。若左表的某行在右表中没有匹配行，则在相关联的结果集中右表的所有选择列均为空值。

　　　　例一：将上面内连接的语句改成左外连接

SELECT e.id, e.`name`, d.`name` '部门' FROM department d LEFT JOIN employee e ON e.`department_id`=d.`id`

　　　　查询结果如下图

　　　　

　　　　可以看出，没有员工的部门也被查询了出来，并且员工的id和姓名列被填上了空值。

　　　　例二：查询所有部门包含的员工数，没有员工的部门也要被查询出来。sql语句如下

SELECT d.`name`,COUNT(e.`id`) '部门人数' FROM department d LEFT JOIN employee e ON e.`department_id`=d.`id` GROUP BY e.`department_id`

　　　　结果如下

　　　　

　　　　可以看出，没有员工的技术部也被查询了出来，统计人数为0(ps：这个0和之前自动填充的null值不冲突，因为这里使用了聚合函数count()，结果集先填充了null,再进行count统计的)。

右外连接 

　　　　语法：right  outer join ... on ... outer可以省略。

　　　　概念：右外连接和左外连接类似，只是位置变了一下。结果集必须包含右表中的所有行，若右表中有的项在左表中没有，则以null值填充。

　　　　例一：将上面左外连接的语句改成右外连接，sql语句如下

SELECT d.`name`,COUNT(e.`id`) '部门人数' FROM employee e RIGHT JOIN department d ON e.`department_id`=d.`id` GROUP BY e.`department_id`

　　　　结果如下