Chapter 29
Set Operators Functions
“Good advice is something a man gives when he is too old to set a bad example”
Table of Contents Chapter 29 – Set Operators Functions
– INTERSECT Explained Logically
– INTERSECT Explained Logically
– UNION ALL Explained Logically
– An Equal Amount of Columns in both SELECT List
– Columns in the SELECT list should be from the same Domain
– The Top Query handles all Aliases
– The Bottom Query does the ORDER BY (a Number)
– Great Trick: Place your Set Operator in a Derived Table
– Using UNION ALL and Literals
– A Great Example of how EXCEPT works
– USING Multiple SET Operators in a Single Request
– Changing the Order of Precedence with Parenthesis
– Using UNION ALL for speed in Merging Data Sets
– Using UNION to be same as GROUP BY GROUPING SETS
– Using UNION to be same as GROUP BY ROLLUP
– Using UNION to be the same as GROUP BY Cube
– Using UNION to be same as GROUP BY Cube
Rules of Set Operators
1. Each query will have two SELECT Statements separated by a SET Operator
2. SET Operators are UNION, INTERSECT, or EXCEPT/MINUS
3. Must specify the same number of columns from the same domain (data type/range)
4. If using Aggregates, both SELECTs much have their own GROUP BY
5. Both SELECTS must have a FROM Clause
6. The First SELECT is used for all ALIAS, TITLE, and FORMAT Statements
7. The Second SELECT will have the ORDER BY statement, which must be a number
8. When multiple operators the order of precedence is INTERSECT, UNION, and EXCEPT/MINUS
9. Parenthesis can change the order of Precedence
10. Duplicate rows are eliminated in the spool, unless the ALL keyword is used
INTERSECT Explained Logically
Table_Red Table Blue
In this example, what numbers in the answer set would come from the query above?
INTERSECT Explained Logically
Table_Red Table Blue
3
In this example, only the number 3 was in both tables so they INTERSECT.
UNION Explained Logically
Table_Red Table Blue
In this example, what numbers in the answer set would come from the query above?
UNION Explained Logically
Table_Red Table Blue
1 2 3 4 5
Both top and bottom queries run simultaneously, then the two different spools files are merged to eliminate duplicates and place the remaining numbers in the answer set.
UNION ALL Explained Logically
Table_Red Table Blue
In this example, what numbers in the answer set would come from the query above ?
UNION Explained Logically
Table_Red Table Blue
1 2 3 3 4 5
Both top and bottom queries run simultaneously, then the two different spools files are merged together to build the answer set. The ALL prevents eliminating Duplicates.
EXCEPT Explained Logically
Table_Red Table Blue
EXCEPT and MINUS do the exact same thing so either word will work!
In this example, what numbers in the answer set would come from the query above ?
EXCEPT Explained Logically
Table_Red Table Blue
1 2
The Top query SELECTED 1, 2, 3 from Table_Red. From that point on only 1, 2, 3 at most could come back. The bottom query is run on Table_Blue and if there are any matches they are not ADDED to the 1, 2, 3 but instead take away either the 1, 2, or 3.
Minus Explained Logically
Table_Red Table Blue
EXCEPT and MINUS do the exact same thing so either word will work!
What will the answer set be? Notice I changed the order of the tables in the query!
Minus Explained Logically
Table_Red Table Blue
4 5
The Top query SELECTED 3, 4, 5 from Table_Blue. From that point on only 3, 4, 5 at most could come back. The bottom query is run on Table_Red and if there are any matches they are not ADDED to the 3, 4, 5 but instead take away either the 3, 4, or 5.
Testing Your Knowledge
Table_Red Table Blue
SELECT * FROM
Table_Blue
EXCEPT
SELECT *
FROM Table_Red ;
SELECT *
FROM Table_Blue
MINUS
SELECT *
FROM Table_Red ;
Will the result set be the same for both queries above?
Both queries above are exactly the same to the system and produce the same result set.
Testing Your Knowledge
Table_Red Table Blue
SELECT *
FROM Table_Blue
EXCEPT
SELECT *
FROM Table_Red ;
SELECT *
FROM Table_Red
MINUS
SELECT *
FROM Table_Blue ;
Will the result set be the same for both queries above?
No! The first query returns 4, 5 and the query on the right returns 1, 2.
An Equal Amount of Columns in both SELECT List
You must have an equal amount of columns in both SELECT lists. This is because data is compared from the two spool files and duplicates are eliminated. So, for comparison purposes there must be an equal amount of columns in both queries.
Columns in the SELECT list should be from the same Domain
The above query works without error, but no data is returned. There are no First Names that are the same as Department Names. This is like comparing Apples to Oranges. That means they are NOT in the same Domain.
The Top Query handles all Aliases
The Top Query is responsible for ALIASING.
The Bottom Query does the ORDER BY (a Number)
The Bottom Query is responsible for sorting, but the ORDER BY statement must be a number, which represents column1, column2, column3, etc.
Great Trick: Place your Set Operator in a Derived Table
SELECT Employee_No AS MANAGER
,Trim(Last_Name) || ‘, ‘ || First_Name as “Name”
FROM Employee_Table
INNER JOIN
(SELECT Employee_No FROM Employee_Table
INTERSECT
SELECT Mgr_No FROM Department_Table)
AS TeraTom (empno)
ON Employee_No = empno
ORDER BY “Name”
| MANAGER | Name |
| 1256349 | Harrison, Herbert |
| 1333454 | Smith, John |
| 1000234 | Smythe, Richard |
| 1121334 | Strickling, Cletus |
The Derived Table gave us the empno for all managers and we were able to join it.
UNION Vs UNION ALL
SELECT Department_Name, Dept_No from Department_Table UNION ALL
SELECT Department_Name, Dept_No from Department_Table ORDER BY 1;
UNION Answer Set
| Department_Name | Dept_No |
| Customer Support | 400 |
| Human Resources | 500 |
| Marketing | 100 |
| Research and Development | 200 |
| Sales | 300 |
UNION ALL Answer Set
| Department_Name | Dept_No |
| Customer Support | 400 |
| Customer Support | 400 |
| Human Resources | 500 |
| Human Resources | 500 |
| Marketing | 100 |
| Marketing | 100 |
| Research and Development | 200 |
| Research and Development | 200 |
| Sales | 300 |
| Sales | 300 |
UNION eliminates duplicates, but UNION ALL does not.
Using UNION ALL and Literals
SELECT Dept_No AS Dept
,'Employee ' (TITLE ' ')
,First_Name || ' ' || Last_Name
as “Name”
FROM Employee_Table
UNION ALL
SELECT Dept_No
,'Department'
,Department_Name
FROM Department_Table
ORDER BY 1, 2 ;
| Dept | Name | |
| ? | Employee | Squiggy Jones |
| 10 | Employee | Richard Smythe |
| 100 | Department | Marketing |
| 100 | Employee | Mandee Chambers |
| 200 | Department | Research and Develop |
| 200 | Employee | Billy Coffing |
| 200 | Employee | John Smith |
| 300 | Department | Sales |
| 300 | Employee | Loraine Larkins |
| 400 | Department | Customer Support |
| 400 | Employee | Cletus Strickling |
| 400 | Employee | Herbert Harrison |
| 400 | Employee | William Reilly |
| 500 | Department | Human Resources |
Notice the 2nd SELECT column in that it is a literal ‘Employee ‘ (with two spaces) and the other Literal is ‘Department’. These literals match up because now they are both 10 characters long exactly. The UNION ALL brings back all Employees and all Departments and shows the employees in each valid department.
A Great Example of how EXCEPT works
SELECT Dept_No as Department_Number
FROM Department_Table
EXCEPT
SELECT Dept_No
FROM Employee_Table
ORDER BY 1 ;
| Department_Number |
| 500 |
This query brought back all Departments without any employees.
USING Multiple SET Operators in a Single Request
SELECT Dept_No , Employee_No as empno
FROM Employee_Table
UNION ALL
SELECT Dept_No, Employee_No
FROM Employee_Table
INTERSECT ALL
SELECT Dept_No, Mgr_No
FROM Department_Table
MINUS
SELECT Dept_No, Mgr_No
FROM Department_Table
WHERE Department_Name LIKE ‘%Sales%'
ORDER BY 1, 2;
| Dept_No | Empno | ||
| ? | 2000000 | ||
| 10 | 1000234 | ||
| 100 | 1232578 | ||
| 200 | 1324657 | ||
| 200 | 1333454 | ||
| 300 | 2312225 | ||
| 400 | 1121334 | ||
| 400 | 1256349 | ||
| 400 | 2341218 |
Above we use multiple SET Operators. They follow the natural Order of Precedence in that UNION is evaluated first, then INTERSECT, and finally MINUS.
Changing the Order of Precedence with Parenthesis
SELECT Dept_No , Employee_No as empno
FROM Employee_Table
UNION ALL
SELECT Dept_No, Employee_No
FROM Employee_Table
INTERSECT ALL
SELECT Dept_No, Mgr_No
FROM Department_Table
MINUS
SELECT Dept_No, Mgr_No
FROM Department_Table
WHERE Department_Name LIKE ‘%Sales%'))
ORDER BY 1, 2;
| Dept_No | Empno | ||
| ? | 2000000 | ||
| 10 | 1000234 | ||
| 100 | 1232578 | ||
| 200 | 1324657 | ||
| 200 | 1333454 | ||
| 300 | 2312225 | ||
| 400 | 1121334 | ||
| 400 | 1256349 | ||
| 400 | 1256349 | ||
| 400 | 2341218 |
Above we use multiple SET Operators and Parenthesis to change the order of precedence. Above the EXCEPT runs first, then the INTERSECT and lastly, the UNION. The natural Order of Precedence without parenthesis is UNION, INTERSECT, and finally EXCEPT or MINUS.
Using UNION ALL for speed in Merging Data Sets
Because the Combined_Custs table started empty there is no Transient Journal taking pictures for Rollback purposes so this dramatically increases the speed. This one transaction sees both SELECT statements run in parallel and then merge into one.
Using UNION to be same as GROUP BY GROUPING SETS
Using UNION to be same as GROUP BY ROLLUP
SEL Product_ID as PROD_ID
,EXTRACT(Year from Sale_Date) as Yr
,EXTRACT (Month from Sale_Date) as Mth
,SUM(Daily_Sales) as “Total” FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SEL Product_ID
,EXTRACT(Year from Sale_Date)
,NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT Product_ID
,NULL
,NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT NULL, NULL, NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 2, 3 ;
Using UNION to be the same as GROUP BY Cube
SEL Product_ID as PROD_ID
,EXTRACT(Year from Sale_Date) as Yr
,EXTRACT (Month from Sale_Date) as Mth
,SUM(Daily_Sales) as “Total”
FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SEL Product_ID
,NULL
,EXTRACT(Year from Sale_Date)
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT Product_ID
,NULL
,NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT NULL
,EXTRACT(Year from Sale_Date)
,EXTRACT (Month from Sale_Date)
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT NULL
,EXTRACT(Year from Sale_Date)
,NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT NULL, NULL
,EXTRACT (Month from Sale_Date)
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT NULL, NULL, NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 2 DESC, 3 DESC ;
Using UNION to be same as GROUP BY Cube
SEL Product_ID as PROD_ID
,EXTRACT(Year from Sale_Date) as Yr
,EXTRACT (Month from Sale_Date) as Mth
,SUM(Daily_Sales) as “Total” FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SEL Product_ID
,EXTRACT(Year from Sale_Date)
,NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT Product_ID
,NULL
,NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
UNION
SELECT NULL, NULL, NULL
,SUM(Daily_Sales) FROM Sales_Table
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 2, 3 ;
