Home

SQL JOIN conditions should involve all joined tables

Description

    The SQL JOIN condition rule states that when using JOINs in SQL, all tables involved in the JOIN must be specified in the JOIN condition. This ensures that the JOIN is performed correctly and that the results of the query are accurate. This rule also helps to prevent any unexpected results from occurring due to incorrect JOINs. By specifying all tables involved in the JOIN, the query will be more efficient and the results will be more accurate.

Key Benefits

  • Reduced complexity: By involving all joined tables in the SQL JOIN conditions, the complexity of the query is reduced, making it easier to read and understand.
  • Improved performance: By involving all joined tables in the SQL JOIN conditions, the query can be optimized for better performance.
  • Accurate results: By involving all joined tables in the SQL JOIN conditions, the query will return the most accurate results.

 

Non-compliant Code Example

SELECT p.Name AS ProductName, pv.Name AS VendorName 
FROM Production.Product p, Purchasing.ProductVendor pv;         --Non compliant code (SQL JOIN conditions not involve all joined tables)

Compliant Code Example

SELECT p.Name AS ProductName, pv.Name AS VendorName 
FROM Production.Product p, Purchasing.ProductVendor pv
Where p.Id = pv.Product_Id;         --Compliant code (SQL JOIN conditions involve all joined tables)
Visual Expert 2024
 VEPLSQLRULE33