db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: need help with distinct results
Date Fri, 27 Jun 2008 15:52:06 GMT
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.
>   


Mime
View raw message