List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:November 9 2011 2:56pm
Subject:Re: Issue With Subqueries
View as plain text  
Hi Mike,

On 11/8/2011 20:46, Mike Seda wrote:
> All,
> Can anyone out there explain the result of the third statement provided
> below:
>
> mysql> select count(distinct field1) from db1.table1;
> +------------------------+
> | count(distinct field1) |
> +------------------------+
> | 1063 |
> +------------------------+
> 1 row in set (0.01 sec)
>
> mysql> select count(distinct field1) from db2.table1;
> +------------------------+
> | count(distinct field1) |
> +------------------------+
> | 3516 |
> +------------------------+
> 1 row in set (0.03 sec)
>
> mysql> select count(distinct field1) from db2.table1 where field1 not in
> (select field1 from db1.table1);
> +------------------------+
> | count(distinct field1) |
> +------------------------+
> | 0 |
> +------------------------+
> 1 row in set (0.08 sec)
>
> A colleague of mine is stating that the result should be much greater
> than 0.
>
> Please let me know what you think.
>
> Thanks In Advance,
> Mike
>

Simple math (set theory) suggests that all of the values of field1 on 
db2.table1 contain only copies or duplicates of the field1 values in the 
rows in db1.table1.

Try this:

SELECT db2.field1, db1.field1
FROM db2.table1
LEFT JOIN db1.table1
   ON db2.field1 = db1.field1
WHERE db1.field1 IS NULL;

How many rows do you get back from that?

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Issue With SubqueriesMike Seda9 Nov
  • Re: Issue With SubqueriesMySQL)9 Nov
    • Re: Issue With SubqueriesJavier YĆ©venez9 Nov
      • Re: Issue With SubqueriesJohan De Meersman10 Nov
    • Re: Issue With SubqueriesJohan De Meersman10 Nov
Re: Issue With SubqueriesMike Seda9 Nov