Oracle collection operations

Keywords:

1. UNION: UNION. All content is queried and displayed once again;

2. union all: UNION set. ALL content is displayed, including duplicate content;

3. INTERSECT: intersection. Only duplicate items are displayed;

4. MINUS: The difference set, which only shows that the other party does not have (it is related to the Order)

Example:

Create Table emp2 under scott, which only contains information about employees of 20 departments in emp:

Code: create table emp2 as select * from emp where deptno = 20;

Let's take a look at the differences between the emp and emp2 tables:

[Emp table structure and content]

 

[Emp2 table structure and content]

~ Verify UNION and UNION ALL

UNION: select * from emp UNION select * from emp2;/* use this statement. duplicate content is not displayed */

 

Union all: select * from emp union all select * from emp2;/* when this statement is used, repeated content is still displayed */

~ Verify INTERSECT

INTERSECT: select * from emp INTERSECT select * from emp2;/* use this statement to display only records that are repeated in two tables */

~ Verify MINUS

MINUS: select * from emp MINUS select * from emp2;/* use this statement to return the records showing the Differences */