Lew writes:
> Strange? Why? Did you expect a particular statistical distribution? Perhaps
The impression was that one query was returning everything.. and the other
only the records that did not exist in the one table.
> you were surprised by the extent of the situation, not thinking there could be
> 100 records that didn't match?
Surprised that the outer join actually did ONLY display records that did not
exist in the second table, even though I did not have a where clause to not
list the records with a NULL value.
> The unconstrained outer join is guaranteed to return every distinct value of
> export_messages.export_id, the LEFT table, by the definition of LEFT OUTER JOIN.
That is what I expected, BUT it only returned records that did NOT exist in
the second table. It did not, as far as I could check, return all records.
>
> DELETE FROM export_messages WHERE NOT EXISTS
> ( SELECT export_id FROM exports
> WHERE exports.export_id = export_messages.export_id
> );
That is what I ended up using.
It worked.
> DELETE FROM export_messages WHERE export_id NOT IN
> ( SELECT export_id FROM exports );
Will keep that one handy too for future reference.
I think I may have not explained properly what I think I was seeing..
The left outter join without the where clause seemed to return the right
data.. only rows that existed in one table, but not in the other.
I looked at a few hundred records and check a good deal of them manually.
The issue was that I used it as a subquery to delete it seemed to produce
the entire list (ie all records from both tables)..
It is possible, or even likely, that I did something wrong or had some form
of oversight.. After I got the suggestion with the better query (with the
where clause) I did not go back to test anymore.
Thanks for your feedback.