多表查询
等连接
通过两个表具有相同意义的列,建立连接条件.
查询结果只显示两个列中的值是等值条件的行数据
表中同名列被选择时必须添加表名前缀进行修饰 否则无法确定这一列是属于哪个表
不等连接
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(*)
----------
14
idle> select count(*) from dept;
COUNT(*)
----------
4
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
SAL
COMM
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 子句
注意事项
1.
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 SALES
CHICAGO
7521 WARD SALESMAN
7698 22-FEB-81
1250 500
30
30 SALES
CHICAGO
7566 JONES MANAGER
7839 02-APR-81
2975
20
20 RESEARCH
DALLAS
7654 MARTIN SALESMAN
7698 28-SEP-81
1250 1400
30
30 SALES
CHICAGO
7698 BLAKE MANAGER
7839 01-MAY-81
2850
30
30 SALES
CHICAGO
7782 CLARK MANAGER
7839 09-JUN-81
2450
10
10 ACCOUNTING
NEW YORK
7788 SCOTT ANALYST
7566 19-APR-87
3000
20
20 RESEARCH
DALLAS
7839 KING PRESIDENT
17-NOV-81
5000
10
10 ACCOUNTING
NEW 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 RESEARCH
DALLAS
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 ACCOUNTING
NEW YORK
7839 KING PRESIDENT
17-NOV-81
5000
10
10 ACCOUNTING
NEW 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 ACCOUNTING
NEW YORK
7782 CLARK MANAGER
7839 09-JUN-81
2450
10
10 ACCOUNTING
NEW 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 SALES
CHICAGO
7521 WARD SALESMAN
7698 22-FEB-81
1250 500
30
30 SALES
CHICAGO
7566 JONES MANAGER
7839 02-APR-81
2975
20
20 RESEARCH
DALLAS
7654 MARTIN SALESMAN
7698 28-SEP-81
1250 1400
30
30 SALES
CHICAGO
7698 BLAKE MANAGER
7839 01-MAY-81
2850
30
30 SALES
CHICAGO
7782 CLARK MANAGER
7839 09-JUN-81
2450
10
10 ACCOUNTING
NEW YORK
7788 SCOTT ANALYST
7566 19-APR-87
3000
20
20 RESEARCH
DALLAS
7839 KING PRESIDENT
17-NOV-81
5000
10
10 ACCOUNTING
NEW 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 RESEARCH
DALLAS
7934 MILLER CLERK
7782 23-JAN-82
1300
10
10 ACCOUNTING
NEW YORK
40 OPERATIONS
BOSTON
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>