--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> As always, a very good and educative answer, Adam. I just think that
> the LEFT JOIN part is incorrect this time:
>
> > Using Left Join:
> >
> > select *
> > from clientes C
> > left join vendas v on (V.cli_cod=C.cli_cod)
> > where V.cli_cod IS NOT NULL
> > and V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31';
>
> To me this seems like an ordinary inner JOIN. Shouldn't it be
>
> select *
> from clientes C
> left join vendas v on (V.cli_cod=C.cli_cod
> and V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31')
> where V.cli_cod IS NULL
>

Guilty as charged. Well spotted.

> i.e. put all things that's part of the record that shouldn't exist in
> the JOIN clause, and then just the IS NULL in the WHERE clause to
> ascertain there ain't any such records.
>
> Myself, I find the NOT EXISTS solution to be the easiest to understand
> and quickest to execute, so I don't use IN (<subselect>) or LEFT JOIN
> ... WHERE <right table PK> IS NULL unless there is a very good reason.
>

I find the 'in' syntax the easiest to write because that is how I
think, but the exists is just as easy to read. The left join solution
would also work with your correction.

Adam