mysql-多表联查(实例)
多表查询
笛卡尔积查询
笛卡尔积查询:就是两张表相乘,若左边表有M条信息,右边表有N条信息,那么查询显示的信息总共为M*N条,这其中往往包含大量错误数据,需要用where+条件来过滤无用信息
# 笛卡尔积查询语句
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 |
+------+--------+------+--------+------+---------+
# 这里面有很多错误的信息,我们可以在后面+where 条件来过滤信息
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 inner join emp on dept_id = dept.id;
# inner 可以不写
select * from dept join emp on dept_id = dept.id;
# join替代了逗号,如果多个表,就加多join,
# on 替代了where,on也是过滤信息
+------+--------+------+--------+------+---------+
| id | name | id | name | sex | dept_id |
+------+--------+------+--------+------+---------+
| 1 | 市场 | 1 | 大黄 | m | 1 |
| 2 | 财务 | 2 | 老王 | m | 2 |
+------+--------+------+--------+------+---------+
左外连接查询
左边的表无论是否能匹配都要完整显示
右边的仅展示匹配上的记录
select * from dept left join emp on dept_id = dept.id;
+------+--------+------+--------+------+---------+
| id | name | id | name | sex | dept_id |
+------+--------+------+--------+------+---------+
| 1 | 市场 | 1 | 大黄 | m | 1 |
| 2 | 财务 | 2 | 老王 | m | 2 |
| 3 | 行政 | NULL | NULL | NULL | NULL |
+------+--------+------+--------+------+---------+
右外连接查询
右边的表无论是否能够匹配都要完整显示
左边的仅展示匹配上的记录
select * from dept right join emp on dept_id = dept.id;
+------+--------+------+--------+------+---------+
| id | name | id | name | sex | dept_id |
+------+--------+------+--------+------+---------+
| 1 | 市场 | 1 | 大黄 | m | 1 |
| 2 | 财务 | 2 | 老王 | m | 2 |
| NULL | NULL | 3 | 老李 | w | 30 |
+------+--------+------+--------+------+---------+
全外连接查询
无论是否匹配成功,两边表的数据都要全部显示
mysql 不支持全外连接,所以我们得用到union来拼接左连接和右连接,来组合成一个全外连接查询表
# 需求:查询所有员工与所有部门的对应关系
select * from emp full join dept on dept_id = dept.id;
# 注意:mysql不支持全外连接
select * from emp left join dept on dept_id = dept.id
union
select * from emp right join dept on dept_id = dept.id;
# union 将自动去除重复的记录
# union 必须保证两个查询结果的列数相同,一般用在多个结果完全一致时
# union all 不去重复
+------+--------+------+---------+------+--------+
| id | name | sex | dept_id | id | name |
+------+--------+------+---------+------+--------+
| 1 | 大黄 | m | 1 | 1 | 市场 |
| 2 | 老王 | m | 2 | 2 | 财务 |
| 3 | 老李 | w | 30 | NULL | NULL |
| NULL | NULL | NULL | NULL | 3 | 行政 |
+------+--------+------+---------+------+--------+
总结:外连接查询,查到的是没有对应关系的记录,但是这样的数据原本就是有问题的,所以最常用的是内连接查询.
内连接表示: 只显示匹配成的记录
外连接: 没有匹配成功的也要实现
