8.oracle表连接

Oracle 表之间的连接分为三种：
1. 内连接(自然连接)
2. 外连接
（1）左外连接 (左边的表不加限制)
（2）右外连接(右边的表不加限制)
（3）全外连接(左右两表都不加限制)
3. 自连接（同一张表内的连接）

SQL的标准语法：
select table1.column,table2.column
from table1 [inner | left | right | full ][outer] [cross] join table2 
     on table1.column1 = table2.column2;
inner join 表示内连接；
left [outer] join表示左外连接；
right [outer] join表示右外连接；
full [outer] join表示完全外连接；
cross join   笛卡尔连接；
on 子句 用于指定连接条件。
注意：
/*如果使用from子句指定内、外连接，则必须要使用on子句指定连接条件；
如果使用（+）操作符指定外连接，则必须使用where子句指定连接条件。*/;

内连接 INNER Join 
/*Inner join逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表是一样的效果，
      所以内连接用的很少。还有一点要说明的就是Join 默认就是inner join。 所以我们在写内连接的时候可以省略inner 这个关键字。*/
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

外连接 Outer Join
/*outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。
外连接分为三种： 左外连接，右外连接，全外连接。
对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我们省略outer 这个关键字。 写成：LEFT/RIGHT/FULL JOIN
在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。
如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。 */
SELECT * FROM EMP E left JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM EMP E left outer JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

SELECT * FROM EMP E right JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM EMP E right outer JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE ENAME='SMITH';

SELECT * FROM EMP E full JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM EMP E full outer JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND ENAME='SMITH';

SELECT * FROM EMP E CROSS JOIN DEPT D WHERE ENAME='SMITH';

/*对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项*/
1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符
3.（+）操作符只适用于列，而不能用在表达式上。
4.（+）操作符不能与or和in操作符一起使用。
5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外;

SELECT * FROM EMP E,DEPT D where d.deptno=e.deptno(+);
SELECT * FROM EMP E,DEPT D where d.deptno(+)=e.deptno and ename='SMITH';

--emp表中谁没有下属
select * from emp;
select a.ename from emp a left join emp p on a.empno=p.mgr where p.empno is null;
--与smith同部门的其他人   关联条件有多个
select P.ENAME from emp e INNER join EMP p on e.deptno=p.deptno AND E.ENAME='SMITH' where P.ename<>'SMITH';
select P.ENAME from emp e INNER join EMP p on e.deptno=p.deptno AND E.ENAME='SMITH' AND P.ename<>'SMITH';
--与simith同部门或者同岗位
select * from emp e inner join emp p on E.ENAME='SMITH' and (e.deptno=p.deptno or e.job=p.job) where p.ename<>'SMITH';

--关联表不止一张 
--查询所有人的姓名，部门名称，岗位，薪资，薪资水平
SELECT * FROM (EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND SAL IS NOT NULL) M LEFT JOIN SALGRADE S ;

--查询simith姓名，部门名称，岗位，薪资，薪资水平

select e.ENAME,d.DNAme,E.SAL,s.GRADE from emp e
left join dept d
on e.deptno=d.deptno and sal is not null
left join salgrade s
on e.sal between s.losal and s.hisal where e.ename='SMITH';

--查询10号部门员工姓名，部门名称，岗位，薪资，薪资水平
select e.ENAME,d.DNAme,E.SAL,s.GRADE from emp e
left join dept d
on e.deptno=d.deptno and sal is not null
left join salgrade s
on e.sal between s.losal and s.hisal where e.deptno=10;

--查询sales部门员工姓名，部门名称，岗位，薪资，薪资水平
select e.ENAME,d.DNAme,E.SAL,s.GRADE from emp e
left join dept d
on e.deptno=d.deptno and sal is not null
left join salgrade s
on e.sal between s.losal and s.hisal where d.dname='SALES';

注意：左外连接时，条件查询中写在where 和 on 中有什么区别？
写在where中是两个数据源先做关联，再对连接结果做筛选
写在on中，对连接数据源先进性筛选，在对结果进行数据连接

--查询sasles部门和research部门所有员工姓名，岗位，薪资，工作地点
select e.ENAME,E.JOB,E.SAL,D.LOC from emp e
left join dept d
on e.deptno=d.deptno
where d.dname='SALES' OR D.DNAME='RESEARCH';

3.如何判断什么时候选择表连接，何时使用子连接
/*
在Oracle中，表连接和子查询都可以用来查询多张表中的数据，但它们有不同的使用场景。
一般来说，当查询需要多个表之间的数据关联时，使用表连接比使用子查询更为高效。表连接是通过将多张表中的数据通过一
个共同的字段进行关联，从而将数据合并成一张表进行查询的方式。通过表连接，可以将多张表中的数据一次性查询出来，避免
了反复查询和重复数据的出现，同时也可以利用索引进行优化查询。
而当查询中需要使用到子查询时，通常是因为需要在内部查询中使用一些聚合函数或者其他复杂的条件判断，或者需要查询的数据量非常
少。此时使用子查询比使用表连接更为合适。因为子查询通常只会查询少量的数据，而且往往需要进行一些复杂的计算，这些计算很
难在表连接中进行优化，因此使用子查询可以更好地满足这些需求。
综上所述，选择使用表连接还是子查询，取决于具体的查询需求和数据量。对于需要关联多张表的查询，应该优先考虑使用表连接；而对
于一些复杂的计算和需要查询少量数据的情况，可以考虑使用子查询。
*/
执行顺序：on-where-having;