Oracle set operator Union/Union all/intersect/minus usage

Set Operations
Set Operators
---- Merge
Union/Union all

---- Intersection
Intersect

----
Minus

Set Operation Specifications
-The select list of the Two Queries must match the number of queries.
-The data type of each column in the second query must match the data type of the corresponding column in the first query.
-Brackets can be used to change the execution sequence.
-Order by can or can only appear at the end of the entire statement.

Except Union all, the other three operators eliminate repeated rows in the result set.
The result set is displayed using the column name in the first query.
By default, except Union all, the output is sorted in ascending order.

Example Table:
Employees and job_history in HR Schema

Example:
---- Union returns to exclude duplicate records
Select employee_id, job_id
From employees
Union
Select employee_id, job_id
From job_history;

---- Union all: Repeated Records are returned and not sorted.
Select employee_id, job_id
From employees
Union all
Select employee_id, job_id
From job_history;
Order by employee_id;
---- The final order by is the order by for the result set of the entire Union all operation

---- Intersect returns the intersection
Select employee_id, job_id
From employees
Intersect
Select employee_id, job_id
From job_history;

Employee_id job_id
---------------------
176 sa_rep
200 ad_asst


---- Minus operator (intersection)
Minus returns all the distinct operations queried in the first query but not found in the second query.
Rows in the query result set.

Select employee_id
From employees
Minus
Select employee_id
From job_history;

---- Sometimes, the data field types of the columns in the two tables do not match, and the following processing can be performed:
Select location_id, department_name "department ",
To_char (null) "warehouse location"
From orders ments
Union
Select location_id, to_char (null) "department", state_province
From locations;

Location_id Department warehouse location
------------------------------------------------------------------
1000
1100
1200 Tokyo Prefecture
1300
1400 it
1400 Texas
1500 Shipping
1500 California
1600 New Jersey
1700 Accounting
1700 Administration
1700 benefits
1700 Construction
1700 Contracting
1700 control and credit
1700 sort ate tax
1700 Executive
1700 Finance
1700 Government sales
1700 IT helpdesk
1700 IT support
1700 Manufacturing
1700 NOC
1700 operations
1700 payroll
1700 purchasing
1700 recruiting
1700 Retail Sales
1700 registrant holder services
1700 Treasury
1700 Washington
1800 Marketing
1800 Ontario
1900 Yukon
2000
2100 Maharashtra
2200 New South Wales
2300
2400 Human Resources
2400
2500 sales
2500 Oxford
2600 Manchester
2700 Public Relations
2700 Bavaria
2800 Sao Paulo
2900 Geneve
3000 be
3100 Utrecht
3200 Distrito Federal,

-----------
Use Union/Union all/intersect/minus
Compound Query
The order by clause cannot be used separately for component queries in a combined query.
The order by clause can only appear at the end of the combined query.
The order by clause can only recognize columns in the first query.
By default, the first column of the First query is used to sort the output in ascending order.
Select location_id, department_name "department ",
To_char (null) "warehouse location"
From orders ments
Union
Select location_id, to_char (null) "department", state_province
From locations

Order by location_id; (the location_id here can only be the column name in the first query)

Please indicate the source and original link for reprinting; otherwise, the reprinting will be rejected.