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
