Rene Churchill <[EMAIL PROTECTED]> wrote on 02/22/2005 09:21:29 AM:
> Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21
> running on Mac OS X 10.3.7
>
> I'm trying to compare two identical tables and find the rows
> that are new/modified. I can't use a timestamp column because
> the "new" table is constantly regenerated. So I'm using a large
> WHERE clause and the <=> operator to detect changes. (BTW, it
> would be very nice if there was a NULL safe not equal operator)
>
> I get empty sets returned when I use <=> in the WHERE clause,
> which seems wrong to me. However if I switch to a HAVING clause,
> it works as expected. Is this a bug or am I doing something
> funky here?
>
> Note, the testC table is used because in full query, the testA
> and testB tables will have 100k+ rows and I need to effeciently
> narrow the scope down to the ~250 rows that I'm interested in.
>
> Thanks,
> Rene
>
>
> Test Script:
>
> create table testA (id int, a int, b int, c int);
> create table testB (id int, a int, b int, c int);
> create table testC (id int);
> insert into testB values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
> insert into testC values(2),(3),(4);
>
>
> WHERE query:
>
> select testA.a, testB.a, testA.b, testB.b, testA.c, testB.c
> from testC
> left join testA on testC.id = testA.id
> left join testB on testC.id = testB.id
> where NOT ( testA.a <=> testB.a AND
> testA.b <=> testB.b AND
> testA.c <=> testB.c);
> Empty set (0.00 sec)
>
>
> HAVING query:
>
> select testA.a, testB.a, testA.b, testB.b, testA.c, testB.c
> from testC
> left join testA on testC.id = testA.id
> left join testB on testC.id = testB.id
> having NOT ( testA.a <=> testB.a AND
> testA.b <=> testB.b AND
> testA.c <=> testB.c);
>
> +------+------+------+------+------+------+
> | a | a | b | b | c | c |
> +------+------+------+------+------+------+
> | NULL | 2 | NULL | 2 | NULL | 2 |
> | NULL | 3 | NULL | 3 | NULL | 3 |
> | NULL | 4 | NULL | 4 | NULL | 4 |
> +------+------+------+------+------+------+
> 3 rows in set (0.00 sec)
>
>
>
> --
> Rene Churchill Specializing in Internet Databases,
> Astute Computing, LLC. Custom Programming and
> Phone: 802-244-5151 Webmaster support
> Fax: 802-244-5512
> http://www.astutecomputing.com
>
Have you considered moving your match/don't match conditions to your ON
clause? It's easy to detect non-matches if you search for a null value
where one is not supposed to be (as in a PK or FK column) in the OUTER
table of an OUTER JOIN.
Assuming that testB is your "new" table and testA has your "old" values. I
think that you want to put a list of IDs in testC (a good idea to minimize
work) and determine which rows of testB (that match the IDs in testC) do
not have matches in testA.
Here is how I would write the query. I would look for the matches
("equals" computes much faster than "not equals") then detect where the
matches didn't happen.
SELECT B.ID
FROM testC c
INNER JOIN testB b
on b.id = c.id
LEFT JOIN testA a
on a.id = b.id
AND a.a <=> b.a
AND a.b <=> b.b
AND a.c <=> b.c
WHERE a.id is null;
Let me know how it works.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
