oracle两表子查询,Oracle02-查询(多表，子查询)

多表查询：

两个和两个以上表或是视图的查询。

SQL> select a1.ename,a1.sal,a2.dname from emp a1 ,dept a2 where a1.deptno=a2.deptno;

ENAME              SAL DNAME

---------- ----------- --------------

SMITH           800.00 RESEARCH

ALLEN          1600.00 SALES

WARD           1250.00 SALES

JONES          2975.00 RESEARCH

MARTIN         1250.00 SALES

BLAKE          2850.00 SALES

CLARK          2450.00 ACCOUNTING

SCOTT          3000.00 RESEARCH

KING           5000.00 ACCOUNTING

TURNER         1500.00 SALES

ADAMS          1100.00 RESEARCH

JAMES           950.00 SALES

FORD           3000.00 RESEARCH

MILLER         1300.00 ACCOUNTING

14 rows selected

首先是：

SQL> select a1.ename,a1.sal,a2.dname from emp a1 ,dept a2;

ENAME              SAL DNAME

---------- ----------- --------------

SMITH           800.00 ACCOUNTING

ALLEN          1600.00 ACCOUNTING

WARD           1250.00 ACCOUNTING

JONES          2975.00 ACCOUNTING

MARTIN         1250.00 ACCOUNTING

BLAKE          2850.00 ACCOUNTING

CLARK          2450.00 ACCOUNTING

SCOTT          3000.00 ACCOUNTING

KING           5000.00 ACCOUNTING

TURNER         1500.00 ACCOUNTING

ADAMS          1100.00 ACCOUNTING

JAMES           950.00 ACCOUNTING

FORD           3000.00 ACCOUNTING

MILLER         1300.00 ACCOUNTING

SMITH           800.00 RESEARCH

ALLEN          1600.00 RESEARCH

WARD           1250.00 RESEARCH

JONES          2975.00 RESEARCH

MARTIN         1250.00 RESEARCH

BLAKE          2850.00 RESEARCH

ENAME              SAL DNAME

---------- ----------- --------------

CLARK          2450.00 RESEARCH

SCOTT          3000.00 RESEARCH

KING           5000.00 RESEARCH

TURNER         1500.00 RESEARCH

ADAMS          1100.00 RESEARCH

JAMES           950.00 RESEARCH

FORD           3000.00 RESEARCH

MILLER         1300.00 RESEARCH

SMITH           800.00 SALES

ALLEN          1600.00 SALES

WARD           1250.00 SALES

JONES          2975.00 SALES

MARTIN         1250.00 SALES

BLAKE          2850.00 SALES

CLARK          2450.00 SALES

SCOTT          3000.00 SALES

KING           5000.00 SALES

TURNER         1500.00 SALES

ADAMS          1100.00 SALES

JAMES           950.00 SALES

FORD           3000.00 SALES

ENAME              SAL DNAME

---------- ----------- --------------

MILLER         1300.00 SALES

SMITH           800.00 OPERATIONS

ALLEN          1600.00 OPERATIONS

WARD           1250.00 OPERATIONS

JONES          2975.00 OPERATIONS

MARTIN         1250.00 OPERATIONS

BLAKE          2850.00 OPERATIONS

CLARK          2450.00 OPERATIONS

SCOTT          3000.00 OPERATIONS

KING           5000.00 OPERATIONS

TURNER         1500.00 OPERATIONS

ADAMS          1100.00 OPERATIONS

JAMES           950.00 OPERATIONS

FORD           3000.00 OPERATIONS

MILLER         1300.00 OPERATIONS

56 rows selected

产生了笛卡儿积，4*14=56；

然后：2个表添一个条件消除笛卡儿积(n张表要n-1个条件才能消除笛卡儿积)

SQL> select a1.ename,a1.sal,a2.dname from emp a1 ,dept a2 where a1.deptno=a2.deptno;

子查询：指嵌入在其他sql语句中的select语句。

注意：把条件多的放查询语句放到后面，sql从后往前。

单行子查询：

如：

查询和SMITH在一个部门的员工

1，查询SMITH所在部门：

SQL> select deptno from emp where ename='SMITH';

DEPTNO

------

20

2，

SQL> select * from emp where deptno =(select deptno from emp where ename='SMITH');

EMPNO ENAME      JOB         MGR HIREDATE            SAL        COMM DEPTNO

----- ---------- --------- ----- ----------- ----------- ----------- ------

7369 SMITH      CLERK      7902 1980/12/17       800.00                 20

7566 JONES      MANAGER    7839 1981/4/2        2975.00                 20

7788 SCOTT      ANALYST    7566 1987/4/19       3000.00                 20

7876 ADAMS      CLERK      7788 1987/5/23       1100.00                 20

7902 FORD       ANALYST    7566 1981/12/3       3000.00                 20

多行查询：

查询和部门10号工作相同的员工

1，查询部门10号的工作

SQL> select job from emp where deptno='10';

JOB

---------

MANAGER

PRESIDENT

CLERK

2，  SQL> select * from emp where  job in (select distinct job from emp where deptno='10' );   EMPNO ENAME      JOB         MGR HIREDATE            SAL        COMM DEPTNO ----- ---------- --------- ----- ----------- ----------- ----------- ------  7782 CLARK      MANAGER    7839 1981/6/9        2450.00                 10  7698 BLAKE      MANAGER    7839 1981/5/1        2850.00                 30  7566 JONES      MANAGER    7839 1981/4/2        2975.00                 20  7839 KING       PRESIDENT       1981/11/17      5000.00                 10  7934 MILLER     CLERK      7782 1982/1/23       1300.00                 10  7900 JAMES      CLERK      7698 1981/12/3        950.00                 30  7876 ADAMS      CLERK      7788 1987/5/23       1100.00                 20  7369 SMITH      CLERK      7902 1980/12/17       800.00                 20