Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the difference between IS NOT NULL and! = NULL in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Query 1: SELECT * FROM test WHERE data=NULL

Query 2: SELECT * FROM test WHERE dataNULL

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

This is because NULL is a unique data type in SQL, which is equivalent to having no value and being an unknown number. NULL is different from 0, empty string, and space.

Rows in the ANSI SQL standard that get null values need to use the following query:

The copy code is as follows:

SELECT * FROM test WHERE data IS NULL

Thus it can be seen that data=NULL is equivalent to data IS NULL,dataNULL and data IS NOT NULL in non-ANSI SQL standards.

So we should keep in mind that by default the keywords "is null" and "is not null" are used when comparing conditions.

If you must use! = null to determine the condition, you need to add this command statement: SET ANSI_NULLS OFF, when the database enters ANSI SQL non-standard mode, you will find that IS NOT NULL and! = null are equivalent.

The mode switching command SET ANSI_NULLS [ON/OFF] is used here. The ON value adopts the ANSI SQL strict standard, and the off value adopts the non-standard compatibility mode. In addition, the SET ANSI_DEFAULTS [ON/OFF] command can also achieve standard switching, but this command controls a set of settings that conform to the SQL-92 standard, including the standard for null values.

By default, the database manager (DB-Library) is SET ANSI_NULLS OFF. But most of our applications access the database through ODBC or OLEDB. As an open and compatible database access program, or for compatibility reasons, the SETANSI_ nulls value is set to ON. Some problems brought about by this need to be paid attention to. Applications such as stored procedures or custom 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, only database configuration parameters.

For example, your application uses ADODB to access the database and uses OleDb or ODBC data providers. For query 1: SELECT * FROM test WHERE data=NULL

We can send a command directly to get the query result set, or we can put it in the stored procedure. But the results of the two queries are different. If you use the query command directly, no rows are returned; if you access the stored procedure, the data of row 2 is returned.

After reading the above, do you have any further understanding of the difference between IS NOT NULL and! = NULL in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.