I am not sure what is going on with your query. It would be helpful if
you could post the full query and the results you are seeing.
I have run some simple experiments with the INTERSECT operator and I
have seen the following behavior:
1) The default behavior of the INTERSECT operator is to remove
duplicates. That is, INTERSECT is equivalent to INTERSECT DISTINCT. This
conforms to the the ANSI 2003 spec, volume 2, section 7.13 (query
expression), syntax rule 6.
2) INTERSECT ALL returns duplicates, which looks correct to me.
Here's the simple experiment I ran:
ij> create table t( a int );
0 rows inserted/updated/deleted
ij> insert into t( a ) values
( 1 ), ( 2 ), ( 3 ), ( 4 );
4 rows inserted/updated/deleted
ij> insert into t select * from t;
4 rows inserted/updated/deleted
ij> select a from t
intersect
select a from t
;
A
-----------
1
2
3
4
4 rows selected
ij> select a from t
intersect all
select a from t
;
A
-----------
1
1
2
2
3
3
4
4
8 rows selected
ij> select a from t
intersect distinct
select a from t
;
A
-----------
1
2
3
4
4 rows selected
Hope this helps,
-Rick
Yun Chie wrote:
Hi all,
I have the following statement (very roughly):
SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers, Sales
...
INTERSECT
SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers, Sales
...
I just need to know what's the right syntax to retrieve only the distinct
results, as, the above statement retrieve also duplicates, in other words,
what is in derby the right syntax for the following statement:
SELECT Customer.Customer_UID, Sales.Sales_UID (
(SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers,
Sales ...)
INTERSECT
(SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers,
Sales ...)
)
thanks for your help.
