The main problem with your query is that it in most cases will produce the wrong result!

Suppose you had three records in tBOMIssue:

1
2
3

and three in tWastedChit:

3
4
5

Your original query would then produce this result:

1
1
1
2
2
2
3
3

since the only combination you eliminated would be the combination 3-3.

If you wanted the result set to be

1
2

you generally have two options, either use LEFT JOIN as Alexandre already has shown, or use

SELECT CAST(tI.iID AS CHAR(12)) AS ctBMRID, tI.CBK AS cBMRBk
FROM tBOMIssue tI
WHERE NOT EXISTS(SELECT 'Random content' FROM tWastedChit tW
WHERE tI.iID = tW.itBMRID)

There isn't much difference between using LEFT JOIN and NOT EXISTS (at least, I haven't noticed any when working with Fb 1.5 - I use both of them), they're both pretty quick unless tBOMIssue contains a lot of records or tWastedChit.itBMRID lacks an index.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bhavbhuti Nathwani
Sent: 15. juni 2009 09:19
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: To JOIN or not to JOIN that is the question

Hi Alexandre

Thanks for your quick response. Please correct me where I am wrong. My understanding of the WHERE clause is that it is client side filter or atleast it applies the filter after the dataset is created, sort of a step 2. So is it (as per my understanding) be efficient because it will create a set and then remove records, when I did not want the records in the first place.

Will what I have done (however unusual :)) have a bad effect on the performance of the query or load the server unnecessary?

I am open to do what you suggested but just want to clarify my understanding of what I have done.

Thanks and regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, Alexandre Benson Smith <iblist@...> wrote:
>
> Bhavbhuti Nathwani wrote:
> > Hi all
> >
> > I would like to bring in data from a table that does not have a corresponding FK in another table What I have currently done (see below) is to join with this another table and do a <> comparision. Is this fine or there is a more efficient way to do this.
> >
> > Thanks and regards.
> > Bhavbhuti
> >
> > SELECT CAST(tBOMIssue.iID AS CHAR(12)) AS ctBMRID,
> > tBOMIssue.CBK AS cBMRBk,
> > ... more fields
> > FROM tBOMIssue tBOMIssue
> > one join here...
> > JOIN tWastedChit
> > ON tBOMIssue.iID <> tWastedChit.itBMRID
> > ORDER BY tBMRDt, cBMRBk, iBMRNo
> >
>
> The usual method to do this is to use a LEFT OUTER JOIN and filter the
> result set to return only the NULL's of the "right" table
>
> SELECT CAST(tBOMIssue.iID AS CHAR(12)) AS ctBMRID,
> tBOMIssue.CBK AS cBMRBk,
> ... more fields
> FROM tBOMIssue tBOMIssue
> one join here...
> LEFT JOIN tWastedChit
> ON tBOMIssue.iID = tWastedChit.itBMRID
> ORDER BY tBMRDt, cBMRBk, iBMRNo
> WHERE
> tWastedChit.itBMRID is null
>
>
> see you !
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br