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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org