"The man who doesn't read good books has no advantage over the man who can't read them."
- Mark Twain
1.Each query will have at least 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.Parentheses can change the order of Precedence
10.Duplicate rows are eliminated in the spool unless the ALLkeyword is used
SELECT * FROM Table_Red
INTERSECT
SELECT * FROM Table_Blue ;
In this example, what numbers in the answer set would come from the query above?
SELECT * FROM Table_Red
INTERSECT
SELECT * FROM Table_Blue ;
3
In this example, only the number 3 was in both tables so they INTERSECT.
SELECT * FROM Table_Red
UNION
SELECT * FROM Table_Blue ;
In this example, what numbers in the answer set would come from the query above?
SELECT * FROM Table_Red
UNION
SELECT * FROM Table_Blue ;
12345
Both of the top and bottom queries run simultaneously, then the two different spools files are merged to eliminate duplicates and then the remaining numbers are in the answer set.
SELECT * FROM Table_Red
UNION ALL
SELECT * FROM Table_Blue ;
In this example, what numbers in the answer set would come from the query above?
SELECT * FROM Table_Red
UNION ALL
SELECT * FROM Table_Blue ;
123345
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.
SELECT * FROM Table_Red
EXCEPT
SELECT * FROM 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?
SELECT * FROM Table_Red
EXCEPT
SELECT * FROM Table_Blue ;
12
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.
SELECT * FROM Table_Blue
MINUS
SELECT * FROM Table_Red ;
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!
SELECT * FROM Table_Blue
MINUS
SELECT * FROM Table_Red ;
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.
Will the result set be the same for both queries above?
Will both queries bring back the exact same result set? Check out the next page to find out.
Will the result set be the same for both queries above?
Yes
Both queries above are exactly the same to the system and produce the same result set.
Will the result set be the same for both queries above?
Will both queries bring back the exact same result set? Check out the next page to find out.
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.
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.
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 is responsible for ALIASING
The Bottom Query is responsible for sorting, but the ORDER BY statement must be a number, which represents column1, column2, column3, etc.
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.
SELECT Department_Name, Dept_No
FROM Department_Table
UNION ALL
SELECT Department_Name, Dept_No
FROM Department_Table
ORDER BY 1;
UNION eliminates duplicates, but UNION ALL does not.
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.