SQL multi-Table query

1. Equivalent connection

Select E.empno d.dname from emp e,dept D where E.deptno=d.deptno;

2. No equivalent connection

Sql> --No equivalent connectionSql> --Query Employee Information: Employee Number name Salary scaleSql> Select *  fromSalgrade; GRADE losal hisal---------- ---------- ----------                                                         1         the        -                                                         2       1201       1400                                                         3       1401        -                                                         4       2001        the                                                         5       3001       9999SQL> SelectE.empno,e.ename,e.sal,s.grade2   fromEMP E,salgrade s3  whereE.salbetweenS.losal ands.hisal; EMPNO ename SAL GRADE---------- ---------- ---------- ----------                                           7369SMITH -          1                                           7900JAMES950          1                                           7876Adams1100          1                                           7521WARD1250          2                                           7654MARTIN1250          2                                           7934MILLER1300          2                                           7844TURNER the          3                                           7499ALLEN the          3                                           7782CLARK2450          4                                           7698BLAKE2850          4                                           7566JONES2975          4EMPNO ename SAL GRADE /c4>---------- ---------- ---------- ----------                                           7788SCOTT the          4                                           7902FORD the          4                                           7839KING the          5                                     

View Code

3. External links

--External ConnectionSql> --number of employees by department: Department number of department nameSql> SelectD.deptno department number, D.dname department name,Count(e.empno) Number of persons2   fromEMP e,dept D3  whereE.deptno=D.deptno4 Group by D.deptno,d.dname; (not appearing in the grouping function) Department number number of department name ---------- -------------- ----------                                                    TenACCOUNTING3                                                     -The5                                                     -SALES6SQL> Select *  fromDept; DEPTNO dname LOC---------- -------------- -------------                                                 TenACCOUNTING NEW YORK -DALLAS -SALES CHICAGO +OPERATIONS BOSTON SQL> Select *  fromEmpwhereDeptno= +; row SQL not selected> /*sql> Hope: For some of the non-established records, still want to include in the final result sql> left outer joins: When where E.deptno=d.deptno is not, the table to the left of the equal sign is still contained sql> notation: where E.deptno=d.deptno (+) Sql> right outer join: When where E.deptno=d.deptno is not true, the table to the right of the equal sign is still contained sql> notation: where E.deptno (+) = D.deptnosql>*/SQL> SelectD.deptno department number, D.dname department name,Count(e.empno) Number of persons2   fromEMP e,dept D3  whereE.deptno (+)=D.deptno4  Group  byD.deptno,d.dname; Department number number of department name---------- -------------- ----------                                                    TenACCOUNTING3                                                     +OPERATIONS0                                                     -The5                                                     -SALES6                                            

SQL multi-Table query