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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
