NULL- missing values.
Not to be confused with zero! NULL is not a number, not equal to a space, an empty reference, Undefined.

NULL is a type-forming value, i.e. there is a type NULL and a single value of this type.

NULL values ​​appear in a query in the following situations:
a) An external join in which no corresponding record was found in another table (with the left one - in the second, with the right one - in the first, with a full one - in both)
b) Referring to the attributes of the elements for the group and vice versa.
c) NULL in the list of selection fields (SELECT)
d) Referring to the details for the broken link

IS NULL used in the SELECT operator (as if checking that the value is empty (Value IS NULL)):
Code 1C v 8.x
CHOICE
WHEN Value IS NULL THEN Result If NULL
ELSE Value
THE END

another example:
Code 1C v 8.x SELECT

CHOICE WHEN Accounting Nomenclature Balances.Amount Balance IS NULL THEN 0
ELSE Nomenclature accounting Balances.Quantity Balance AS Quantity Balance
FROM



WHERE

Function ISNULL (value, ResultIf NULL) returns the value of its first parameter if it is not NULL, and the value of its second parameter otherwise
Is a collapsed SELECT ... END, but IS NULL is preferred.
Code 1C v 8.x
SELECT
ISNULL (Directory.Nomenclature.Article, "---") AS Article,
Reference.Nomenclature.Presentation AS Nomenclature

another example:
Code 1C v 8.x
SELECT
Directory of Nomenclature.
IS NULL (NomenclatureAccountingBalance.QuantityRemainment, 0) AS QuantityRemainment
FROM
Directory.Nomenclature AS Directory
LEFT JOIN Accumulation Register.AccountingNomenclature.Balances AS AccountingNomenclatureBalances
Software Nomenclature AccountingBalances.Nomenclature = Nomenclature Directory.Link
WHERE
DirectoryNomenclature.ThisGroup = FALSE
V this example all the elements of the stock list are obtained, after which, for each stock, the current balances are obtained from the accumulation register. Because for an item for which there are no balances, the virtual table of balances will not return a record, then as a result of the connection in the field "NomenclatureAccountRemains.Quantity" there will be NULL values ​​for an item for which there were no balances. In order to have the value 0 instead of the NULL value in the query result, we used the IS NULL () function, which will perform the desired replacement.

IS NULL differs from CHOICE for the following reasons:
a) If IS NULL, the query is better read (easier)
b) If IS NULL, if a complex expression is checked, then it works faster, since it is evaluated once
c) If IS NULL, the replacement expression is cast to the type of the tested expression if it is of the String (length) or Number (bit width) type.

You cannot check values ​​for NULL with ordinary equality, because three-valued logic operates in SQL - True, False, NULL, and the result of such a comparison will be UNKNOWN, which in 1C 8.0 is similar to FALSE.
NULL<>0, so for left outer joins ref. Nomenclature with tables of balances, prices, Counterparties with mutual settlements, in the absence of such records, there will be NULL, which is not equal to 0. The best solution- IS NULL

When working with the query language, sometimes a situation arises when you need to replace the NULL value with some other value. The need for such an operation may arise, for example, when receiving stock balances for the entire item. This article describes how to use the ISNULL () query language function to solve similar problems, and also discusses other solutions.

ISNULL function

The ISNULL () function is implemented in the query language, the purpose of which is to replace an expression with another expression if the expression was NULL. The syntax diagram for this function looks like this:

ISNULL (<Проверяемое выражение>, <Выражение замены>)

This function will return the value of the first parameter if it is not NULL, and the value of the second expression otherwise.

SELECT
IS NULL (NomenclatureAccountingBalance.QuantityBalance, 0) AS QuantityBalance
FROM

WHERE

In this example, all the elements of the item catalog are obtained, after which, for each item, the current balances are obtained from the accumulation register. Because for an item for which there are no balances, the virtual table of balances will not return a record, then as a result of the connection in the field "NomenclatureAccountRemains.Quantity" there will be NULL values ​​for an item for which there were no balances. In order to have the value 0 instead of the NULL value in the query result, we used the IS NULL () function, which will perform the desired replacement.

