0

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. But I get a doubt if DISTINCT function is working properly for 1 TB full data of alphanumeric values?

First query

SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_1;
SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_2;

Difference of count is 3910

Second query

SELECT COUNT(*) DOC_ID FROM
(SELECT DISTINCT(DOC_ID) FROM TABLE_1
MINUS
SELECT DISTINCT(DOC_ID) FROM TABLE_2);

count is 0

Does DISTINCT work properly on alphanumeric DOC_ID, especially if my data is huge?

1
  • 1
    distinct is not a function. But yes, the distinct operator works correctly on for alphanumeric values. Your two queries are not counting the same things
    – user1822
    Mar 30, 2016 at 5:52

1 Answer 1

2

Your two queries are counting the same thing.

Consider this data:

create table table1 (doc_id varchar(20));
create table table2 (doc_id varchar(20));

insert into table1 values ('foo');
insert into table1 values ('foo');
insert into table1 values ('bar');
insert into table1 values ('bar');

insert into table2 values ('foo');
insert into table2 values ('foo');
insert into table2 values ('bar');
insert into table2 values ('bar');

count(distinct doc_id) returns 2 for both tables.

But the following returns no rows at all:

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;

So the count of that is of course zero.

If you change the sample data your second query still counts something different:

delete from table2;
insert into table2 values ('foo2');
insert into table2 values ('foo2');
insert into table2 values ('bar2');
insert into table2 values ('bar2');

count(distinct doc_id) still returns 2 for both tables, so the difference is still 0 between those two counts

But,

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;

Now only returns foo and bar because foo2 and bar2 are not in table1.

If you add another distinct value to table2:

insert into table2 values ('bar3');

The difference in the distinct count from both tables is 1 (2 distinct values in table1, 3 in table2), but the minus still returns the two distinct values from the first table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.