多表查询

来源：互联网发布：淘宝内衣买家晒图编辑：程序博客网时间：2024/05/28 06:04

多表查询

等连接

通过两个表具有相同意义的列,建立连接条件.

查询结果只显示两个列中的值是等值条件的行数据

表中同名列被选择时必须添加表名前缀进行修饰否则无法确定这一列是属于哪个表

不等连接

A表中的某列数据和B表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴

自连接

数据都来自一张表,所以在from字句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名.

本质就是将一张表虚拟成了两张表

外连接

即是选择出满足等连接条件及其以外的行

(+)修饰符号用法:放置在选出结果只包含等连接的列后,则另一列的结果就是等值行+非等值行

全连接

select * from t1

union

select * from t2;

当数据需要从多个表中获得时

idle> select * from emp,dept;

...

56 rows selected.

这样得到的结果是 56 行,其实两个表产生了笛卡尔集 14 * 4

idle> select count(*) from emp;

COUNT(*)

----------

idle> select count(*) from dept;

COUNT(*)

----------

idle>

在连接中给定一个等值条件一般是主键和外键的关系

例如deptno在dept表中是主键在emp表中是外键

观看dept(部门表)和emp(员工表)的关系

他们是主从关系,dept是主表,emp是从表

emp中的deptno一定要隶属于dept中的deptno

所以他们两个列是等值的

具体的主键和外键关系我们在以后建表时再详细阐述

等连接

按deptno的等值关系联合两张表的所有列

idle> select * from dept,emp where dept.deptno=emp.deptno;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SALCOMM DEPTNO

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

10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

30 SALES CHICAGO 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

14 rows selected.

idle>

这才是我们要的结果.笛卡尔积几乎我们不会需要.多表查询时基本都带有where子句来描述多个表的关系避免笛卡尔集

当两个表中有相同的列名时,为了区分要在列前加上表名作前缀.

不冲突时直接使用

idle> select deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno;

select deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno

ERROR at line 1:

ORA-00918: column ambiguously defined

因为deptno 没有表前缀冲突

idle>

在联合的两个表内取数据:描述scott在哪个部门

idle> select empno,ename,dname,sal from emp,dept where emp.deptno=dept.deptno and ename='SCOTT';

EMPNO ENAME DNAME SAL

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

7788 SCOTT RESEARCH 3000

idle>

表的别名

格式:表名别名

给表取别名是很有必要的,因为有的表名很长不便于引用时书写.

idle> select a.deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno and ename='SCOTT';

DEPTNO EMPNO ENAME DNAME SAL

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

20 7788 SCOTT RESEARCH 3000

idle>

不等连接

就是排除完全相等条件以外的 >,<,!=, <=, >=, between and

主要在于不同表之间显示特定范围的信息(也可以理解成包含关系)

例如: SALGRADE表把工资划分了5个等级

idle> select * from SALGRADE;

GRADE LOSAL HISAL

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

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

idle>

查处每个员工的工资等级

idle> select ename,sal,grade from emp a,salgrade b where sal between losal and hisal;

ENAME SAL GRADE

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

SMITH 800 1

JAMES 950 1

ADAMS 1100 1

WARD 1250 2

MARTIN 1250 2

MILLER 1300 2

TURNER 1500 3

ALLEN 1600 3

CLARK 2450 4

BLAKE 2850 4

JONES 2975 4

SCOTT 3000 4

FORD 3000 4

KING 5000 5

14 rows selected.

idle>

练习: emp dept salgrade

1.查询名字是S开头的员工名,员工号,部门号,部门名,部位地理位置

select ename,empno,e.deptno,dname,loc

from dept d,emp e

where

d.deptno=e.deptno

and

ename like 'S%'

2.查询员工名,部门名,工资,工资等级

select ename,dname,sal,grade

from emp e,dept d,salgrade s

where

e.deptno=d.deptno

and

sal >= s.losal

and

sal <= s.hisal;

3.查询工作在NEW YORK的所有员工

select empno,ename,d.deptno,dname,loc

from emp e,dept d

where

e.deptno=d.deptno

and

loc='NEW YORK';

自连接

同一张表内的连接查询即把一个表映射成两个表

主要用于表的自参照关系比如emp中的上下级或层次关系

因为自连接是同一张表之间的链接查询所以必须定义表别名

例子:根据empno和mgr的对应关系,可以查找出KING的下属都有谁

idle> select b.ename||'''s manager is '||a.ename from emp a,emp b where a.empno = b.mgr and a.ename='KING';

B.ENAME||'''SMANAGERIS'||A.ENAME

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

BLAKE's manager is KING

JONES's manager is KING

CLARK's manager is KING

idle>

外链接

不仅返回满足连接的记录还会返回不满足连接的记录

外链接的运算符(+)

该符号可以放在等的左边或右边,但一定要放在缺少信息(完全满足条件的行)的一边.

若加在多信息的一边和没加一样

例如:emp中不包含40号部门的员工而dept中有40号部门

idle> select empno,ename,b.deptno,loc from emp a,dept b where a.deptno(+) = b.deptno;

EMPNO ENAME DEPTNO LOC

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

7782 CLARK 10 NEW YORK

7839 KING 10 NEW YORK

7934 MILLER 10 NEW YORK

7566 JONES 20 DALLAS

7902 FORD 20 DALLAS

7876 ADAMS 20 DALLAS

7369 SMITH 20 DALLAS

7788 SCOTT 20 DALLAS

7521 WARD 30 CHICAGO

7844 TURNER 30 CHICAGO

7499 ALLEN 30 CHICAGO

7900 JAMES 30 CHICAGO

7698 BLAKE 30 CHICAGO

7654 MARTIN 30 CHICAGO

40 BOSTON

15 rows selected.

