Guide to Oracle SQL (University at Buffalo Version)
Using the Set Operators
Set Operators
The "Set Operators" (UNION, UNION ALL, INTERSECT, and MINUS) are used to work with result sets from two (2) SELECT statements.
| Operator | Description |
|---|---|
| UNION | Returns a sorted and non-duplicate result set from two (2) queries. |
| UNION ALL | Returns a non-sorted and duplicates included result set of only the rows that occur in each query. |
| INTERSECT | Returns a sorted and non-duplicate result set from two (2) queries. |
| MINUS | Returns a sorted and non-duplicate result set from the first query that do no occur in the result set of the second query. |
Classifications and The Venn Diagram
Let's look at a company's employees and customers example. Both are members of the people classification (set). Employess can also be customers which are identified in the next figure where the inner circles (classifications) overlap. This is where they INTERSECT. The UNION of these three sets are all three (3) circles. The MINUS sets would be employees who are not customers and customer who are not employees.
Some Set Operator Principles
You must have two (2) or more SELECT queries to use a set operator. Each Select statement must have the same number or fields being returned in its result set. They do not have to have the same field names. The fields need to be of the same data type group (NUMBER/INTEGER, DATE/TIMESTAMP, for example). Since UNION, INTERSECT, and MINUS will sort the result set (and remove duplicates) you cannot have an ORDER BY clause in each SELECT statement. You can change the sorting by using a single ORDER BY at the end of the compound SELECT query.
SELECT last_name || ', ' || first_name AS "Employee", hire_date AS "Start Date", inactive_date AS "End Date" FROM employees UNION SELECT last_name || ', ' || first_name, hire_date, inactive_date FROM employees_history; Employee Start Dat End Date ----------------------------------------------- --------- --------- Felix, Allison 10-OCT-08 Hall, Darryl 10-OCT-08 Hall, Sarah 10-SEP-09 Jones, LoLo 10-SEP-12 Lindgren, Gerry 10-DEC-13 Oates, John 10-SEP-09 Scott, Steve 10-MAR-11 01-JUL-12 Taylor, James 10-MAR-09 Taylor, Livingston 01-SEP-99 Wertz, Charlie 01-SEP-89 10 rows selected.
UNION Set OperatorSELECT last_name || ', ' || first_name AS "Employee", hire_date AS "Start Date", inactive_date AS "End Date" FROM employees INTERSECT SELECT last_name || ', ' || first_name, hire_date, inactive_date FROM employees_history; Employee Start Dat End Date ----------------------------------------------- --------- --------- Hall, Darryl 10-OCT-08 Oates, John 10-SEP-09 2 rows selected.
INTERSECT Set OperatorSELECT last_name || ', ' || first_name AS "Employee", hire_date AS "Start Date", inactive_date AS "End Date" FROM employees MINUS SELECT last_name || ', ' || first_name, hire_date, inactive_date FROM employees_history; Employee Start Dat End Date ----------------------------------------------- --------- --------- Felix, Allison 10-OCT-08 Hall, Sarah 10-SEP-09 Jones, LoLo 10-SEP-12 Lindgren, Gerry 10-DEC-13 Scott, Steve 10-MAR-11 01-JUL-12 Taylor, James 10-MAR-09 Taylor, Livingston 01-SEP-99 Wertz, Charlie 01-SEP-89 8 rows selected.
MINUS Set OperatorSome Complex Set Operator Examples
If the SELECT statements do not have to have the same number of fields you can generate the additional fields using a NULL value. Remeber that the field data types must be in the same groups. So, if you are generating a string us TO_CHAR(NULL) and if you are generating a number us TO_NUMBER(NULL).
SELECT lastname || ', ' || firstname AS "Name", advisorid AS "Advisor" FROM student UNION ALL SELECT lastname || ', ' || firstname , TO_NUMBER(NULL) FROM faculty; Name Advisor ------------------------------------------ ---------- Thompson, Eric 916785 Smithy, Bob 326975 Dutch, Rita 120974 Alcott, Louisa Mae 396268 Azimov, Isaac 916786 cummings, ee 916786 Vonnegut, KURT 916786 Walker, Alice 916786 Singer, Isaac Bashevis 396268 Teddon, Don SMITH, Steve Name Advisor ------------------------------------------ ---------- Thomas, Becky Baryshinokov, Mikhail Mitchell, Arthur Alonzo, Alicia Fonteyn, Dame Margot Tudor, Antony Balanchine, George Graham, Martha 19 rows selected.
NULL FieldThe Order of a Complex Set Operator Matters
The order of your SELECT statements is important. SQL will execute the statements in the order they are presented. You can use parentheses, () around your SELECT queries to change this order of precedence.
SELECT lastname || ', ' || firstname AS "Name" FROM student UNION ALL SELECT lastname || ', ' || firstname FROM faculty MINUS SELECT lastname || ', ' || firstname FROM studenttable; Name ------------------------------------------ Alonzo, Alicia Balanchine, George Baryshinokov, Mikhail Fonteyn, Dame Margot Graham, Martha Mitchell, Arthur SMITH, Steve Singer, Isaac Bashevis Teddon, Don Thomas, Becky Tudor, Antony Name ------------------------------------------ Vonnegut, KURT 12 rows selected.
SELECT lastname || ', ' || firstname AS "Name" FROM student UNION ALL (SELECT lastname || ', ' || firstname FROM faculty MINUS SELECT lastname || ', ' || firstname FROM studenttable); Name ------------------------------------------ Thompson, Eric Smithy, Bob Dutch, Rita Alcott, Louisa Mae Azimov, Isaac cummings, ee Vonnegut, KURT Walker, Alice Singer, Isaac Bashevis Alonzo, Alicia Balanchine, George Name ------------------------------------------ Baryshinokov, Mikhail Fonteyn, Dame Margot Graham, Martha Mitchell, Arthur SMITH, Steve Teddon, Don Thomas, Becky Tudor, Antony 19 rows selected.
SELECT lastname || ', ' || firstname AS "Name" FROM student UNION ALL SELECT lastname || ', ' || firstname FROM faculty ORDER BY "Name; Name ------------------------------------------ Alcott, Louisa Mae Alonzo, Alicia Azimov, Isaac Balanchine, George Baryshinokov, Mikhail Dutch, Rita Fonteyn, Dame Margot Graham, Martha Mitchell, Arthur SMITH, Steve Singer, Isaac Bashevis Name ------------------------------------------ Smithy, Bob Teddon, Don Thomas, Becky Thompson, Eric Tudor, Antony Vonnegut, KURT Walker, Alice cummings, ee 19 rows selected.
ORDER BY Clause on a Complex Set Operation