Using the SELECT operation

To solve the problem described earlier, you can use the SELECT query language operation. An example of how a query similar to the previous one will look like will look like this:

SELECT
Directory of Nomenclature.
SELECT WHEN Nomenclature accounting Balances.Amount Balance IS NULL THEN 0
ELSE Nomenclature accounting Balances.Quantity Balance AS Quantity Balance
FROM
Directory.Nomenclature AS Directory
LEFT JOIN Accumulation Register.AccountingNomenclature.Balances AS AccountingNomenclatureBalances
Software Nomenclature AccountingBalances.Nomenclature = Nomenclature Directory.Link
WHERE
DirectoryNomenclature.ThisGroup = FALSE

The result of this query will be identical to the result of the query given in the previous section.

Using the ISNULL () function is preferable to using the SELECT operation for the following reasons: writing with ISNULL () is somewhat more compact, which increases the readability of the query. In addition, in the case when the tested expression is a complex function, including an aggregate function, the evaluation of the ISNULL () function can be faster than the analogue written using the CHOICE function.

Features of the ISNULL function

The ISNULL () function, although it is analogous to the SELECT operation with checking the value for NULL, nevertheless has a difference. The difference is that if the function expression has a string or numeric type, then the replacement expression will be converted to the type of the tested expression.

So, for example, in the case when the tested expression has the String (5) type, and the replacement expression has the String (10) type, the result type will be converted to the String (5) type. As a result, when the function returns a replacement expression, the value will be truncated to five characters.

With numeric expressions, the situation is similar: the value of the expression being replaced will be converted to the type of the tested one, i.e. the expression to be replaced can be truncated. If the value cannot be converted, the query language will terminate the query with an error. For example, an attempt to convert the number 1000 to the Number (2) type will end with an error.

Hello.
Continuing about primitive data types, we have recently considered, and today we will talk about type NULL.

The NULL type is a literal that can contain only one value, which is the "NULL" value.
NULL - Not equal to empty reference, space, or Undefined Type.

It is used when working with a database (when joining tables), it is used to determine the missing value when working with a database.
A NULL value can be obtained by assigning this value to a variable:
Variable = NULL.

NULL values ​​are formed as a result of joins when an element from one table does not conditionally find a corresponding element from another. NULL values ​​have specific features:
- comparison of a NULL value with any other argument always returns false;

To define a NULL value, use the construction IS NULL.
To convert the NULL type, use the function ISNULL (isNULL).

In order to cut off fields containing NULL values ​​as a result of a query, the following constructions are used: - is not NULL - not is NULL

Examples of

An example of checking a value for NULL

SELECT Directory. Nomenclature. Name, Reference. Nomenclature. Purchase Price WHERE Directory. Nomenclature. Purchase Price Is NULL

Example of ISNULL () function
The return value of the function IS NULL (): the value of the first parameter, if the first parameter does not contain a NULL value, otherwise the value of the second parameter. The second parameter will be converted to the type of the first parameter if the type of the first parameter is a string or a number.

// Get the amount for the quantity field. In case there are no records, get 0 SELECT IS NULL (SUM (Quantity), 0) AS Quantity FROM Document. Consignment Composition

Best regards, 1C Programmer.
Leave your comments, I am interested in your opinion.

NULL- missing values.
Not to be confused with zero! NULL is not a number, not equal to a space, an empty reference, Undefined.

NULL is a type-forming value, i.e. there is a type NULL and a single value of this type.

NULL values ​​appear in a query in the following situations:
a) An external join in which no corresponding record was found in another table (with the left one - in the second, with the right one - in the first, with a full one - in both)
b) Referring to the attributes of the elements for the group and vice versa.
c) NULL in the list of selection fields (SELECT)
d) Referring to the details for the broken link

