Home

Description

The "Queries should not join too many tables" code rule states that when writing SQL queries, it is important to limit the number of tables that are joined in the query. Joining too many tables can lead to complex queries that are difficult to read, debug, and maintain. Additionally, joining too many tables can lead to decreased performance, as the query optimizer must process more data to return the desired result set. Therefore, it is recommended to limit the number of tables joined in a query to improve readability and performance.

Key Benefits

• Reduced complexity: Queries which join too many tables can be difficult to understand and manage, resulting in a more complex query.
• Improved performance: Joining too many tables can slow down query performance, as the database needs to process more data.
• Increased reliability: Queries which join too many tables can introduce additional complexity and potential errors.

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 AS p   
INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID  
INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID  
INNER JOIN Purchasing.Address AS 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 AS p, Purchasing.ProductVendor AS pv,  Purchasing.Vendor AS v, Purchasing.Address AS ad 
Where p.ProductID = pv.ProductID  
AND pv.BusinessEntityID = v.BusinessEntityID  
AND ad.Id = v.BusinessEntityID  
ORDER BY p.Name, v.Name, ad.State ;