oracle 里面的intersect 是什么意思？

INTERSECT，取两个表中的交集。

举例如下：

1、创建测试表，

create table test_tbl_1(id varchar2(20))

create table test_tbl_2(id varchar2(20))

2、插入测试数据，部分值含回车换行符；

insert into test_tbl_1 values(1)

insert into test_tbl_1 values(2)

insert into test_tbl_1 values(3)

insert into test_tbl_1 values(4)

insert into test_tbl_2 values(3)

insert into test_tbl_2 values(2)

insert into test_tbl_2 values(5)

insert into test_tbl_2 values(6)

commit

3、查询表中全量数据，可以发现部分值含回车换行符；select 1 as tbl, t.*, rowid from test_tbl_1 t union all select 2 as tbl, t.*, rowid from test_tbl_2 t

4、编写语句，使用INTERSECT，获取两表的交集；

 select * from test_tbl_1 INTERSECT select * from test_tbl_2

集合运算

         这是一种二目运算，一共有四种四种运算符：并，差，交，笛卡尔积；

语法：

         查询语句

                  [UNION | UNIONALL | INTERSECT | MINUS]

         查询语句

UNION(并集)

返回若干个查询结果，但是重复的不显示

Eg：SELECT *FROM dept

                  UNION

         SELECT*FROM dept WHERE deptno = 10

注：查询 *** 作编写的过程中尽量使用UNION , UNION ALL代替 OR，提高查询速度

例：

         查询工作是销售和clerk的；

         SELECT*

FROM emp WHEREjob = ‘saleman’ or job = ‘clerk’

另一种方式：

SELECT * FROMemp WHERE job = ‘SALESMAN’

         UNION

SELECT * FROMemp WHERE job = ‘CLERK’

UNION ALL(并集)

返回若干个查询结果，但是重复的也显示

Eg：SELECT * FROM dept

                  UNION ALL

         SELECT*FROM dept WHERE deptno = 10

MINUS(差集)

返回若干个结果中不同的部分；

Eg：SELECT * FROM dept

                   MINUS

         SELCT*FROM dept WHERE deptno = 10

INTERSECT(交集)

显示查询结果中相同的部分；

Eg：SELECT * FROM dept

                   INTERSECT

         SELCT*FROM dept WHERE deptno = 10

————————————————

A 并 B 去掉重复记录----union

select empno, ename ,salary ,deptno from employee_ccy where deptno=10

union

select empno, ename ,salary ,deptno from employee_ccy where salary>100

－－union all 不排序，不去重复

select empno, ename ,salary ,deptno from employee_ccy where deptno=10 union all

select empno, ename ,salary ,deptno from employee_ccy where salary>100

－－－交集-----intersect

select empno, ename ,salary ,deptno from employee_ccy where deptno=10

intersect

select empno, ename ,salary ,deptno from employee_ccy where salary>100

--差集--------minus

select empno, ename ,salary ,deptno from employee_ccy where deptno=10

minus

select empno, ename ,salary ,deptno from employee_ccy where salary>100

-------------用两个结果集的差集 ，获得

select deptno,dname ,location from department_ccy where deptno in(select deptno from department_ccy

minus

select distinct deptno from employee_ccy )

希望对你有帮助

---