The difference between SQL null and NULL

1.NULL meaning is the missing value (missing value).

2. Three value logic (Three-valued-logic:true,false,unknown). There are three logical predicates in SQL: Ture,false,unknown. In most programming language only True and false in SQL , and all of the existence of unknown is related to null.

For example, make the following comparison: null>32; Null=null; x+null>y; Null<>null. The results of the calculation are unknown.

May be somewhat confusing, with two value logic different (not ture=faluse; Not false=true) is not unknown=unknown.

3.UNKNOWN is treated as false. When querying for filtering in SQL (on,where,having), unknown is treated as false, so that rows with computed values of unknown are not added to the next result set.

4.UNKNOWN is handled as true. Unknown in a check constraint is handled as true.

5. The comparison between null and NULL is discussed above (null=null; Null<>null), that is, the comparison between null and NULL is unknown. However, for unioue constraints, set operations (such as union,except), sorting, grouping, null and NULL are considered equivalent.

A comparison operation for the null value of SQL Server. In general, when we query for null or non-null values, we use is null/is not NULL, and seldom use =/<>. But in my this program, is not used is such a keyword, but with =/<> such a comparison meta-calculation symbol, this encountered some problems.
The problem originates from a Web query page, because the problem is more complex, so simplify it to illustrate.
On the page, the user is free to select some fields of the data table, fill in the query criteria of the field, first select the comparison operation symbol (=,<>, etc.), and then fill in the value. Once committed, you need to create an SQL statement, and the parts of the query condition are created by different program modules. This involves two program modules, one module creates the comparison operation symbol according to the commit, and one module is responsible for creating the comparison value module. There is a rule in the Create value module, "If the value submitted is empty, set the value to null".
But I find that if the value is null, the same SQL query statement is placed in the stored procedure and the result of querying and querying directly through the application is not the same.
Check out the SQL Server documentation and find the comparison operation for null values, there are two kinds of rules:
There are two rules for comparison operations of NULL values in SQL2000. One is that the comparison of NULL values specified by ANSI SQL (SQL-92) results in a value of false, both Null=null and false. Another is not allowed to follow the ANSI SQL standard, that is, Null=null is true.
Take the query for a table t as an example.

Table T has the following data:
RowId Data
--------------
1 ' Test '
2 Null
3 ' test1 '

In accordance with the ANSI SQL standard, none of the following two queries return any rows:
Query1: select * from T where data=null
Query2: select * from T where Data<>null
In the non-ANSI SQL standard, query 1 returns the second row, and query 2 returns 1, 3 rows.
Rows that get null values in the ANSI SQL standard require the following query:
SELECT * from T where Data is null
Otherwise, it is not null. This shows that the non-ANSI SQL standard data=null equivalent to data is null,data<>null equivalent to data is not Null.

The control uses that kind of rule, need to use the command SET ansi_nulls [on/off]. The on value uses the ANSI SQL standard and the off value is in nonstandard 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. However, most of our applications access the database through ODBC or OLE DB, as an open and compatible database accessor, perhaps for compatibility, set ANSI_NULLS value 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, set ANSI_NULLS is off, and in such a program, you cannot use SET ANSI_NULLS to modify rules in an environment, only database configuration parameters can be modified.

Consider the following scenario.
Your application uses ADODB to access the database, using OLE DB or ODBC data providers. For the previous query 1:
SELECT * FROM T where data=null
You can send commands directly to a result set, or you can put it into a stored procedure. But their query results are not the same. If you use the query command directly, nothing results, and if you access the stored procedure, you get the data for row 2nd.

I wrote a. NET program to verify this. Also in order to verify the settings of the. Net SqlClient set ANSI_NULLS, because SqlClient does not access SQL Server through OLE DB or ODBC data providers, it accesses SQL Server directly. Originally I thought it would take SQL Server default settings, but the result is the opposite, with the default setting of OLE DB, ODBC.

Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.OleDb;
Using System.Data.Odbc;
public class ansinullstest{
public static void Main (string[] args) {
IDbConnection Conn;
String conntype = "SqlClient";
if (args. length>0) Conntype = args[0];
if (conntype.toupper () = = "OLE DB") {
Console.WriteLine ("Connection type:oledb");
conn = new OleDbConnection ("Provider=SQLOLEDB.1; User Id=sa; Pwd=test;initial catalog=test;data source=test ");
}else if (conntype.toupper () = = "ODBC") {
Console.WriteLine ("Connection Type:odbc");
conn = new OdbcConnection ("Driver={sql Server}; Uid=sa; Pwd=test;database=test; Server=test ");
}else{
Console.WriteLine ("Connection type:sqlclient");
conn = new SqlConnection ("server=test;database=test; User Id=sa; Pwd=test ");
}
Test (conn);
}
public static void Test (IDbConnection conn) {
String query1 = "Select ' Test ' where null=null";
String Query2 = "exec p_test"; SQL statements that are the same in a stored procedure
IDbCommand cmd;
IDataReader reader;
Console.WriteLine ("Print ' Test ' Set ANSI_NULLS off");
try{
CMD = conn. CreateCommand ();
Conn. Open ();
Cmd.commandtext = Query1;
reader = cmd. ExecuteReader ();
Console.WriteLine ("Command:" + Query1);
while (reader. Read ()) {
Console.WriteLine ("Result:" + reader[0]. ToString ());
}
Reader. Close ();
Cmd.commandtext = Query2;
reader = cmd. ExecuteReader ();
Console.WriteLine ("Command:" + Query2);
while (reader. Read ()) {
Console.WriteLine ("Result:" + reader[0]. ToString ());
}
Reader. Close ();
}
catch (Exception ex) {
Console.WriteLine (ex. Message);
}
finally{
Conn. Close ();
}

}
}

It has a parameter that takes different parameter values according to the parameters using different database access programs. The command object makes two queries, one SQL query command, and one call to the stored procedure. Statements are the same, but the results are different.

The difference between SQL null and NULL