Best practices
1.1. With PostgreSQL databases, the server performance might suffer if you use the FULL OUTER JOIN clause in your queries. The impact is more significant when the query contains a few of such clauses.
That's why it is not recommended that you use FULL OUTER JOIN in queries. Whenever possible, reconsider the query text to replace this clause with an equivalent.
For example, the query
SELECT ISNULL(SalesPlan.Products, ActualSales.Products) AS Products, ISNULL(SalesPlan.Sum, 0) AS SumPlan, ISNULL(ActualSales.Sum, 0) AS ActualSum FROM SalesPlan AS SalesPlan FULL JOIN ActualSales AS ActualSales BY SalesPlan.Products = ActualSales.Products
can be replaced with the following query that doesn't contain FULL OUTER JOIN:
SELECT TargetActualSales.Products AS Products, SUM(TargetActualSales.SumTarget) AS SumTarget, SUM(TargetActualSales.SumActual) AS SumActual FROM (SELECT SalesPlan.Products AS Products, SalesPlan.Sum AS SumPlan, 0 AS SumActual FROM SalesPlan AS SalesPlan UNION ALL SELECT ActualSales.Products, 0, ActualSales.Sum FROM ActualSales AS ActualSales) AS TargetActualSales
GROUP BY TargetActualSales.Products
1.2. You can ignore the above mentioned recommendation and use FULL OUTER JOIN only when other clauses cannot deliver the required result. Note that when you execute a query in PostgreSQL, 1C:Enterprise substitutes FULL OUTER JOIN with an equivalent clause. All query attributes, such as TOP, DISTINCT, and ORDER BY, remain intact. In this scenario, there is no need to get rid of FULL OUTER JOIN in your query.
|