================================================================================ SQL statement (Executed with 4.26 Build 1) ================================================================================ Select Customerid as "Customer GUID", CustID as "Cust No", FullName as "FullName", LastName as "LastName", FirstName as "FirstName", MiddleInitial as "MiddleInitial", Address1 as "Address1", Address2 as "Address2", City as "City", StateOrProv as "StateOrProv", PostalCode as "PostalCode", Country as "Country", Phone as "Phone", PhoneExtension as "PhoneExtension", Phone2 as "Phone2", Phone2Extension as "Phone2Extension", Fax as "Fax", CellPhone as "CellPhone", Pager as "Pager", Email as "Email", NotActive as "NotActive", PrimaryContact as "PrimaryContact", CustSince as "CustSince", TermsID as "TermsID", GroupDiscountID as "GroupDiscountID", CustomerTypeID as "CustomerTypeID", TaxCode1 as "TaxCode1", TaxCode2 as "TaxCode2", TaxCode3 as "TaxCode3", Comments as "Comments", RefID as "RefID", OldCustID as "OldCustID", UserField1 as "UserField1", UserField2 as "UserField2", UserField3 as "UserField3", UserField4 as "UserField4", ARAccount as "ARAccount", NewOrModified as "NewOrModified", Salutation as "Salutation", NamePrefix as "NamePrefix", ShipToName as "ShipToName", ShipToAddress1 as "ShipToAddress1", ShipToAddress2 as "ShipToAddress2", ShipToCity as "ShipToCity", ShipToStateOrProv as "ShipToStateOrProv", ShipToPostalCode as "ShipToPostalCode", ShipToCountry as "ShipToCountry", NoFormat as "NoFormat", DiscPC as "DiscPC", DiscDays as "DiscDays", NetDays as "NetDays", COD as "COD", CurrencyID as "CurrencyID", CustomerNo as "CustomerNo", WebPage as "WebPage", Tax1ExemptionNo as "Tax1ExemptionNo", Tax2ExemptionNo as "Tax2ExemptionNo", Tax3ExemptionNo as "Tax3ExemptionNo", CreditLimit as "CreditLimit", TaxNo as "TaxNo", PrintStatement as "PrintStatement", AllowBackOrders as "AllowBackOrders", SalesManID as "SalesManID", CustomerMessage as "CustomerMessage", ARSubAccount1 as "ARSubAccount1", ARSubAccount2 as "ARSubAccount2", ARSubAccount3 as "ARSubAccount3", ReferredBy as "ReferredBy", NoEmail as "NoEmail", CustomerTypes.Description as "Category", GroupDiscounts.Description as "Group Discount", CustomerTerms.Description as "Terms", T1.Description as "Tax Code 1", T2.Description as "Tax Code 2", T3.Description as "Tax Code 3" From Customers LEFT OUTER JOIN CustomerTypes ON customers.customertypeid = CustomerTypes.CustomerTypeID LEFT OUTER JOIN GroupDiscounts ON customers.GroupDiscountID = GroupDiscounts.GroupDiscountID LEFT OUTER JOIN CustomerTerms ON Customers.TermsID = CustomerTerms.TermsID LEFT OUTER JOIN SalesTaxCodes as T1 ON Customers.TaxCode1 = T1.TaxCodeID LEFT OUTER JOIN SalesTaxCodes as T2 ON Customers.TaxCode2 = T2.TaxCodeID Left OUTER JOIN SalesTaxCodes as T3 On Customers.TaxCode3 = T3.TaxCodeID Order by CustID ascending Tables Involved --------------- Customers (Customers) table opened shared, has 4137 rows CustomerTypes (CustomerTypes) table opened shared, has 3 rows GroupDiscounts (GroupDiscounts) table opened shared, has 11 rows CustomerTerms (CustomerTerms) table opened shared, has 4 rows SalesTaxCodes (T1) table opened shared, has 5 rows SalesTaxCodes (T2) table opened shared, has 5 rows SalesTaxCodes (T3) table opened shared, has 5 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: Cust No ASC Join Ordering ------------- The driver table is the Customers table (Customers) The Customers table (Customers) is joined to the CustomerTypes table (CustomerTypes) with the LEFT OUTER JOIN expression: customers.customertypeid = CustomerTypes.CustomerTypeID The Customers table (Customers) is joined to the GroupDiscounts table (GroupDiscounts) with the LEFT OUTER JOIN expression: customers.GroupDiscountID = GroupDiscounts.GroupDiscountID The Customers table (Customers) is joined to the CustomerTerms table (CustomerTerms) with the LEFT OUTER JOIN expression: Customers.TermsID = CustomerTerms.TermsID The Customers table (Customers) is joined to the SalesTaxCodes table (T1) with the LEFT OUTER JOIN expression: Customers.TaxCode1 = T1.TaxCodeID The Customers table (Customers) is joined to the SalesTaxCodes table (T2) with the LEFT OUTER JOIN expression: Customers.TaxCode2 = T2.TaxCodeID The Customers table (Customers) is joined to the SalesTaxCodes table (T3) with the LEFT OUTER JOIN expression: Customers.TaxCode3 = T3.TaxCodeID Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: customers.customertypeid = CustomerTypes.CustomerTypeID is OPTIMIZED The expression: customers.GroupDiscountID = GroupDiscounts.GroupDiscountID is OPTIMIZED The expression: Customers.TermsID = CustomerTerms.TermsID is OPTIMIZED The expression: Customers.TaxCode1 = T1.TaxCodeID is OPTIMIZED The expression: Customers.TaxCode2 = T2.TaxCodeID is OPTIMIZED The expression: Customers.TaxCode3 = T3.TaxCodeID is OPTIMIZED ================================================================================ >>>>> 4137 rows affected in 0.687 seconds