Tt
Click this widget to change the font size.
CC
Click this widget to change contrast.

Home Page 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | Links | Search | Bio |

Guide to HTML, CSS, and JavaScript (Buffalo State College Version)


Creating a Multimedia Web Site

Set Operators

The "Set Operators" (UNION, UNION ALL, INTERSECT, and MINUS) are used to work with result sets from two (2) SELECT statements.

OperatorDescription
UNIONReturns a sorted and non-duplicate result set from two (2) queries.
UNION ALLReturns a non-sorted and duplicates included result set of only the rows that occur in each query.
INTERSECTReturns a sorted and non-duplicate result set from two (2) queries.
MINUSReturns a sorted and non-duplicate result set from the first query that do no occur in the result set of the second query.

Figure 9-1: SQL "Set" Operators

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.

Venn Diagram
Figure 9-2: A People/Employees/Customers Venn Diagram

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.

Figure 9-3: The UNION Set Operator
SELECT  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.

Figure 9-4: The INTERSECT Set Operator
SELECT  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.

Figure 9-5: The MINUS Set Operator

Some 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. 

Figure 9-6: Generating a NULL Field

The 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. 

Figure 9-7: Set Operator Order
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.

Figure 9-8: Changing Set Operator Order of Precedence
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. 

Figure 9-: The ORDER BY Clause on a Complex Set Operation