Brad Marks
BradM at blackforestltd.com
Tue Jun 8 16:37:34 CDT 2010
Asger, Thanks for your explanation and for your help. I appreciate it. Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Tuesday, June 08, 2010 4:30 PM To: 'Access Developers discussion and problem solving' Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.