Is not null and! in SQL! = NULL difference, sqlnull

Is not null and! in SQL! = NULL difference, sqlnull

These two methods are often used: is not null and! = NULL. We often encounter database conditions! = NULL, but the returned result is a NULL set. In fact, the difference is not fully understood.

By default, it IS recommended to use is not null for condition determination, because SQL uses WHERE XX by default! If the value is Null, 0 rows are always returned, but no syntax error is prompted.

Why?

The SQL Server document defines two rules for comparison of Null values, such as in SQL Server 2000:

Rule 1 is that the comparison results for Null values specified by ANSISQL (SQL-92) are both False, both Null = Null and False. Another method is not followed by the ANSISQL standard, that is, Null = Null is True. :

For example, the test structure of a data table:
Copy codeThe Code is as follows:
ROWNUM DATA
-------------------
1 'Liu yang'
2 Null
3 '123'

According to the ansi SQL standard, the following two queries do not return any rows:

Query 1: SELECT * FROM test WHERE data = NULL
Query 2: SELECT * FROM test WHERE data <> NULL

According to the non-ansi SQL standard, query 1 returns the second row, and query 2 returns the first and third 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.
The following query is required for the row whose Null value is obtained in the ansi SQL standard:
Copy codeThe Code is as follows:
SELECT * FROM test WHERE data IS NULL

In non-ansi SQL standards, data = NULL IS equivalent to data IS NULL, and data <> NULL IS equivalent to data IS NOT NULL.

Therefore, we should keep in mind that the keywords "is null" and "is not null" are used for comparison conditions by default ".

If you must use it! = Null to determine the condition. You need to add the command: SET ANSI_NULLS OFF. When the database enters the ansi SQL non-standard mode, you will find that IS NOT NULL and! = Null is equivalent.

The SET ANSI_NULLS [ON/OFF] command is used here. The ON value adopts the strict ansi SQL standard, and the OFF value adopts the non-standard compatibility mode. In addition, the SET ANSI_DEFAULTS [ON/OFF] command can also implement standard switching, but this command controls a SET of settings that comply with the SQL-92 standard, including the Null value standard.

By default, the Database Management Program (DB-Library) is SET ANSI_NULLS to OFF. However, most of our applications access the database through ODBC or OLEDB. As an open and compatible database access program, it may be compatible. The SETANSI_NULLS value is set to ON. In this way, you need to pay attention to some problems. Applications such as stored procedures and user-defined functions are based on DB-Library. By default, SETANSI_NULLS is OFF, and in such programs, you cannot use SETANSI_NULLS to modify rules in an environment. You can only modify Database configuration parameters.

For example, your application uses ADODB to access the database and OleDb or ODBC data provider. For query 1: SELECT * FROM test WHERE data = NULL
We can directly send commands to obtain the query result set, or store it in the stored procedure. However, the query results are different. If you directly use the query command, no rows are returned. If you access the stored procedure, the system returns 2nd rows of data.

Finally, we declare that, by default, the keywords "is null" and "is not null" are used for SQL condition query and comparison ".