FullOuterJoinQuery.md

Using of "FULL OUTER JOIN" in queries (FullOuterJoinQuery)

Description

You should not use FULL OUTER JOIN in queries, especially in PostgreSQL database. It is better to rewrite query without FULL OUTER JOIN.

Examples

Query below will lead to high load on PostgreSQL database.

Procedure Test1()

    Query = New Query;
    Query.Text = "SELECT
                   |    Goods.Product AS Product,
                   |    ISNULL(SalesPlan.Sum, 0) AS PlanSum,
                   |    ISNULL(SalesActual.Sum, 0) AS ActualSum
                   |FROM
                   |    Goods AS Goods
                   |        LEFT JOIN SalesPlan AS SalesPlan
                   |            FULL OUTER JOIN SalesActual AS SalesActual // Should trigger here
                   |            ON SalesPlan.Product = SalesActual.Product
                   |        ON Goods.Product = SalesPlan.Product";

EndProcedure

Sources

• Restricting the use of the "FULL OUTER JOINT" construct in queries (RU)
• Administrator's Guide: Peculiarities of Using PostgreSQL (RU)