=null and is NULL in SQL statements

These two types of notation are often encountered: is not NULL and!=null. It is also often encountered that the database has data that meets the criteria!=null, but is returned as an empty collection. In fact, the difference between the two uses is not understood.

By default, it is recommended to use is not NULL to make conditional judgments, because SQL defaults to where xx! = NULL judgment will return 0 rows forever without prompting for a syntax error.

What is this for?

The comparison operation for null values in a SQL Server document defines two rules, such as in SQL Server 2000:

Rule one is that the comparison of the null values specified by Ansisql (SQL-92) results in a value of false, both Null=null and false. Another is not allowed to follow the Ansisql standard, that is, Null=null is true. ：

For example, the data table test structure:

ROWNUM DATA
-------------------
1 ' Liu Yang '
2 Null
3 ' 12345 '

In accordance with the ANSI SQL standard, none of the following two queries return any rows:

Query one: SELECT * from Test WHERE data=null
Query two: SELECT * from Test WHERE data<>null

In the non-ANSI SQL standard, query 1 returns the second row, and query 2 returns 1, 3 rows.

This is because in SQL, NULL is a unique data type, which is equivalent to no value and is unknown. Null is different from 0, empty string, and space.
Rows that get null values in the ANSI SQL standard require the following query:

SELECT * FROM Test WHERE data is NULL

This shows that the non-ANSI SQL standard data=null equivalent to data is null,data<>null equivalent to data is not NULL.

So let's keep in mind that the keyword "is null" and "is not NULL" are used by default when comparing conditions.

If you must use! = NULL to make conditional judgments, add this command statement: SET ANSI_NULLS OFF, when the database enters ANSI SQL nonstandard mode, you will find that is isn't null and! = NULL is equivalent. (Of course, it's just a dead end.) ）

The mode Toggle command Set Ansi_nulls[on/off] is used here. The on value uses ANSI SQL strict standards and the off value is in non-standard compatibility mode. The set ansi_defaults [on/OFF] command also enables standard switching, except that this command controls a set of settings that conform to the SQL-92 standard, including criteria for null values.

By default, the database management program (db-library) is set ANSI_NULLS off. But most of our applications access the database through ODBC or OLE DB, as an open and compatible database accessor, perhaps for compatibility reasons, the Setansi_nulls value is set to ON. Some of the problems that this brings are important to note. Applications such as stored procedures or custom functions are based on db-library, and by default, Setansi_nulls is off, and in such a program, you cannot use Setansi_nulls to modify rules in an environment, only database configuration parameters can be modified.

For example, your application uses ADODB to access the database, using OLE DB or an ODBC data provider. For query one: SELECT * from Test WHERE data=null
We can send the command directly to get the query result set, or it can be put into the stored procedure. However, they differ in their query results. If the query command is used directly, no rows are returned, and if the stored procedure is accessed, the data on row 2nd is returned.

Finally, we declare again: the database by default, makes SQL conditional query comparisons using the keyword "is null" and "is not NULL".

=null and is NULL in SQL statements