Oracle union Union all intersect minus usage)

The set operation has three types of operations: Union, intersection, and difference. Union: returns the union of two query results and removes duplicate rows automatically. Union all is not sorted: the Union set of the two query results is obtained, and duplicate rows are not removed. Intersect will not be sorted either: Get the intersection of the two query results, and sort by the first column of the result set minus: Get the subtraction set of the two query results. The first column is used for sorting. Example: the following are two tables: one is a major curriculum and the other is an optional curriculum. This is the primary course: minors create table minors (minor_id Number Primary Key, minor_name varchar2 (30) not null, credit_hour number (2) insert 3 records: insert into minors values (10101, 'computer principle ', 4) insert into minors values (10201, 'automatic Control Principle', 3) insert into minors values (10301, 'engineering drawing principle ', 4) Create the elective course schedule minors2 create table minors2 (minor_id Number Primary Key, minor_name varchar2 (30) not null, credit_hour number (2) to insert two records: insert into minors2 values (10201, 'automatic control policy', 3) insert into minors2 values (10301, 'engineering drawing policy', 4) (1) two tables use Union all: the following result is obtained: Select minor_id, minor_name, credit_hour from minors Union all select minor_id, minor_name, credit_hour from minors2 order by credit_hour: minor_id minor_name credit_hour ---------- ------------------------------ ----------- 10201 Automatic Control Principle 3 10201 Automatic Control Principle 3 10101 Computer Principle 4 10301 Engineering Drawing Principle 4 10301 engineering drawing principle 4 (2) two tables use Union: the following result is displayed: Select minor_id, minor_name, credit_hour from minors Union select minor_id, minor_name, credit_hour from minors2 order by credit_hour: minor_id minor_name credit_hour ---------- -------------------------------- 10201 Automatic Control Principle 3 10101 Computer Principle 4 10301 engineering drawing principle 4 (3) Two tables use intersect: the following result is displayed: Select minor_id, minor_name, credit_hour from minors intersect select minor_id, minor_name, credit_hour from minors2 result: minor_id minor_name credit_hour ---------- automatic --------- 10201 Automatic Control Principle 3 10301 engineering drawing principle 4 (4) two tables use minus: the following result is displayed: Select minor_id, minor_name, credit_hour from minors minus select minor_id, minor_name, credit_hour from minors2. Result: minor_id minor_name credit_hour -------- ------------------------------ ----------- 10101 computer principle 4.

 from: http://www.douban.com/note/154030850/