At 10:37 PM 5/02/2006, you wrote:

>If I use FULL JOIN the result is same.

Try this:

SELECT A.barcode, B.barcode FROM shops A left JOIN shops B ON A.barcode =
B.barcode and A.shop_id = 1 and B.shop_id = 2

I know this has *something* to do with the distribution of the search
criteria when neither the search criterion (shop_id) nor the join
criterion (barcode) is a unique column so I think there is a "gotcha"
in there. Once the search has been applied, the left stream record
containing the match is the only one that matches the join criterion
and all of the others are eliminated. The same "problem" applies if
you do a right or full join, too, for the same reason.

By pushing the search criteria up into the join, it ensures that all
of the records are there when the join criteria are applied.

Something like that...if you're lucky, Ann or Arno will explain it in
more technical language. Arno fixed this in Fb 2, as I understand.

./heLen