Oracle多表查询，顾名思义就是从多个表中查询你所要的数据。这里以scott用户下的emp表与dept表进行试验，两个表的结构如下：
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
使用连接可在多个表中查询数据：
基本语句如下：
SELECT table1.column, table2.column from table1, table2 where table1.column1 = table2.column2
连接的类型分成很多种：Equijoins（等值连接） Non-equijoin（非等值连接） Outer-join（外连接） Self-join（自连接） Cross joins（交叉连接） Natural joins（自然连接）
1、Equijoins
Equijoins（等值连接）--利用emp表中的deptno与dept表中的deptno进行等值连接。
SQL> select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept where
2 emp.deptno=dept.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7369 SMITH 20 20 RESEARCH
7499 ALLEN 30 30 SALES
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7782 CLARK 10 10 ACCOUNTING
7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH
EMPNO ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
14 rows selected.
2、Non-equijoin
Non-equijoin（非等值连接）--新建一个名为job_grades的表，并往其中添加数据：
SQL> select * from job_grades
2 ;
GRA LOWEST_SAL HIGHEST_SAL
----- ---------- -----------
A 500 1499
B 1500 2499
C 2500 3500
SQL> select emp.ename,emp.sal,job_grades.gra from emp,job_grades where emp.sal between
2 job_grades.lowest_sal and job_grades.highest_sal;
ENAME SAL GRA
---------- ---------- -----
SMITH 800 A
WARD 1250 A
MARTIN 1250 A
ADAMS 1100 A
JAMES 950 A
MILLER 1300 A
ALLEN 1600 B
CLARK 2450 B
TURNER 1500 B
JONES 2975 C
BLAKE 2850 C
ENAME SAL GRA
---------- ---------- -----
SCOTT 3000 C
FORD 3000 C
13 rows selected.