The characteristics of the Union, the weight and not the weight

The characteristics of the Union, the weight and not the weight


Set operation has and, intersection, difference 3 kinds of operations.


Union: Gets the set of two query results and automatically removes duplicate rows. Not sorted


UNION ALL: Gets the set of two query results and does not remove duplicate rows. It's not sorted.


Intersect: Gets the intersection of two query results and sorts by the first column of the result set


Minus: Get two reduction of query result, sort in first column


Example:
Here are two tables: one major curriculum, one elective course table.
This is major curriculum: minors
CREATE TABLE Minors (
MINOR_ID Number primary Key,
Minor_name VARCHAR2 (+) NOT NULL,
Credit_hour Number (2)
)
Insert 3 record:
INSERT into minors values (10101, ' Principles of Computer ', 4)
INSERT into minors values (10201, ' Principle of automatic control ', 3)
INSERT into minors values (10301, ' engineering charting principle ', 4)
The following is the creation of the electives table Minors2
CREATE TABLE Minors2 (
MINOR_ID Number primary Key,
Minor_name VARCHAR2 (+) NOT NULL,
Credit_hour Number (2)
)
Insert two record:
INSERT into minors2 values (10201, ' Principle of automatic control ', 3)
INSERT into minors2 values (10301, ' engineering charting principle ', 4)


(1)
Two tables use UNION ALL: to get the following results
Select Minor_id,minor_name,credit_hour from Minors UNION ALL
Select Minor_id,minor_name,credit_hour from Minors2 ORDER by Credit_hour
Results:
minor_id Minor_name Credit_hour
---------- ------------------------------ -----------
10201 Automatic Control Principle 3
10201 Automatic Control Principle 3
10101 Computer Principles 4
10301 Engineering Drawing Principle 4
10301 Engineering Drawing Principle 4


(2)
Two tables use union: get the following results
Select Minor_id,minor_name,credit_hour from Minors Union
Select Minor_id,minor_name,credit_hour from Minors2 ORDER by Credit_hour
Results:
minor_id Minor_name Credit_hour
---------- ------------------------------ -----------
10201 Automatic Control Principle 3
10101 Computer Principles 4
10301 Engineering Drawing Principle 4


(3)
Two tables using intersect: Get the following results
Select Minor_id,minor_name,credit_hour from Minors intersect
Select Minor_id,minor_name,credit_hour from Minors2
Results:
minor_id Minor_name Credit_hour
---------- ------------------------------ -----------
10201 Automatic Control Principle 3
10301 Engineering Drawing Principle 4


(4)


Two tables using minus: get the following results
Select Minor_id,minor_name,credit_hour from Minors minus
Select Minor_id,minor_name,credit_hour from Minors2
Results:
minor_id Minor_name Credit_hour
---------- ------------------------------ -----------
     10101 computer principles                         &NBSP ;     4