Hi Mark,
I generally agree that the join condition should ideally only contain
columns for the join condition itself, but with outer joins (LEFT, RIGHT
and probably FULL), it may actually make a difference to the result.

Your original query:

SELECT te.email_address
FROM temp_email3 te
LEFT JOIN recipient re ON te.email_address = re.email_address
WHERE re.fk_client_id = :A_CLIENT_ID
AND re.email_address IS NULL

would not return any rows at all unless there actually was some records
with NULL in re.email_address. The reason is simple: Whatever you put in
the WHERE clause has to hold true for the result set (i.e. for te and re
combined), what you put in a LEFT JOIN clause only has to be true for
the RIGHT alias (re).

You made me a bit uncertain, so I translated both your and my statement
and tried them on a database on my computer, and whilst my statement
returned three rows, your statement returned nothing.

As for your earlier comment about me meaning NOT EXISTS in my very first
reply, well, no I didn't, but I didn't understand what Myles was looking
for, so my query was useless.

Set

Mark Rotteveel wrote:

> Svein Erling Tysvaer wrote:
>> Sorry, I didn't notice the last few replies to this thread where it is
>> better described what you want (my reply was directed towards your
>> initial email only, where it still wasn't evident what you were looking
>> for). Emb_blaster has already written an appropriate query, and Mark
>> almost got it right as well, the only thing missing is that the
>> parameter has to be in the JOIN clause:
>
> The position of that condition is more a matter of 'taste' and maybe of
> optimization (I am not sure how the optimizer of Firebird handles this),
> the end result of my query and the version below should be the same
> (although the execution time may differ). However semantically, the ON
> condition should only contain the columns used for the join, not any
> 'other' select-conditions.
>
>> SELECT te.email_address
>> FROM temp_email3 te
>> LEFT JOIN recipient re ON te.email_address = re.email_address
>> AND re.fk_client_id = :A_CLIENT_ID
>> WHERE re.email_address IS NULL
>>
>> Basically, you have two identically good solutions to choose from.