SQL multi-Table query

Source: Internet
Author: User
Tags dname

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.