集合联合查询
集合
使用前提：两个集合必须有相同的列数,相同的列属性(数据类型,长度).
| 集合 | 关键字 | 五种运算 | 描述 |
|---|---|---|---|
| 并集 | union all | A union all B | 取两个集合中所有的元素,不去除重复元素 |
| union | A union B | 取两个集合中的所有元素,去掉重复元素 | |
| 交集 | intersect | A intersect B | 取两个集合重复部分的元素 |
| 差集合 | minus | A minus B | A集合中的元素减去两个集合交叉部分的元素 |
| B minus A | B集合中的元素减去两个集合交叉部分的元素 |
union 显示两个集合的合集,并去掉重复部分,并按照从小到大自动排序;
SQL> select * from test1
2 union
3 select * from test2;
ID NAME
---------- ------
1 A
2 B
3 C
4 D
union all 显示两个集合的合集,不去重
SQL> select * from test1
2 union all
3 select * from test2;
ID NAME
---------- ------
1 A
2 B
3 C
1 A
2 B
4 D
minus:显示差集,显示第一个集合的数据去掉两个集合的合集部分
SQL> select * from test1
2 minus
3 select * from test2;
ID NAME
---------- ------
3 C
SQL> select * from test2
2 minus
3 select * from test1;
ID NAME
---------- ------
4 D
intersect:显示两个集合的交集
SQL> select * from test1
2 intersect
3 select * from test2;
ID NAME
---------- ------
1 A
2 B
关联查询
定义: 当需要的数据在两张表或者多张表中时,需要用到关联查询
语法:关联查询使用到多张表时,from之后的表名用逗号隔开, where条件中必须有两个表关联的条件,实现关系传递.
准备数据
create table TEST1
(id number(5),
name varchar2(3)
);
insert into TEST1 values(1,'A');
insert into TEST1 values(2,'B');
insert into TEST1 values(3,'C');
commit;
create table TEST2 as select * from TEST1;
update TEST2 set ID=4, name='D' where id=3;
commit;
SQL> select * from test1;
ID NAME
---------- ------
1 A
2 B
3 C
SQL> select * from test2;
ID NAME
---------- ------
1 A
2 B
4 D
1内连接
根据条件将两张表的数据连接起来
如果没有符合条件的数据都会被过滤掉(会过滤掉两个表中的部分数据)
关键字:inner join
inner是可以省略的
SQL> select *
2 from test1 t1 inner join test2 t2
3 on t1.id=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
SQL> select *
2 from test1 t1 join test2 t2
3 on t1.id=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
也可以写为
SQL> select *
2 from test1 t1,test2 t2
3 where t1.id=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
查找员工编号为7521的人所在部门的全部信息
emp,dept
SQL> select * from dept a join emp b
2 on a.deptno=b.deptno
3 and b.empno=7521;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------------------------- -------------------------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-2 月 -81 1250 500 30
SQL> select *
2 from dept a, emp b
3 where b.empno = 7521
4 and a.deptno = b.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------------------------- -------------------------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-2 月 -81 1250 500 30
2自连接
内连接的一种特殊形式,没有自己的关键字
一张表和它自己进来连接
查询员工姓名并查询员工的上级名称
SQL> select p1.ename,p2.ename
2 from emp p1 join emp p2
3 on p1.mgr=p2.empno;
ENAME ENAME
-------------------- --------------------
FORD JONES
SCOTT JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
也可以写为
SQL> select p1.ename,p2.ename
2 from emp p1,emp p2
3 where p1.mgr=p2.empno;
ENAME ENAME
-------------------- --------------------
FORD JONES
SCOTT JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
3不等连接
连接条件是不等关系的连接
查询员工编号,姓名 并显示员工的工资等级
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select p1.empno,p1.ename,s1.grade
2 from emp p1 join salgrade s1
3 on p1.sal between s1.losal and s1.hisal;
EMPNO ENAME GRADE
---------- -------------------- ----------
7369 SMITH 1
7900 JAMES 1
7876 ADAMS 1
7521 WARD 2
7654 MARTIN 2
7934 MILLER 2
7844 TURNER 3
7499 ALLEN 3
7782 CLARK 4
7698 BLAKE 4
7566 JONES 4
7902 FORD 4
7788 SCOTT 4
7839 KING 5
查询比每个人工资低的人数
SQL> select p1.empno, p1.ename, p1.sal, count(*)
2 from emp p1 join emp p2
3 on p1.sal > p2.sal
4 group by p1.empno, p1.ename, p1.sal;
EMPNO ENAME SAL COUNT(*)
---------- -------------------- ---------- ----------
7566 JONES 2975 10
7698 BLAKE 2850 9
7521 WARD 1250 3
7654 MARTIN 1250 3
7876 ADAMS 1100 2
7782 CLARK 2450 8
7788 SCOTT 3000 11
7839 KING 5000 13
查看比员工: ALLEN工资低的员工信息
SQL> select p1.empno, p1.ename, p1.sal,
2 p2.empno, p2.ename, p2.sal
3 from emp p1 join emp p2
4 on p1.sal>p2.sal
5 where p1.ename='ALLEN';
EMPNO ENAME SAL EMPNO ENAME SAL
---------- ----------- ---------- ---------- ----------------- ----------
7499 ALLEN 1600 7369 SMITH 800
7499 ALLEN 1600 7521 WARD 1250
7499 ALLEN 1600 7654 MARTIN 1250
7499 ALLEN 1600 7844 TURNER 1500
7499 ALLEN 1600 7876 ADAMS 1100
7499 ALLEN 1600 7900 JAMES 950
7499 ALLEN 1600 7934 MILLER 1300
1全(外)连接
关键字:full outer join
outer:可以省略
-
会查出两张表的所有数据
-
根据关系列进行等值连接
-
如果一张表中的数据,根据连接条件在另一张表中找不到相应的数据时,他会在相应的位置显示为空
SQL> select t1.*,t2.*
2 from test1 t1 full join test2 t2
3 on t1.id=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
4 D
3 C
2左(外)连接
关键字:left outer join
outer:可以省略
-
左连接是把左表的所有数据查询出来
-
会根据连接条件去右表中找和它对应的数据
-
如果找到就连接为一条数据,如果找不到就只显示左表的数据.右表部分显示为空(左连接会过滤掉右表中的部分数据)
SQL> select t1.*,t2.*
2 from test1 t1 left join test2 t2
3 on t1.id=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
3 C
左连接的又一写法
SQL> select t1.*,t2.*
2 from test1 t1,test2 t2
3 where t1.id=t2.id(+);
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
3 C
3右(外)连接
-
右连接会查出所有右表的数据,根据连接条件去左表找对应的数据,如果找到就连接起来
-
如果找不到,就显示为空
-
右连接会过滤掉部分左表的数据
SQL> select t1.*,t2.*
2 from test1 t1 right join test2 t2
3 on t1.id=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
4 D
右连接的又一写法
SQL> select t1.*,t2.*
2 from test1 t1,test2 t2
3 where t1.id(+)=t2.id;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
2 B 2 B
4 D
自然连接和交叉连接
1自然连接
关键字natural join
-
自然连接会从两张表中找到列名相同的列进行等值连接
-
如果在两张表中找不到关系列值相等的数据,这样的数据会被过滤掉
SQL> select *
2 from test1 t1 natural join test2 t2;
ID NAME
---------- ------
1 A
2 B
2交叉连接
关键字:cross join
拿表1中的一条数据和表2的所有数据依次进行连接
然后再拿表1第二条数据和表2的所有数据依次进行连接
以此类推排查出所有结果
交叉连接的结果条数,是第一张表的数据条数,乘以第二张表的数据条数
SQL> select *
2 from test1 t1 cross join test2 t2;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
1 A 2 B
1 A 4 D
2 B 1 A
2 B 2 B
2 B 4 D
3 C 1 A
3 C 2 B
3 C 4 D
交叉连接又一写法
SQL> select *
2 from test1 t1,test2 t2;
ID NAME ID NAME
---------- ------ ---------- ------
1 A 1 A
1 A 2 B
1 A 4 D
2 B 1 A
2 B 2 B
2 B 4 D
3 C 1 A
3 C 2 B
3 C 4 D
补充all和any
后面跟一个集合或者子查询
all,any不能直接用等号,只能用<= | >=
| 选项 | 描述 |
|---|---|
| >all | 表示大于集合中最大的元素 |
| <all | 表示小于集合中最小的元素 |
| >any | 表示大于集合中最小的元素 |
| <any | 表示小于集合中最大的元素 |
查询员工信息要求,要求比下列工资都少(2000,1500,3000)
比列表中最少的还少
select * from emp where sal<all(2000,1500,3000);
