多表连接查询

1 多表查询

1.1 笛卡尔积导致查询错误

##多表查询如何实现？
SELECT employee_id,department_name 
FROM employees,departments;

此条查询语句的结果会以笛卡尔积的形式乘在一起
错误现象：每个员工和每个不同部门都匹配了一遍
错误原因：缺少多表的连接条件

1.2查询两个表的公共字段导致错误

SELECT employee_id,department_name,department_id
FROM employees,departments
#两个表的连接条件
WHERE employees.department_id = departments.department_id;

错因：因为两个表中都有这个字段服务器不知道去哪个表中查询这个字段
解决办法：明确的写出要查询那个表中的 department_id
修正代码：

SELECT employee_id,department_name,employees.department_id
FROM employees,departments
#两个表的连接条件
WHERE employees.department_id = departments.department_id;

建议从sql优化的角度在每个多表查询的字段前都加上所在的表名

1.3 多表查询的正确方式

添加连接条件

##正确的查询方式：添加连接条件
SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.department_id = departments.department_id;

1.4可以给表起别名避免sql代码太臃肿

#3.可以给表起别名，防止代码可读性太差
SELECT emp.employee_id,dep.department_name,emp.department_id
FROM employees emp,departments dep
#两个表的连接条件
WHERE emp.department_id = dep.department_id;

注意点：表的别名写在from后面
如果改了别名，本条sql语句所有用到表的所有的地方都要用别名，不能使用原表名，否则会报错

1.5n张表连接查询

#4.如果有n个表查询，则需要至少n-1个连接条件。
#练习：查询员工的employment_id,last_name,department_name,city
SELECT emp.employee_id,emp.last_name,dep.department_name,loc.city
FROM employees emp,departments dep,locations loc
WHERE emp.department_id=dep.department_id
AND dep.location_id=loc.location_id;

注意点：如果有n个表查询，则需要至少n-1个连接条件

2 内连接与外连接

2.1内连接

内连接也叫自然拼接，合并具有同一列的两张以上表的行，结果集中不包含一个表与另一个表不匹配的行

SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id();

2.2外连接

2.2.1左外连接

左外连接的原理就是把右边的表的长度填充到和左边一样长

#mysql不支持sql92语法中的外连接的写法

#左外连接 
#查询所有的员工的last_name,depart_name信息

SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id(+); ##需要使用左外连接
## sql92语法实现内连接 见上
## sql92语法实现外连接 使用 +

mysql使用sql99语法的join on实现多表查询 这种方式也能解决外连接的问题

3 sql99语法实现多表查询

3.1sql99语法实现内连接（两张表查询）

SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

注意点：1.与92语法不同的是两个被查询的表之间用join连接
2.用on替换where查询条件

3.2 sql99语法实现内连接（多张表查询）

SELECT emp.employee_id,emp.last_name,dep.department_name,loc.city
FROM employees emp JOIN departments dep
ON emp.department_id=dep.department_id
JOIN locations loc
ON dep.location_id=loc.location_id;

注意：join和on始终是挨着成对出现
如果不写inner join默认是内连接

3.1sql99语法实现外连接**

3.2.1左外连接

SELECT last_name,department_name
FROM employees e left OUTER JOIN departments d
ON e.department_id=d.department_id;

注意点：outer可以省略掉

4 七种join的实现

4.1union和union all

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并
时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION
ALL关键字分隔。

union会执行去重操作，效率低一些
union all 不执行去重操作，效率高一些
如果我们明确知道连接的两个结果集没有重复部分，使用union all