[AccessD] Optimizing query when select part requires a calculation

Thank you. Yes a typo. They are not dentical the second one is 1b. They are
comparing different fields for null.

I guess I don't really understand join fields very well in a SQL statement
that joins tables on multiple fields.

I expect situations with sometimes table 1 and 2 both have a match on field
A but not on B. And other records in 1 and 2 that match field B but don't
match field A.

That was why my I wrote method 2. Yes it is nonsensical but I don't really
understand joins on more than one field.

Thanks for explanation regarding. IN.
On Oct 13, 2011 7:00 AM, "Arthur Fuller" <fuller.artful at gmail.com> wrote:

> It seems you made a copy+paste error in your message. 1a and 1b seem
> identical, even to the point of being both labelled 1a.
>
> Method 2 strikes me as nonsensical. How could a left join end up with nulls
> on the first table?
>
> As for option 3,
>
> Any IN() or NOT IN() expression forces a full table scan, regardless of the
> indexes available that might be more profitably used. Stay away from IN()
> unless there's no other alternative.
>
> HTH,
> Arthur
>
> On Mon, Oct 3, 2011 at 3:20 PM, William Benson (VBACreations.Com) <
> vbacreations at gmail.com> wrote:
>
> > Optimizing a  query that compares calculated result from each table. I
> have
> > 2 tables Tbl1 and Tbl2, each with the same two columns [A] and [B]; I
> > wanted
> > to ask Access what values from the combination of these two fields exist
> in
> > one but not the other. I used "|" as a separator. I want to know
> >
> > (1) Why method 1a and 1b are equivalent
> > (2) Why Method 2 is not required (ie, why can I test either item for Null
> > and it represents the combination is null, not the single field on its
> own)
> > (3) Why Method 3 takes hundreds of times longer
> >
> >
> >
> > 'METHOD 1a
> > SELECT [Tbl1].[A] & "|" & [Tbl1].[B] AS ResultX
> > FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.[B] = Tbl2.[B]) AND (Tbl1.[A] =
> Tbl2.[A])
> > WHERE Tbl2.A Is Null
> >
> > 'METHOD 1a
> > SELECT [Tbl1].[A] & "|" & [Tbl1].[B] AS ResultX
> > FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.[B] = Tbl2.[B]) AND (Tbl1.[A] =
> Tbl2.[A])
> > WHERE Tbl2.B Is Null
> >
> > 'METHOD 2
> > SELECT [Tbl1].[A] & "|" & [Tbl1].[B] AS ResultX
> > FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.[B] = Tbl2.[B]) AND (Tbl1.[A] =
> Tbl2.[A])
> > WHERE (Tbl2.A Is Null  OR Tbl2.B Is Null)    <--- Testing both seems not
> > necessary
> >
> > 'METHOD 3
> > Select [Tbl1].[A] & "|" & [Tbl1].[B] as ResultX
> > From [Tbl1]
> > Where
> >     [Tbl1].[A] & "|" & [Tbl1].[B] Not IN  (Select [Tbl2].[A] & "|" &
> > [Tbl2].[B] as ResultY From [Tbl2])
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>