IS NULL used in the SELECT operator (as if checking that the value is empty (Value IS NULL)):
Code 1C v 8.x
CHOICE
WHEN Value IS NULL THEN Result If NULL
ELSE Value
THE END

another example:
Code 1C v 8.x SELECT
SELECT WHEN Nomenclature accounting Balances.Amount Balance IS NULL THEN 0
ELSE Nomenclature accounting Balances.Quantity Balance AS Quantity Balance
FROM

WHERE

Function ISNULL (value, ResultIf NULL) returns the value of its first parameter if it is not NULL, and the value of its second parameter otherwise
Is a collapsed SELECT ... END, but IS NULL is preferred.
Code 1C v 8.x
SELECT
ISNULL (Directory.Nomenclature.Article, "---") AS Article,
Reference.Nomenclature.Presentation AS Nomenclature

another example:
Code 1C v 8.x
SELECT
Directory of Nomenclature.
IS NULL (NomenclatureAccountingBalance.QuantityRemainment, 0) AS QuantityRemainment
FROM
Directory.Nomenclature AS Directory
LEFT JOIN Accumulation Register.AccountingNomenclature.Balances AS AccountingNomenclatureBalances
Software Nomenclature AccountingBalances.Nomenclature = Nomenclature Directory.Link
WHERE
DirectoryNomenclature.ThisGroup = FALSE
In this example, all the elements of the item catalog are obtained, after which, for each item, the current balances are obtained from the accumulation register. Because for an item for which there are no balances, the virtual table of balances will not return a record, then as a result of the connection in the "NomenclatureAccountRemains.Quantity" field there will be NULL values ​​for an item for which there were no balances. In order to have the value 0 instead of the NULL value in the query result, we used the IS NULL () function, which will perform the desired replacement.

IS NULL differs from CHOICE for the following reasons:
a) If IS NULL, the query is better read (easier)
b) If IS NULL, if a complex expression is checked, then it works faster, since it is evaluated once
c) If IS NULL, the replacement expression is cast to the type of the tested expression if it is of the String (length) or Number (bit width) type.

You cannot check values ​​for NULL with ordinary equality, because three-valued logic operates in SQL - True, False, NULL, and the result of such a comparison will be UNKNOWN, which in 1C 8.0 is similar to FALSE.
NULL<>0, so for left outer joins ref. Nomenclature with tables of balances, prices, Counterparties with mutual settlements, in the absence of such records, there will be NULL, which is not equal to 0. The best solution is IS NULL

27.06.2017

NULL, IS NULL () and IS NULL in 1C requests

What is NULL

NULL as a result of the request, means no value (this is not an empty value, not null, not a null reference).
For example, as a result of joining the query tables, no value was found in one of the tables when linking by the join fields. Or, the request is referring to a non-existent attribute (property).

NULL is not equal to NULL!

If in a 1C query you want to impose a selection (condition) with a check for NULL, then the construction of the form "WHERE VT. Field1 = NULL" will always return LYING! To check, use the special operator

THERE IS NULL in 1C request

To check a value (or to work with values) for NULL, you must use the following construction "WHERE VT. Field1 IS NULL" Or another example, use in the construction CHOICE"CHOICE | WHEN VT.Field1 IS NULL | THEN" It is worth noting that the function of checking a field for NULL is one of the most resource-intensive in 1C queries. Therefore, if you want to use it in a query condition, think about whether it is possible to replace such a check internal connection(a type of join in queries in which only the records present in both tables remain in the selection).

Function IS NULL () in 1C request

Function ISNULL () allows you to replace the missing value in the request field with the specified value (standard value, stub).
For example, if there is no information about the cost of a product, then we indicate it equal to 0 (zero) "SELECT | IS NULL (WT.Price, 0) AS Price | FROM Balance of Products AS WT"

Conclusion

Write your queries correctly and optimally. Because a bad request is brakes and shit code)