mysql内外连接_MySQL之多表查询（笛卡尔积查询、内连接、外连接(左外连接，右外连接)、union、union all ）...

测试数据

create table emp (id int,name char(10),sex char,dept_id int);

insert emp values(1,"大黄","m",1);

insert emp values(2,"老王","m",2);

insert emp values(3,"老李","w",30);

#一张表示部门表

#存在一些没有员工的的部门

create table dept (id int,name char(10));

insert dept values(1,"市场");

insert dept values(2,"财务");

insert dept values(3,"行政");

1. 笛卡尔积查询

# 笛卡尔积查询，

mysql> select *from dept,emp;

+------+--------+------+--------+------+---------+

| id | name | id | name | sex | dept_id |

+------+--------+------+--------+------+---------+

| 1 | 市场 | 1 | 大黄 | m | 1 |

| 2 | 财务 | 1 | 大黄 | m | 1 |

| 3 | 行政 | 1 | 大黄 | m | 1 |

| 1 | 市场 | 2 | 老王 | m | 2 |

| 2 | 财务 | 2 | 老王 | m | 2 |

| 3 | 行政 | 2 | 老王 | m | 2 |

| 1 | 市场 | 3 | 老李 | w | 30 |

| 2 | 财务 | 3 | 老李 | w | 30 |

| 3 | 行政 | 3 | 老李 | w | 30 |

+------+--------+------+--------+------+---------+

9 rows in set (0.00 sec)

# 改进版

mysql> select *from dept,emp where dept.id = dept_id;

+------+--------+------+--------+------+---------+

| id | name | id | name | sex | dept_id |

+------+--------+------+--------+------+---------+

| 1 | 市场 | 1 | 大黄 | m | 1 |

| 2 | 财务 | 2 | 老王 | m | 2 |

+------+--------+------+--------+------+---------+

总结：

笛卡尔积查询的结果，存在很多错误的数据。即数据关联关系错误

解决办法： select *from dept,emp where dept.id = dept_id;

同时笛卡尔积的结果，会产生重复的字段信息

解决办法： select 指定字段... from dept,emp where dept.id = dept_id;

2. 内连接

内连接查询本质上就是笛卡尔积查询

mysql> select *from dept join emp ;

+------+--------+------+--------+------+---------+

| id | name | id | name | sex | dept_id |

+------+--------+------+--------+------+---------+

| 1 | 市场 | 1 | 大黄 | m | 1 |

| 2 | 财务 | 1 | 大黄 | m | 1 |

| 3 | 行政 | 1 | 大黄 | m | 1 |

| 1 | 市场 | 2 | 老王 | m | 2 |

| 2 | 财务 | 2 | 老王 | m | 2 |

| 3 | 行政 | 2 | 老王 | m | 2 |

| 1 | 市场 | 3 | 老李 | w | 30 |

| 2 | 财务 | 3 | 老李 | w | 30 |

| 3 | 行政 | 3 | 老李 | w | 30 |

+------+--------+------+--------+------+---------+

9 rows in set (0.00 sec)

mysql> select *from dept join emp on dept.id=emp.dept_id;

+------+--------+------+--------+------+---------+

| id | name | id | name | sex | dept_id |

+------+--------+------+--------+------+---------+

| 1 | 市场 | 1 | 大黄 | m | 1 |

| 2 | 财务 | 2 | 老王 | m | 2 |

+------+--------+------+--------+------+---------+

2 rows in set (0.00 sec)

3. 外连接：左连接

左边的表无论是否能够匹配 都要完整显示,右边即使没有也要显示出来

# 需求：要查询所有员工以及所属的部门信息

mysql> select * from emp left join dept on dept_id= dept.id;

+------+--------+------+---------+------+--------+

| id | name | sex | dept_id | id | name |

+------+--------+------+---------+------+--------+

| 1 | 大黄 | m | 1 | 1 | 市场 |

| 2 | 老王 | m | 2 | 2 | 财务 |

| 3 | 老李 | w | 30 | NULL | NULL |

+------+--------+------+---------+------+--------+

3 rows in set (0.00 sec)

