With JOIN the optimizer is free to choose which tuple to use first in the plan, with a LEFT JOIN this choice does not exist. Is that bad? Well, if all your criteria in the query refers to the left table (which would be the typical case for a LEFT JOIN), then my guess is that it probably is fine (though I haven't thought it thoroughly through). However, if you do have a query like:

SELECT *
FROM POPULATION P
LEFT JOIN COUNTRY C ON P.BIRTHCOUNTRY = C.ID
WHERE P.BIRTHCOUNTRY IS NULL OR C.POPULATION < 1 000 000

and you know the birthplace for 99.9% of the population, then I'd expect a design where you have a dummy record in COUNTRY with unknown POPULATION and where you could use

SELECT *
FROM POPULATION P
JOIN COUNTRY C ON P.BIRTHCOUNTRY = C.ID
WHERE C.POPULATION < 1 000 000 or C.POPULATION is NULL

as a replacement for the above query, to be considerably quicker.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Sergio H. Gonzalez
Sent: 14. oktober 2009 19:21
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] very basic questions about FK

> Why you don't want to let field to be null AND _use_ foreign key?

Another thing... I seem to remember that I read somewhere that when you do JOINS
is better -for performance- to always having a corresponded field in the join
table than using LEFT JOINS.

Of course... know I have the strong feeling that I was wrong again... wasn't I ?

-s