The difference between are not NULL and!=null in SQL _ database other

These two types of writing are often encountered: are not NULL and!=null. It is also often encountered that the database has!=null data, but returns to an empty collection. In fact, it is because the difference between the two uses is not understood.

By default, it is recommended that you use is not NULL to make conditional judgments, because SQL defaults to where xx! = NULL judgment returns 0 rows forever without prompting for syntax errors.

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 Ansisql (SQL-92) The value of the null value of the comparison evaluates to False, both Null=null value and false. The other is not allowed to follow the Ansisql standard, that is, Null=null is true. ：

For example, the data table test structure:

Copy Code code as follows:

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

The following two queries do not return any rows according to the ANSI SQL standard:

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

According to 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, an empty string, or a space.
The rows that get null values in the ANSI SQL standard need to use the following query:

Copy Code code as follows:

SELECT * FROM Test WHERE data is NULL

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

So let's keep in mind that by default comparisons are made using the keyword "is null" and "is not null."

If you have to use!= NULL to make conditional judgments, you need to add this command statement: SET ansi_nulls off, when the database goes into ANSI SQL non-standard mode, and you will find that isn't null and!= null are equivalent.

The mode switch command set Ansi_nulls[on/off is used here. The on value is in the ANSI SQL strict standard and the off value is in nonstandard compatibility mode. In addition, the set ansi_defaults [On/off] command also enables standard switching, except that the command controls a set of criteria that meet the SQL-92 standard, including the criteria for null values.

By default, the database management program (db-library) is set ANSI_NULLS off. Most of our applications, however, access the database through ODBC or OLE DB, as an open, compatible database access program, perhaps with compatibility considerations, with the Setansi_nulls value set to ON. Some of the problems that come with that need to be noted. Applications like stored procedures or custom functions are based on db-library, by default, Setansi_nulls is off, and in such programs, setansi_nulls cannot be used to modify rules in an environment, only database configuration parameters can be modified.

For example, your application uses ADODB to access the database, using an OLE DB or 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 you can put it in the stored procedure. But the results of the two queries are different. If you use the query command directly, you do not return any rows, and if you access the stored procedure, you return the 2nd row of data.

Finally, we declare again that the database uses the keyword "is null" and "is not NULL" by default when making SQL conditional query comparisons.