【SQL】多表查询

多表查询,即查询可以从两个或多个表中获取数据。在Oracle中,有两种类型的连接格式:ANSI SQL连接格式和Oracle特有的连接格式。Oracle建议采用符合ANSI标准的连接格式。

1、内连接

(1)Oracle中特有的连接语法为:

SELECT table1.column,table2.column

FROM table1,table2

WHERE table1.column1=table2.column2;

如:

SQL> SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname

2  FROM emp e ,dept d

3  WHERE e.deptno=d.deptno;

EMPNO ENAME      JOB       HIREDATE                   SAL DNAME

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

7782 CLARK      MANAGER   1981-06-09 00:00:00       2450 ACCOUNTING

7839 KING       PRESIDENT 1981-11-17 00:00:00       5000 ACCOUNTING

7934 MILLER     CLERK     1982-01-23 00:00:00       1300 ACCOUNTING

7566 JONES      MANAGER   1981-04-02 00:00:00       2975 RESEARCH

7788 SCOTT      ANALYST   1987-04-19 00:00:00       3000 RESEARCH

7876 ADAMS      CLERK     1987-05-23 00:00:00       1100 RESEARCH

7902 FORD       ANALYST   1981-12-03 00:00:00       3000 RESEARCH

7844 TURNER     SALESMAN  1981-09-08 00:00:00       1500 SALES

7521 WARD       SALESMAN  1981-02-22 00:00:00       1250 SALES

7499 ALLEN      SALESMAN  1981-02-20 00:00:00       1600 SALES

7900 JAMES      CLERK     1981-12-03 00:00:00        950 SALES

7698 BLAKE      MANAGER   1981-05-01 00:00:00       2850 SALES

7654 MARTIN     SALESMAN  1981-09-28 00:00:00       1250 SALES

13 rows selected.

(2)ANSI SQL的标准内连接语法为:

SELECT table1.column,table2.column

FROM table1 INNER JOIN table2

ON table1.column1=table2.column2;

如:

SQL> SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname

2  FROM emp e INNER JOIN dept d

3  ON e.deptno=d.deptno;

EMPNO ENAME      JOB       HIREDATE                   SAL DNAME

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

7782 CLARK      MANAGER   1981-06-09 00:00:00       2450 ACCOUNTING

7839 KING       PRESIDENT 1981-11-17 00:00:00       5000 ACCOUNTING

7934 MILLER     CLERK     1982-01-23 00:00:00       1300 ACCOUNTING

7566 JONES      MANAGER   1981-04-02 00:00:00       2975 RESEARCH

7788 SCOTT      ANALYST   1987-04-19 00:00:00       3000 RESEARCH

7876 ADAMS      CLERK     1987-05-23 00:00:00       1100 RESEARCH

7902 FORD       ANALYST   1981-12-03 00:00:00       3000 RESEARCH

7844 TURNER     SALESMAN  1981-09-08 00:00:00       1500 SALES

7521 WARD       SALESMAN  1981-02-22 00:00:00       1250 SALES

7499 ALLEN      SALESMAN  1981-02-20 00:00:00       1600 SALES

7900 JAMES      CLERK     1981-12-03 00:00:00        950 SALES

7698 BLAKE      MANAGER   1981-05-01 00:00:00       2850 SALES

7654 MARTIN     SALESMAN  1981-09-28 00:00:00       1250 SALES

13 rows selected.

2、外连接

(1)Oracle中特有的连接语法为:

--右外连接

SELECT table1.column,table2.column

FROM table1, table2

WHERE table1.column1(+)=table2.column2;

--左外连接

SELECT table1.column,table2.column

FROM table1, table2

WHERE table1.column1=table2.column2(+);

注意:

右外连接:当(+)符号在等号左边,将返回table2表中的所有数据。

左外连接:当(+)符号在等号右边,将返回table1表中的所有数据。

等号两边不能同时都加(+)符号

(2)ANSI SQL标准中的连接、右连接和全连接语法为:

SELECT table1.column,table2.column

FROM table1

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column=table2.column)];

3、交叉连接(笛卡尔积)

如果在进行连接的时候没有指定连接条件,则会产生笛卡尔积,返回的记录数是两个表中记录数的乘积。

4、自然连接

前提要求:两个表中有相同名称、相同数据类型的列。如果两个标的列名相同而数据类型不相同,也不能进行自然连接。自然连接使用NATRUAL JOIN关键字

如:

SQL> SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname

2  FROM emp e NATURAL JOIN dept d;

EMPNO ENAME      JOB       HIREDATE                   SAL DNAME

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

7782 CLARK      MANAGER   1981-06-09 00:00:00       2450 ACCOUNTING

7839 KING       PRESIDENT 1981-11-17 00:00:00       5000 ACCOUNTING

7934 MILLER     CLERK     1982-01-23 00:00:00       1300 ACCOUNTING

7566 JONES      MANAGER   1981-04-02 00:00:00       2975 RESEARCH

7788 SCOTT      ANALYST   1987-04-19 00:00:00       3000 RESEARCH

7876 ADAMS      CLERK     1987-05-23 00:00:00       1100 RESEARCH

7902 FORD       ANALYST   1981-12-03 00:00:00       3000 RESEARCH

7844 TURNER     SALESMAN  1981-09-08 00:00:00       1500 SALES

7521 WARD       SALESMAN  1981-02-22 00:00:00       1250 SALES

7499 ALLEN      SALESMAN  1981-02-20 00:00:00       1600 SALES

7900 JAMES      CLERK     1981-12-03 00:00:00        950 SALES

7698 BLAKE      MANAGER   1981-05-01 00:00:00       2850 SALES

7654 MARTIN     SALESMAN  1981-09-28 00:00:00       1250 SALES

13 rows selected.

上一篇:Android 仿百度网页音乐播放器圆形图片转圈播放效果


下一篇:iterator迭代器的使用