MySQL各种多表查询

SQL92标准中的查询

等值链接

1. 多表等值连接的结果为多表的交集部分；

2. n表连接，至少需要n-1个连接条件；

3. 多表不分主次，没有顺序要求；

4. 一般为表起别名，提高阅读性和性能；

5. 可以搭配排序、分组、筛选 ….等子句使用；

等值连接也被称为 简单连接或内连接。

查询雇员 King 所在的部门名称。
select d.department_name from employees e,departments d   where e.dept_id =
d.department_id and e.last_name = 'King';

 非等值链接

即使用非（=）操作符获得关系。

查询所有雇员的薪水级别。
select e.last_name,j.grade_level from employees e ,job_grades j 
where e.salary between j.lowest_sal and j.highest_sal;

自连接

连接一个表到他自己，需要给该表起两个名字。

//查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。
SELECT
worker.LAST_NAME W,manager.LAST_NAME M
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID;

SQL99标准中的查询

交叉连接

cross join子句导致两个表的交叉乘积，该连接和两个表之间的笛卡尔积是一样的（去重后的）。

交叉连接在遇到列名相同的列时，会查询到许多冗余数据，

select * from employees cross join departments;

 自然连接

自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。
sql语句：Select …… from 表1 natural join 表2

使用自然连接查询所有有部门的雇员的名字以及部门名称。
select e.last_name,d.department_name from employees e 
natural join departments d;

内连接

内连接基本与自然连接相同，不同之处在于自然连接要求是同名属性列的比较，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。
sql语句：Select …… from 表1 inner join 表 2 on 表1.A=表2.E

select e.employee_id,e.salary,d.department_name from employees e inner JOIN
departments d on e.department_id = d.department_id where e.last_name = 'Fox';

 外连接

外连接包括做外连接，右外连接已经全外连接，会出现孤儿数据（被连接的列的值为空的数据） 。

左外连接

左外连接，即使没有与 左表中匹配的行，该查询也会取回 左表中所有的行。

select e.last_name,d.department_name from employees e 
LEFT OUTER JOIN departments d on e.dept_id = d.department_id;

 右外连接

与左外连接相似， 即使没有与 右表中匹配的行，该查询也会取回右表中所有的行

select  e.last_name,d.department_name  from  employees  e 
RIGHT  OUTER JOIN departments  d on e.DEPARTMENT_ID = d.department_id;

 全外连接

全外连接是在两表进行自然连接，只把左表和右表要舍弃的都保留在结果集中，相对应的列上填null。
Select …… from 表1 full join 表2 on 表1.C=表2.C

MySql不支持full join，可以使用 union 实现全完连接。

        UNION可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。

        UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，  那么返回的结果集就会包含重复的数据了。

(select e.last_name,d.department_name from employees e 
LEFT OUTER JOIN departments d  on  e.department_id =  d.department_id)
UNION
(select  e1.last_name,d1.department_name  from employees e1 
RIGHT OUTER JOIN departments d1 on d1.department_id = e1.department_id)