Oracle self-connect/outer JOIN/subquery

--Three formats for connection queries select Ename, deptno,dname from EMP Natural join Dept;select ename, deptno,dname from EMP Join dept using (DEPTN o); Select Emp.ename, emp.deptno,dept.dname from EMP Join dept on emp.deptno!=dept.deptno;--Self-connect, treats a table as two tables for select E.ename,m.ename from EMP e-join EMP m on e.mgr= m.empno--outer Connection right outer join/left outer join/full outer Joinselect ename,d  . deptno,d.dnamefrom EMP Right OUTER join Dept D on emp.deptno= d.deptno--connect more Tables Select Ename,dname,grade from EMP Join dept On Emp.deptno=dept.deptno          joins Salgrade on Sal between Losal and Hisal;------------------------------------------- --------------------------subquery--single-row subquery: Subquery Returns a row of select * from EMP where sal> (select AVG (SAL) from EMP);--Multiline subquery: Subquery returns multiple rows Any (arbitrary) all (everything) in (inside) SELECT * from emp where Sal>all (select AVG (SAL) from EMP Group by DEPTNO);--[not] exists determine if the row exists s Elect Ename,mgrfrom EMP E where EXISTS (select 1 from emp where e.empno=mgr);

　　

Oracle self-connect/outer JOIN/subquery