[AccessD] Access 2007 Generated SQL "Join" Question

Brad,
>> Does it make any difference which approach is used?
No 
>> Are there performance implications when the tables contain 
>> large numbers of records?
No
The first approach is often called "old style join" - and there is absolutely no difference as to result and performance.
The reason for using the second "new style join" is:
1) It is easier to write an outer (left, right or full) join using the "new style" JOIN operator that using the "old style" WHERE clause.
2) You can't as easily get an accidental cross-join using the "new style" (you have to explicit write CROSS JOIN for this to happen) - whereas you can accidentally get a cross-join using the "old style" if you forget the where clause. And accidental cross-joins have laid down several servers through the ages...

Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Brad Marks
Sendt: 4. juni 2010 21:32
Til: Access Developers discussion and problem solving
Emne: [AccessD] Access 2007 Generated SQL "Join" Question

I have a question regarding the SQL that is generated by Access 2007.
To ask this question, I put together this simple example. 

 

Two small test tables were set up.   

 

Customer Table has two fields; Cust_Name and Cust_State

Example data  

John Doe

NY

 

 

State Table has two fields; State_Code and State_Name

 

Example data

NY

New York

 

 

As you can see, Cust_State and State_Code is the "common" field

~ ~ ~ ~

Using Access 2007 Query Design View with "Criteria" for State_Code =
[tbl_Customers].[Cust_State]

generates this SQL -

 

SELECT tbl_Customers.Cust_Name, tbl_Customers.Cust_State,
tbl_State.State_Code, tbl_State.State_Name

FROM tbl_Customers, tbl_State

WHERE (((tbl_State.State_Code)=[tbl_Customers].[Cust_State]));

 

~ ~ ~ ~

Using Query Design View and using "Join Properties" (I picked Option 1
in the join properties).  There is now line connecting the two tables in
the upper portion of the Query Design View.  Here is the SQL that is
generated from this approach. 

 

SELECT tbl_Customers.Cust_Name, tbl_Customers.Cust_State,
tbl_State.State_Code, tbl_State.State_Name

FROM tbl_Customers INNER JOIN tbl_State ON tbl_Customers.Cust_State =
tbl_State.State_Code;

 

~ ~ ~ ~

Even though the generated SQL is different, both approaches produce the
same end results.

Does it make any difference which approach is used?

The first approach is "joining" the two tables, even though we don't see
the word "join" in the generated SQL.

Are there performance implications when the tables contain large numbers
of records?   

 

Thanks,

Brad

~ ~ ~ ~ ~ ~ ~

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com