Oracle SQL多表查询

Oracle SQL多表查询

开发工具与关键技术：Oracle sql*plus PLSQL Developer
作者：王福驰
撰写时间：2019年4月01日

多表查询要注意的笛卡尔积和叉集，他们都是一样的。
笛卡尔积：它主要是因为省略连接条件、连接条件无效、所有表中的所有行互相连接，所产生，所以我们为了避免笛卡尔积为他加上有效where语句连接条件。
叉集：使用CROSS JOIN 子句使连接的表产生叉集。
一、 等值连接
是指两张表，有关系，一张在自己做主键，在其他表做附键。
Select e.last_name,e.salary ,e.department_id,d.department_id,d.location_id
from employees e,departments d where e.department_id=d.department_id

二、 非等值连接
是指二张没有关系的表进行连接，有关系会产生叉积
借用别人给我讲过的例子：EMPLOYEES表中的列工资应在JOB_GRADES表中的最高工资与最低工资之间
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

如果有关系你还有非等值连接
SELECT e.last_name, e.salary, j.job_id FROM employees e, jobs j
WHERE e.salary BETWEEN j.min_salary AND j.max_salary

从107行变645行
三、 左连接(Oracle语法)
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右）外连接，注意：（+）在右是左连接，如下例子
例子：列出每个员工的姓名，工作，部门号，部门名
select e.last_name,j.job_title,e.department_id,d.department_name
from employees e ,jobs j ,departments d
where e.department_id = d.department_id(+) and e.job_id = j.job_id(+)

四、 右连接(Oracle语法)
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右）外连接，注意：（+）在左是右连接，如下例子
例子：对于dept表中，列出所有部门名，部门号，
select e.last_name,e.job_id, d.department_id,d.department_name
from employees e ,departments d
where e.department_id(+) = d.department_id

五、 自连接
自连接就是他自己连接自己，如下例子
例子：对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
select e.last_name,d.last_name from employees e , employees d
where e.manager_id = d.employee_id

六、 自然连接
NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
在表中查询满足等值条件的数据。如果只是列名相同而数据类型不同，则会产生错误。
select last_name,salary,department_id,location_id
from employees natural join departments

七、 USING 字句
在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。使用 USING 可以在有多个列满足条件时进行选择。不要给选中的列中加上表名前缀或别名。
JOIN 和 USING 子句经常同时使用。
select e.last_name,e.salary,d.department_name
from employees e join departments d using(department_id)

八、 ON 子句（常用）
自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。
例子：查询出公司中所有 manager 的详细信息.
select distinct s.*
from employees e
join employees s
on e.manager_id = s.employee_id

九、 左外连接(1999语法)
两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。 左外连接的OUTER可以省略
例子：列出emp中姓名，工作，部门号，部门名
select e.last_name,e.job_id,e.department_id,d.department_name
from employees e left OUTER join departments d
on e.department_id = d.department_id

十、 右外连接(1999语法)
两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。右外连接的OUTER可以省略
例子：列出locations中所有信息，部门号，部门名
select d.department_id,d.department_name,l.* from departments d
RIGHT OUTER join locations l on d.location_id = l.location_id

十一、 满外连接(1999语法)
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满外连接。满外连接的OUTER 不可以省略。
select e.last_name,e.job_id,e.department_id,d.department_name
from employees e full OUTER join departments d
on e.department_id = d.department_id