Skip to content

BSL191 — Using of "FULL OUTER JOIN" in queries

← All rules

Summary

Using of "FULL OUTER JOIN" in queries

Identifiers

Field Value
Rule code BSL191
Compatible alias FullOuterJoinQuery
Severity WARNING
Enabled by default Yes
Implemented Yes
Tags query, design

Behavior

  • The public identifier BSL191 and alias FullOuterJoinQuery are stable.
  • The rule reports the cases documented on this page.
  • Suppressions and project configuration are applied before publication.
  • The rule requires neither an external analyzer nor network access.

Configuration and suppression

BSL### is the primary stable identifier. The compatible alias is accepted in select, ignore, and compatible block suppression comments.

[tool.onec-hbk-bsl]
select = ["BSL191"]
ignore = ["FullOuterJoinQuery"]

All three suppression families support both a current line and a range. When an opening comment follows code, it affects only that line. Use any one form:

  • noqa:
Value = "example";  // noqa: BSL191
  • bsl-disable:
Value = "example";  // bsl-disable: BSL191
  • compatible BSLLS form:
Value = "example";  // BSLLS:FullOuterJoinQuery-off

When the same opening comment is on a line by itself, it starts a range. Close it with the matching marker from the same family:

// noqa: BSL191
// code without this diagnostic
// noqa-enable: BSL191

// bsl-disable: BSL191
// code without this diagnostic
// bsl-enable: BSL191

// BSLLS:FullOuterJoinQuery-off
// code without this diagnostic
// BSLLS:FullOuterJoinQuery-on

To disable the rule until the end of the file, omit the closing noqa-enable, bsl-enable, or BSLLS:…-on marker.

Opening and closing markers must belong to the same family.

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