[AccessD] Optimizing query when select part requires a calculation

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
>