mysql 内连接和外连接

基本概念

表连接分为内连接和外连接。前面我们学习的多表查询都是内连接
可以在select，update 和 delete 语句中使用 mysql 的 join 来联合多表查询

JOIN 按照功能大致分为如下三类

• inner join（内连接）：获取两个表中字段匹配关系的记录。
• left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• right join（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

内连接 inner join

基本语法

select 字段名… from 表名1 inner join 表名2 on 连接条件 and 连接条件…

说明：前面我们学习的都是内连接…inner join 相当于， on 相当于 where

代码示例

显示部门号为10的 雇员名,雇员工资，所在部门及部门的名字

//以前的方法
mysql> select ename,sal,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
+--------+---------+--------+------------+
| ename  | sal     | deptno | dname      |
+--------+---------+--------+------------+
| clark  | 2450.00 |     10 | accounting |
| king   | 5000.00 |     10 | accounting |
| miller | 1300.00 |     10 | accounting |
+--------+---------+--------+------------+
3 rows in set (0.00 sec)

//使用 inner join  如果不加 on 得到的结果就是笛卡尔集    join =  inner join
mysql> select ename,sal,emp.deptno,dname from emp inner join dept on emp.deptno = dept.deptno and emp.deptno = 10;
+--------+---------+--------+------------+
| ename  | sal     | deptno | dname      |
+--------+---------+--------+------------+
| clark  | 2450.00 |     10 | accounting |
| king   | 5000.00 |     10 | accounting |
| miller | 1300.00 |     10 | accounting |
+--------+---------+--------+------------+
3 rows in set (0.00 sec)

外连接

外连接分为左外连接和右外连接

• left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• right join（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

创建测试表

//创建表
create table stu(
id int,
name varchar(30));

create table exam(
id int,
grade int);

insert into stu values(1,'jack');
insert into stu values(2,'tom');
insert into stu values(3,'kity');
insert into stu values(4,'nono');

insert into exam values(1,56);
insert into exam values(2,76);
insert into exam values(11,100);

//查看表
mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from exam;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |   100 |
+------+-------+
3 rows in set (0.00 sec)

左外连接

基本语法

select 字段名… from 表名1 left join 表名2 on 连接条件 and 连接条件…

left join 与 join 有所不同，left join会读取左边数据表的全部数据，即便右边表无对应数据

查询所有学生的成绩，若没有成绩，也要显示个人信息

//正确写法  使用 left join on  注意stu和exam的左右顺序
mysql> select stu.id,name,grade from stu left join exam on stu.id=exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | jack |    56 |
|    2 | tom  |    76 |
|    3 | kity |  NULL |
|    4 | nono |  NULL |
+------+------+-------+
4 rows in set (0.00 sec)

//错误写法 使用内连接
mysql> select stu.id,name,grade from stu inner join exam on stu.id=exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | jack |    56 |
|    2 | tom  |    76 |
+------+------+-------+
2 rows in set (0.00 sec)

右外连接

基本语法

select 字段名… from 表名1 right join 表名2 on 连接条件 and 连接条件…

right join 与 join 有所不同，right join会读取右边数据表的全部数据，即便左边表无对应数据

查询所有学生的成绩，即使这个成绩没有对应的个人信息

//使用 right join on

mysql> select exam.id,name,grade from stu right join exam on stu.id=exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | jack |    56 |
|    2 | tom  |    76 |
|   11 | NULL |   100 |
+------+------+-------+
3 rows in set (0.00 sec)

//使用 left join on
mysql> select exam.id,name,grade from exam left join stu on stu.id=exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | jack |    56 |
|    2 | tom  |    76 |
|   11 | NULL |   100 |
+------+------+-------+
3 rows in set (0.00 sec)

综合练习

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

//使用左外连接实现
mysql> select dept.deptno,dname,ename from dept left join emp on dept.deptno=emp.deptno;
+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     10 | accounting | clark  |
|     10 | accounting | king   |
|     10 | accounting | miller |
|     20 | research   | smith  |
|     20 | research   | jones  |
|     20 | research   | scott  |
|     20 | research   | ford   |
|     30 | sales      | allen  |
|     30 | sales      | ward   |
|     30 | sales      | martin |
|     30 | sales      | blake  |
|     30 | sales      | iurner |
|     30 | sales      | james  |
|     40 | operations | NULL   |
+--------+------------+--------+
14 rows in set (0.00 sec)

//使用右外连接实现
mysql>  select dept.deptno,dname,ename from emp right join dept on dept.deptno=emp.deptno;
+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     10 | accounting | clark  |
|     10 | accounting | king   |
|     10 | accounting | miller |
|     20 | research   | smith  |
|     20 | research   | jones  |
|     20 | research   | scott  |
|     20 | research   | ford   |
|     30 | sales      | allen  |
|     30 | sales      | ward   |
|     30 | sales      | martin |
|     30 | sales      | blake  |
|     30 | sales      | iurner |
|     30 | sales      | james  |
|     40 | operations | NULL   |
+--------+------------+--------+
14 rows in set (0.00 sec)