[AccessD] Query Question

    Could there be any particular reason for preferring a Cartesian join filtered by WHERE clause? Why not simply use an inner join as follows (T_1 and T_LkUp are the names of two tables)?

====================================
SELECT T_1.LookUpValue, T_LkUp.*  
FROM T_1 INNER JOIN T_LkUp ON (T_1.LookUpValue <= T_LkUp.RangeEnd)  AND (T_1.LookUpValue >=  T_LkUp.RangeStart);
====================================

A.D.Tejpal
------------

  ----- Original Message ----- 
  From: Stuart McLachlan 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, April 30, 2008 19:33
  Subject: Re: [AccessD] Query Question


  Not necessarily.  Jet's  Rushmore technology is pretty smart at optimising queries

  You can't see the optimised execution plan in Access, , but I set the tables up in  SQL Server with appropriate indexes and did an execution plan there.  (100 values in Table1, 3 in LKUP)

  In SQL Server ,my version uses a Table Scan on LKUP (which would normally be fairly 
  small)  and one Index Seek on Table1.   Drew's version uses an Index Scan on Table1 and two Index Seeks on LKUP.  

  Doing an Execution Plan on a Union of the two versions, my  version's sub-branch cost 
  0.0446 while Drew's cost 0.0598.  (34% more expensive)

  Cheers,
  Stuart

  On 30 Apr 2008 at 16:36, Charlotte Foust wrote:

  > But an Outer Join WHERE has to examine all records.  A subquery has
  > already filtered them down.
  > 
  > Charlotte Foust 
  > 
  > -----Original Message-----
  > From: accessd-bounces at databaseadvisors.com
  > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
  > McLachlan
  > Sent: Wednesday, April 30, 2008 4:01 PM
  > To: Access Developers discussion and problem solving
  > Subject: Re: [AccessD] Query Question
  > 
  > You can return both results and you don't need a subquery, just use an
  > Outer Join with
  > "Where":
  > 
  > SELECT Table1.LookupValue,Result1,Result2
  > FROM LKUP, Table1
  > WHERE (RangeStart<=LookupValue AND RangeEnd>=[LookupValue])
  > 
  > Cheers,
  > Stuart