idle>

查询出所有部门名和10号部门内的员工

因为员工信息显示较少所以(+)符号放在emp.deptno上

idle> select b.deptno,loc,empno,ename from emp a,dept b where a.deptno(+) = b.deptno and a.deptno(+)=10;

DEPTNO LOC EMPNO ENAME

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

10 NEW YORK 7782 CLARK

10 NEW YORK 7934 MILLER

10 NEW YORK 7839 KING

20 DALLAS

30 CHICAGO

40 BOSTON

6 rows selected.

idle>

外连接的注意事项

1.where中有多个条件时必须每个条件都需要使用(+)符号

2.(+)只使用于列不能是表达式

3.(+)不能与in or一起操作

SQL 1999 查询方法

语法:

select table1.column_name,table2.column_name from table1

[cross join table2| natural join table2] | [join table2 using (column_name)]

| [join table2 on(table1.column_name = table2.column_name)]

| [left|right|full outer join table2 on(table1.column_name = table2.column_name)];

t1 cross join t2 用于返回笛卡尔集

t1 natural join t2 用于基于同名列执行等连接

t1 join t2 using (column_name) 用于基于特定列执行等连接

t1 join t2 on(table1.column_name = table2.column_name) 用于指定连接条件

t1 left outer join t2 on(col1=col2) 用于左连接

t1 right outer jion t2 on(col1=col2) 用于右连接

t1 full join t2 on(col1=col2) 用于完全连接

idle> select * from emp cross join dept;

...

56 rows selected.

等同于select * from emp,dept;

natural join

两个表要有共有列,并且数据类型一致

如果两个表的共有列不只一个 natural join会按所有共有列连接

idle> select * from emp natural join dept;

DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC

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

10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 ACCOUNTING NEW YORK

10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING NEW YORK

10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 ACCOUNTING NEW YORK

20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 RESEARCH DALLAS

20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 RESEARCH DALLAS

20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 RESEARCH DALLAS

20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 RESEARCH DALLAS

20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 RESEARCH DALLAS

30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 SALES CHICAGO

30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 SALES CHICAGO

30 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 SALES CHICAGO

30 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 SALES CHICAGO

30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 SALES CHICAGO

30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 SALES CHICAGO

14 rows selected.

idle> 等同于 idle> select * from emp,dept where emp.deptno=dept.deptno;

单oracle的写法会将同名列显示两次而自然连接则对同名列只显示一次

USING 子句

注意事项:

1.natural join 和 using子句互斥不能同时使用

2.所引用的列不能使用表明前缀

3.被引用的列一定是两个表的共有列

4.多列相同时只能选择一列

SCOTT@ora10g> select * from emp join dept using (deptno);

DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC

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

20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS

30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO

30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO

20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS

30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO

30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO

10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK

20 7788 SCOTT ANALYST 7566 19-APR-87 3000 RESEARCH DALLAS

10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK

30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO

20 7876 ADAMS CLERK 7788 23-MAY-87 1100 RESEARCH DALLAS

30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO

20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS

10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK

14 rows selected.

SCOTT@ora10g> 等同于 select * from emp,dept where emp.deptno=dept.deptno;

on 子句

注意事项

SCOTT@ora10g> select * from emp join dept on emp.deptno=dept.deptno ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC

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

7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALESCHICAGO

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALESCHICAGO

7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCHDALLAS

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALESCHICAGO

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALESCHICAGO

7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTINGNEW YORK

7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCHDALLAS

7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTINGNEW YORK

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO

7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS

7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO

7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCHDALLAS

7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK

14 rows selected.

SCOTT@ora10g> 等同于 select * from emp,dept where emp.deptno=dept.deptno;

多条件用and连接或再添加where子句

SCOTT@ora10g> select * from emp join dept on emp.deptno=dept.deptno and emp.deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC

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

7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTINGNEW YORK

7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTINGNEW YORK

7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK

SCOTT@ora10g>

SCOTT@ora10g> select * from emp join dept on emp.deptno=dept.deptno where emp.deptno=10 order by 1 desc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC

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

7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK

7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTINGNEW YORK

7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTINGNEW YORK

SCOTT@ora10g>

左外连接

满足连接条件的数据,以及不满足连接条件的左边表的其他数据

SCOTT@ora10g> select * from dept left outer join emp on emp.deptno=dept.deptno;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-DEC-80 800 20

30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-APR-81 2975 20

30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81 5000 10

30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

30 SALES CHICAGO 7900 JAMES CLERK 7698 03-DEC-81 950 30

20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20

10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82 1300 10

40 OPERATIONS BOSTON

15 rows selected.

SCOTT@ora10g>

右外链接

满足连接条件的数据,以及不满足连接条件的右边表的其他数据

SCOTT@ora10g> select * from emp right outer join dept on emp.deptno=dept.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC

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

7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALESCHICAGO

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALESCHICAGO

7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCHDALLAS

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALESCHICAGO

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALESCHICAGO

7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTINGNEW YORK

7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCHDALLAS

7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTINGNEW YORK

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO

7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS

7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO

7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCHDALLAS

7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK

40 OPERATIONSBOSTON

15 rows selected.

左外和右外其实得到的结果是一致的,只是写法上不同左外是列中数据多的表在前,反之依然

等同于: select * from emp,dept where emp.deptno(+) = dept.deptno;

全连接

返回连接条件的数据以及不满足连接条件的左边表和右边表的其他数据

SCOTT@ora10g> select * from dept full join emp on dept.deptno=emp.deptno and emp.deptno=10;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81 5000 10

10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82 1300 10

30 SALES CHICAGO

40 OPERATIONS BOSTON

20 RESEARCH DALLAS

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

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

17 rows selected.

SCOTT@ora10g>