Re: Delete with subquery deleting all records

Francisco Reyes wrote:
> 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.

You only looked at some of the records, not all of them, correct?

Ah, yes, you did say,
> I checked a number of them.

Your evaluation of a whole data set by manual examination of a small subset of
the returned results cannot be certain.

Did you try SELECT COUNT(*) to check if the queries differed in the size of
their returned result sets?

> 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.

You mean "as far as you did check". You still do not know the truth of your
assertion that the outer join returned only a subset of the records.

SELECT COUNT( DISTINCT export_messages.export_id )
FROM export_messages
LEFT OUTER JOIN exports ON (export_messages.export_id = exports.export_id);

vs.

SELECT COUNT( DISTINCT export_messages.export_id )
FROM export_messages;

will reveal.

--
Lew