Details on how to use the group inclusion operator

1C:Enterprise query language includes a generalized form of the IN/ NOT IN operator. This section describes how this operator is executed. This knowledge helps with query optimization.

Group inclusion operator

The inclusion operator has the following syntax:

<Expression> IN (<List of values>)

The inclusion operator is one of the comparison operators defined in 1C:Enterprise query language. Its value is True if the value of the left operand is in the list of values of the right operand. The list of values can be defined as:

a list of expressions;
a parameter with a value that is a value collection;
a nested query.

If a list of values is defined by a nested query, you can use the generalized form of the inclusion operator, i.e. the group inclusion operator:

(<Expression 1>, ..., <Expression N>) IN (<Nested query>)

A nested query should define a selection of N columns.

The group inclusion operator value is True if the record (<Expression 1>, ..., <Expression N>) matches one of the records in the selection defined by the nested query. For example, suppose the left operand is a list:

(1, 2, 3).

If the result of the nested query is the following selection:

1, 1, 1
2, 2, 2
2, 2, 3

the result of this group inclusion operator is False. If the result of the nested query is another selection:

1, 1, 1
1, 2, 3
2, 2, 3

the result of the group inclusion operator is True.

In addition to the group inclusion operator (IN) the query language also provides a noninclusion operator (NOT IN). The result of this operator is a negation of the IN operator with the same operands. The explanation above regarding the IN operator is valid for NOT IN as well.

Implementation of the group inclusion operator

Unlike a simple inclusion operator that has some analogues in most database management systems (the SQL language IN operator), the group inclusion operator does not have an exact equivalent in SQL. Therefore, when you use the group inclusion operator, you should take into account the way it is translated into SQL. The following operator:

(<Expression 1>, ..., <Expression N>) IN (
    SELECT <Column 1>, ..., <Column N>
    FROM <Sources>
    WHERE <Condition>
)

is executed as follows in the DBMS:

EXISTS(
    SELECT 1
    FROM <Sources>
    WHERE (<Condition>) AND <Expression 1> = <Column 1> AND ... AND <Expression N> = <Column N>
)

If a nested query contains aggregate functions and/or a GROUP BY section, the following group inclusion operator:

(<Expression 1>, ..., <Expression N>) IN (
    SELECT <Column 1>, ..., <Column N>
    FROM <Sources>
    WHERE <Condition 1>
    GROUP BY <Grouping list>
    HAVING <Condition 2>
)

is coded as follows in SQL:

EXISTS(
    SELECT 1
    FROM <Sources>
    WHERE <Condition 1>
    GROUP BY <Grouping list>
    HAVING (<Condition 2>) AND <Expression 1> = <Column 1> AND ... AND <Expression N> = <Column N>
)

Notes on execution speed

The execution speed of a query containing the group inclusion operator depends on the capability of the query plan optimizer of the database management system to efficiently implement the EXISTS operator with a nested query. Therefore, it is difficult to predict if a specific query will be executed efficiently in general. However, it is possible to offer some recommendations that may help generate efficient queries.

Short tables

Use of a group inclusion operator in a query does not decrease performance if the nested query generates a selection from tables with small numbers of records, and these numbers cannot significantly increase during the operation of the system with the growth of the accumulated database.

Indexes

You should remember that a query nested in an EXISTS operator is executed multiple times when checking the filter condition for each record. Therefore, if tables used in a nested query may include a large number of records, any search within the tables should be optimized by defining the appropriate indexes:

for the values of columns <Column 1>, ..., <Column N> only use names of table fields (not expressions);
select a field from those used as values of columns <Column 1>, ..., <Column N> with its values repeated least frequently (the most selective field) and enable indexing by this field;
do not use aggregate functions and groupings in a query nested in a group inclusion operator;
include the most selective fields in the list of group inclusion operator. For example, when you check warehouse balances by product items from the tabular section of the PurchaseOrder document, which is referenced by the DocumentRef parameter, the following query will be executed slowly because it is impossible to generate an index from a group of fields (Items, ItemProperty, Quality) in the Document.PurchaseOrder.Goods table.