# 注意 在外连接查询中 不能使用where关键字，必须使用on 专门来做表的对应关系

4. 外连接：右连接

右边的表无论是否能够匹配 都要完整显示,左边即使没有也要显示出来

select *from dept full join emp on dept.id = emp.dept_id;

5. 外连接：全连接(不支持)

无论是否匹配成功，两边表的数据都要全显示

##mysql 不支持

select *from dept full join emp on dept.id = emp.dept_id;

# 可以转化一种思路，使用左连接 + 右连接

select *from dept left join emp on dept.id=emp.dept_id

union

select *from dept right join emp on dept.id=emp.dept_id;

6. union：联合两个表

过滤重复，即重复的数据不显示。同时必须保证 两个表的列数要相同

select * from emp

union

select * from emp;

mysql> select * from emp

-> union

-> select * from emp;

+------+--------+------+---------+

| id | name | sex | dept_id |

+------+--------+------+---------+

| 1 | 大黄 | m | 1 |

| 2 | 老王 | m | 2 |

| 3 | 老李 | w | 30 |

+------+--------+------+---------+

3 rows in set (0.00 sec)

7. union all ：

不过滤重复，即重复的数据可以显示。同时必须保证 两个表的列数要相同

select * from emp

union all

select * from emp;

mysql> select * from emp

-> union all

-> select * from emp;

+------+--------+------+---------+

| id | name | sex | dept_id |

+------+--------+------+---------+

| 1 | 大黄 | m | 1 |

| 2 | 老王 | m | 2 |

| 3 | 老李 | w | 30 |

| 1 | 大黄 | m | 1 |

| 2 | 老王 | m | 2 |

| 3 | 老李 | w | 30 |

+------+--------+------+---------+

6 rows in set (0.00 sec)

总结：

内连接表示，只显示匹配成功的记录。一般情况下，我们通常使用内连接

外连接表示，没有匹配成功的也要显示

练习：

测试数据

create table stu(id int primary key auto_increment,name char(10));

create table tea(id int primary key auto_increment,name char(10));

create table tsr(id int primary key auto_increment,t_id int,s_id int,

foreign key(s_id) references stu(id),

foreign key(t_id) references tea(id));

insert into stu values(null,"张三"),(null,"李四");

insert into tea values(null,"egon"),(null,"wer");

insert into tsr values(null,1,1),(null,1,2),(null,2,2);

需求：查出egon教过的学生

使用内连接：

mysql> select * from tea join tsr join stu on tea.id=tsr.t_id and tsr.s_id = stu.id where tea.name="egon";

+----+------+----+------+------+----+--------+

| id | name | id | t_id | s_id | id | name |

+----+------+----+------+------+----+--------+

| 1 | egon | 1 | 1 | 1 | 1 | 张三 |

| 1 | egon | 2 | 1 | 2 | 2 | 李四 |

+----+------+----+------+------+----+--------+

2 rows in set (0.00 sec)

mysql> select stu.name from tea join tsr join stu on tea.id=tsr.t_id and tsr.s_id = stu.id where tea.name="egon";

+--------+

| name |

+--------+

| 张三 |

| 李四 |

+--------+

2 rows in set (0.00 sec)

使用子查询：

# 先查出egon对应的id

select id from tea where name="egon";

# 在tsr表中 查询 egon 教过学生 的id

select s_id from tsr where t_id = (select id from tea where name="egon");

# 在学生表中查询出对应的id

select stu.name from stu where id in (select s_id from tsr where t_id = (select id from tea where name="egon"));

mysql> select * from tea where name="egon";

+----+------+

| id | name |

+----+------+

| 1 | egon |

+----+------+

1 row in set (0.00 sec)

mysql> select s_id from tsr where t_id = (select id from tea where name="egon");

+------+

| s_id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

mysql> select stu.name from stu where id in (select s_id from tsr where t_id = (select id from tea where name="egon"));

+--------+

| name |

+--------+

| 张三 |

| 李四 |

+--------+

2 rows in set (0.27 sec)

通常情况下，内连接能够查询出来的数据，使用子查询也能查询出来