Oracle-Query Statements-Multiple Table association queries

/*the historical version of the SQL statement SQL89 comparison SQL92 SQL99 multi-Table association query Cartesian product equivalent correlation query Non-equivalence correlated query left outer connection right outer connection full outer connection self-connected*/----------------------------------92 Syntax--Query the department name and employee name (there is no constraint on the two-sheet association)SELECT *  fromEMP;SELECT *  fromDEPT;SELECT *  fromSalgrade;SELECT *  fromemp,dept;SELECT *  fromEmp,dept,salgrade;--equivalent correlation querySELECT *  fromEmp,deptWHEREEmp. DEPTNO=DEPT. DEPTNO;SELECTDEPT. Dname,emp. Ename fromEmp,deptWHEREEmp. DEPTNO=DEPT. DEPTNO;--Query the department name where Sott is locatedSELECTDEPT. Dname fromEmp,deptWHEREEmp. DEPTNO=DEPT. DEPTNO andEmp. Ename= 'SCOTT';--non-equivalent correlation querySELECTEmp. Ename,emp. Sal,salgrade. GRADE fromEmp,salgradeWHEREEmp. SALbetweenSalgrade. Losal andSalgrade. Hisal;--sets an alias for the table, which is valid only for the current querySELECTE.ename,e.sal,s.grade fromEMP E,salgrade SWHEREE.salbetweenS.losal ands.hisal;--Query Department number, department name, employee name, employee salary, employee salary levelSELECTD.deptno,d.dname,e.ename,e.sal,s.grade fromEMP e,dept d,salgrade SWHEREE.deptno=D.deptno andE.salbetweenS.losal ands.hisal;--External connection: To retrieve data that is not eligibleSELECTDEPTNO,COUNT(*) fromEmpGROUP  byDEPTNO;SELECTD.deptno,d.dname,e.ename fromEMP e,dept DWHEREE.deptno=D.deptno;--Right Outer connectionSELECTD.deptno,d.dname,e.ename fromEMP e,dept DWHEREE.deptno (+)=D.deptno;--Left outer connectionSELECTD.deptno,d.dname,e.ename fromEMP e,dept DWHEREE.deptno=D.deptno (+);--Modifying DataUPDATEEmpSETDEPTNO= NULL WHEREEMPNO= 7934;COMMIT;--self-connectSELECT *  fromEMP;--Query the name of the current employee and managerSELECTE.ename, E.mgr, M.ename,m.empno fromEMP e,emp MWHEREE.mgr=M.empno (+); ----------------------------------99 Syntax--Cartesian productSELECT *  fromEMP e,dept D;SELECT *  fromEMP E Cross JOINDEPT D;--equivalent connections (that is, automatically matching names, columns of the same type)SELECT *  fromEMP E NATURALJOINDEPT D;SELECT *  fromEMP EJOINDEPT D USING (DEPTNO);SELECT *  fromEMP EJOINDEPT D on(E.deptno=D.deptno);--non-equivalent connectionSELECT *  fromEMP EJOINSalgrade S on(E.salbetweenS.losal ands.hisal);--External ConnectionSELECT *  fromEMP E Left JOINDEPT D USING (DEPTNO);SELECT *  fromEMP E Right JOINDEPT D USING (DEPTNO);SELECT *  fromEMP E Full JOINDEPT D USING (DEPTNO);--self-connectSELECTE.ename,m.ename fromEMP EJOINEMP M on(E.mgr=m.empno);SELECTE.ename,m.ename fromEMP E Left JOINEMP M on(E.mgr=m.empno);--subquery (as a query condition)SELECT *  fromEmpWHERESAL=(SELECT MAX(SAL) fromEMP);SELECT *  fromEmpWHERESAL>(SELECT AVG(SAL) fromEMP);--Sub-query (as a virtual table)SELECT *  fromEMP E, (SELECT AVG(SAL) Asl,deptno fromEmpGROUP  byDEPTNO) TWHEREE.sal>T.ASL andE.deptno=T.deptno;--single-line subquerySELECT *  fromEmpWHERESAL=(SELECT MAX(SAL) fromEMP);--multi-row subqueriesSELECT *  fromEMP EWHEREE.salinch(SELECT MAX(SAL) fromEmpWHEREDEPTNO is  not NULL GROUP  byDEPTNO);/*In : Take one of them equal to OK all: must all satisfy the condition SOME: a part can be satisfied*/

Oracle-Query Statements-Multiple Table association queries