--- In firebird-support@yahoogroups.com, "Ann W. Harrison" wrote:

> Ann W. Harrison wrote:
> SELECT A.barcode, B.barcode
> FROM shops A LEFT JOIN shops B
> ON (A.barcode = > B.barcode
> AND B.shop_id = 2)
> WHERE A.shop_id = 1;
>
> If you think the ON clause is ugly, you could write the
> query like this:
>
> SELECT A.barcode, B.barcode
> FROM shops A LEFT JOIN shops B
> ON (A.barcode = > B.barcode)
> WHERE A.shop_id = 1 AND
> (B.shop_id = 2 or B.shop_id IS NULL);
>
> I doubt there would be any performance difference.

But Ann, what if there was a shop with shop_id NULL? Then I'd say the
latter query would return its barcode (provided it was less than
A.barcode), whereas the first would eliminate it and the result set be
different.

Admittedly, Damirs example didn't have any records with NULL, I just
thought it was worth mentioning so that people don't think these two
queries in general are equivalent when they involve fields that may be
NULL (alternatively - if I'm wrong - that my confusion may be corrected).

Set