I'm not surprised, Ian. In doing a left join, you tell Firebird to include
rows of U whether or not there is a matching record in A. Then it is
contradictory to use A in the where clause. Hence, your first example is
the way to code, whereas I would never trust your second statement. Though
I think giving a syntax error would have been more appropriate than to
yield unpredictable results.

Set

At 12:49 16.07.2003 +0000, you wrote:

>Hi Folks,
>
>Should the following two sql statements produce the same result
>(they don't)
>
>select
> a.ordering,
> u.*
>from unit u
>left join my_mimic_asset a
> on a.mimic_id = '0NUmDq*4Ibdl4m5R5VdG*W'
>
>select
> a.ordering,
> u.*
>from unit u
>left join my_mimic_asset a
>where a.mimic_id = '0NUmDq*4Ibdl4m5R5VdG*W'
>
>The only difference is the location of the and a.mimic_id = '..'
>The first one works as expected, the second returns no rows.