<snip>
ij> --
-- I expected 6,6,8,9 only
--
(select i from t1 union all select j from t2) except all (select k from t3);
1
-----------
1
1
2
3
6
6
8
9
8 rows selected
</snip>
Hi Rajesh:
The query result above returned from the system is correct. Let's take the query apart so we can visualize better:
Q1: (select i from t1 union all select j from t2)
Q2: select k from t3
Q1 will retain all rows from both table t1 and t2, so the result is: 1,2,3,4,9,1,3,2,1,6,8,6
Q2 result is: 5,2,3,4,1
Q1 EXCEPT ALL Q2, will have the result: 1, 1, 2, 3, 6, 6, 8, 9 because Q1 have three rows containing the value of 1, Q2 has only one, so the result should retain (3-1 or 2) rows containing the value of 1. Q1 have two rows containing the value of 2, Q2 has only one, so
the result should retain (2-1 or 1) row containing the value of 2 and so forth. Rows of Q1 that does not appear in Q2 will be retained in the final result as well.
Q1 EXCEPT Q2, will have the result: 6,8,9 because duplicates are eliminated from both
Q1 and Q2 before applying the EXCEPT operator:
Q1-nodups: 1,2,3,4,6,8,9
Q2-nodups: 1,2,3,4,5
So the result here is 6,8,9, which is also correct in your output. Now, let's try the INTERSECT testcase you listed, the output is also expected and correct:
<snip>
ij> --
-- Are these Wrong results, I expected 1,1,1,2,2,3,3,4
--
(select i from t1 union all select j from t2) intersect all ( select k from t3);
1
-----------
1
2
3
4
4 rows selected
</snip>
Again, using the results from above:
Q1: 1,2,3,4,9,1,3,2,1,6,8,6
Q2 : 5,2,3,4,1
Q1 INTERSECT ALL Q2 means "return all rows from Q1 that are also found in Q2". The result will be 1,2,3,4 because there are three rows containing the value of 1 in Q1 but Q2 only has one, so the result will only have one row containing the value of 1. There are two rows containing the value of 2 in Q1 but Q2 only has one, so the result will only have one row containing the value of 2 and so forth. (If Q2 happens to have 2 rows containing the value of 2, the final result will have 2 rows containing the value of 2). Rows that only appear in one of the two queries will not make it into the final result.
For Q1 INTERSECT Q2, Q1 and Q2 will first have their duplicates removed before
applying the INTERSECT operator:
Q1-nodups: 1,2,3,4,6,8,9
Q2-nodups: 1,2,3,4,5
So, the result is again 1,2,3,4. Hope this helps.
Yip
