Is not null and! in Oracle SQL! = NULL

Is not null and! in Oracle SQL! = NULL: 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 ANSISQL (SQL-92) the comparison result of the specified Null value is 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: rownum data--1 'Liu yang' 2 Null3 '20160301' follows 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, in accordance with Non-ansi SQL standards, 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 rows that obtain Null values in the ansi SQL standard: SELECT * FROM test WHERE data is null. Therefore, data = NULL in non-ansi SQL standards IS equivalent to data IS NULL, 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 a command 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 ".