[AccessD] Access 2007 Generated SQL "Join" Question

The first design is called a cartesian join.

The second join is a full inner join, this is the correct way to do it.

For your simple join of the two tables, they both work (and probably
perform) the same way,
but with large tables of many records the second would perform faster.

David

On Fri, Jun 4, 2010 at 12:32 PM, Brad Marks <BradM at blackforestltd.com> wrote:
> 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
>