Home

Description

The rule CROSS JOIN queries should not be used is a rule of thumb for PL/SQL code. It means that when writing a query, one should avoid using the CROSS JOIN operator. This operator is used to combine every row from one table with every row from another table, resulting in a Cartesian product. This can lead to a large number of unnecessary rows being returned, which can lead to performance issues. It is generally better to use other operators such as INNER JOIN or LEFT JOIN, which can be used to limit the number of rows returned.

Key Benefits

• No Cartesian Product : CROSS JOIN queries should not be used as they result in a Cartesian product, which can be a performance issue.
• No Filtering : CROSS JOIN queries do not provide any filtering of the data, which can lead to incorrect results.
• No Optimization : CROSS JOIN queries are not optimized by the query optimizer, which can lead to poor performance.

Non-compliant Code Example

SELECT p.Name AS ProductName, pv.Name AS VendorName  
FROM Production.Product p   
CROSS JOIN Purchasing.ProductVendor pv; --Non compliant code (Cross Join used in the query)

SELECT p.Name AS ProductName, pv.Name AS VendorName  
FROM Production.Product p, Purchasing.ProductVendor pv; --Non compliant code (Cross Join used in the query)