Oracle Set Operators

Description:

The set operators are used to combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

UNION Operator:

Select all distinct rows by either query.

Example:

SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Country Office" FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", 
state_province FROM locations;

Sample Output:

LOCATION_ID Department                     Country_Office
----------- ------------------------------ -------------------------
       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 Corporate 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 Shareholder 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,

50 rows selected.

UNION ALL Operator:

Select all rows by either query including all duplicates.

Sample table: regions

Example:

SELECT region_id  FROM regions
UNION ALL 
SELECT region_id  FROM countries
ORDER BY region_id;

Sample Output:

 REGION_ID
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         2
         2
         2
         2
         2
         2
         3
         3
         3
         3
         3
         3
         3
         4
         4
         4
         4
         4
         4
         4

29 rows selected.

INTERSECT Operator:

Both the queries select all the distinct rows.

Example:

SELECT 3 FROM DUAL
INTERSECT
SELECT 3f FROM DUAL;

Sample Output:

        3
----------
  3.0E+000

Example:

Sample table: regions

SELECT region_id  FROM regions
INTERSECT
SELECT region_id  FROM countries
ORDER BY region_id;

Sample Output:

 REGION_ID
----------
         1
         2
         3
         4

MINUS Operator:

All distinct rows selected by the first query but not the second

Sample table: countries

Sample table: locations

Example:

SELECT country_id  FROM countries
MINUS
SELECT country_id  FROM locations
ORDER BY country_id;

Sample Output:

COUNTRY_ID
---------------
AR
BE
DK
EG
FR
IL
KW
ML
NG
ZM
ZW

Previous: Hierarchical Query Operators
Next: Oracle Numeric Functions Introduction