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

Type	Scope	Severity	Activated by default	Minutes to fix	Tags
Code smell	BSL	Major	Yes	10	sql standard performance

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)

Snippets

Diagnostic ignorance in code

// BSLLS:FullOuterJoinQuery-off
// BSLLS:FullOuterJoinQuery-on

Parameter for config

"FullOuterJoinQuery": false