SELECT
    Doc.Items.Presentation AS ItemsPresentation,
    Doc.Items.UnitOfMeasurement.Presentation AS UnitOfMeasurementPresentation,
    Doc.ItemProperty AS ItemProperty,
    Doc.ItemSeries AS ItemSeries,
    Doc.Quality AS Quality,
    SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3))) AS DocumentQuantity,
    ISNULL(MAX(Balance.QuantityBalance), 0) AS BalanceQuantity
FROM 
    Document.SalesInvoice.Goods AS Doc
    LEFT JOIN
        AccumulationRegister.GoodsToBeShippedFromWarehouses.Balance(,
            Warehouse = &Warehouse AND (Items, ItemProperty, Quality) IN (
                SELECT
                    Doc.Items, Doc.ItemProperty, Doc.Quality
                FROM
                    Document.SalesInvoice.Goods AS Doc
                WHERE
                    Doc.Ref = &DocumentRef
                    AND NOT Doc.Items.Package 
            ) AND &ShippingDocument = ShippingDocument) AS Balance
    ON 
        Doc.Items = Balance.Items
        AND Doc.ItemProperty = Balance.ItemProperty
        AND Doc.Quality = Balance.Quality
        AND ((NOT Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = Doc.ItemSeries) OR
            (Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = &EmptySeries))
WHERE
    Doc.Ref = &DocumentRef
GROUP BY
    Doc.Items,
    Doc.ItemProperty,
    Doc.ItemSeries,
    Doc.Quality,
    &ShippingDocument
HAVING
    ISNULL(MAX(Balance.QuantityBalance), 0) < SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3)))

The following query is different from the previous one in that the group inclusion operator has the Warehouse field added, which is used for indexing in the Document.PurchaseOrder.Goods table. This results in a significant reduction of query execution time.

SELECT
    Doc.Items.Presentation AS ItemsPresentation,
    Doc.Items.UnitOfMeasurement.Presentation AS UnitOfMeasurementPresentation,
    Doc.ItemProperty AS ItemProperty,
    Doc.ItemSeries AS ItemSeries,
    Doc.Quality AS Quality,
    SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3))) AS DocumentQuantity,
    ISNULL(MAX(Balance.QuantityBalance), 0) AS BalanceQuantity
FROM 
    Document.SalesInvoice.Goods AS Doc
    LEFT JOIN
        AccumulationRegister.GoodsToBeShippedFromWarehouses.Balance(,
            (Warehouse, Items, Quality, ItemProperty) IN (
                SELECT
                    Doc.Ref.Warehouse, Doc.Items, Doc.Quality, Doc.ItemProperty
                FROM
                    Document.SalesInvoice.Goods AS Doc
                WHERE
                    Doc.Ref = &DocumentRef
                    AND NOT Doc.Items.Package 	
            ) AND &ShippingDocument = ShippingDocument) AS Balance
     ON 
        Doc.Ref.Warehouse = Balance.Warehouse
        AND Doc.Items = Balance.Items
        AND Doc.Quality = Balance.Quality
        AND Doc.ItemProperty = Balance.ItemProperty
        AND ((NOT Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = Doc.ItemSeries) OR
            (Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = &EmptySeries))

WHERE
    Doc.Ref = &DocumentRef
GROUP BY
    Doc.Items,
    Doc.ItemProperty,
    Doc.ItemSeries,
    Doc.Quality,
    &ShippingDocument
HAVING
    ISNULL(MAX(Balance.QuantityBalance), 0) < SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3)))

Temporary tables

If a query nested in the group inclusion operator is complex, contains aggregate functions, and uses tables with large numbers of records, the execution of the group inclusion operator might be extremely inefficient. In this case we recommend the following:

execute the nested query separately and store its result to a temporary table;
define an index in this temporary table either using all the fields or the most selective subset of fields;
use the resulting temporary table in the query nested in the group inclusion operator.

Next page: Display of reference fields