William Benson (VBACreations.Com)
vbacreations at gmail.com
Mon Oct 3 14:20:49 CDT 2011
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])