Home

Description

The rule "SQL statements should not join too many tables" states that SQL statements should be kept simple and should not join more than a few tables. This rule is important to follow because joining too many tables can lead to slower query performance, and can make the SQL statement more difficult to read and understand. Additionally, it can lead to data integrity issues if the tables are not properly joined. It is best to limit the number of tables joined in a single SQL statement to ensure that the query is efficient and the data is accurate.

Key Benefits

• Reduced Complexity: By avoiding joining too many tables, the complexity of the SQL statement is reduced, making it easier to read and debug.
• Improved Performance: Joining too many tables can have a negative impact on query performance, as the database engine has to process more data.
• Increased Reliability: By avoiding joining too many tables, the risk of errors due to incorrect joins is reduced, resulting in more reliable results.

Non-compliant Code Example

SELECT p.Name AS ProductName, v.Name AS VendorName, ad.State  --Non compliant code (Join tables are more then default defined limit 3)
FROM Production.Product p   
INNER JOIN Purchasing.ProductVendor pv ON p.ProductID = pv.ProductID  
INNER JOIN Purchasing.Vendor v ON pv.BusinessEntityID = v.BusinessEntityID  
INNER JOIN Purchasing.Address ad ON ad.Id = v.BusinessEntityID  
ORDER BY p.Name, v.Name, ad.State;

SELECT p.Name AS ProductName, v.Name AS VendorName, ad.State   --Non compliant code (Join tables are more then default defined limit 3)
FROM Production.Product p, Purchasing.ProductVendor pv,  Purchasing.Vendor v, Purchasing.Address ad 
Where p.ProductID = pv.ProductID  
AND pv.BusinessEntityID = v.BusinessEntityID  
AND ad.Id = v.BusinessEntityID  
ORDER BY p.Name, v.Name, ad.State;