When we first get started with SQL, we filter to a row with a column value of NULL, typically in the following way: SELECT * from Table as T WHERE t.col=null www.2cto.com and in fact this kind of writing does not have the desired result. The workaround we found online at this time is to use: T.col is NULL. Although this method can get data, but I and some of the peers do not know why the t.col=null can not get a Col null row of this result.
Recently I want to strengthen the knowledge of SQL, so I looked for SQL Server 2005 Technical Insider: T-SQL query this book to see. In the book, we see three-valued logical points of knowledge. And this knowledge point for me to dispel the above question. What is a three-valued logic? The following is the original text in the book: Possible values for logical expressions in SQL include True, FALSE, and unknown. They are called three-valued logic. The three-valued logic is unique to SQL. Most programming languages have logical expressions that have only true or false values of two. Unknown logical values in SQL typically appear in logical expressions that contain NULL values, for example, the following three expression values are unknown:null<42; Null=null; x+null>y;
A null value typically represents a missing or unrelated value. The logical result is always unknown when comparing lost values and another value (this value can also be null). Handling unknown logical results and NULL is easily confusing. Not true equals false,not false equals true, while negation of UNKNOWN (not UNKNOWN) is UNKNOWN. Unknown logical results and NULL are treated differently in different language elements. For example, all query filters (on, where, and have) treat unknown as false. Rows that make the filter unknown are excluded from the result set, and the unknown value in the check constraint is treated as true. The construction table contains a CHECK constraint that requires that the value of the salary column must be greater than 0, which can be accepted when inserting salary to the table, because (null>0) equals unknown and is treated as true in the check constraint.
Comparing two null values in a filter will get unknown, which will be treated as false, as if one of the null is not equal to another null. and UNIQUE constraints. The sort operation and grouping operations consider two null values to be equal. If there is a column in the table that defines a unique constraint, it is not possible to insert two rows in the table with NULL for that column value. The GROUP BY clause groups all of the null values. The www.2cto.com ORDER BY clause arranges all the null values together.
It is good to understand how unknown logical results and NULL are handled in different language elements, so that you can avoid future problems. A lot of dense words, all dizzy. I use the popular point of explanation. For example in the C # language, the value of the conditional expression in C # is true and false. But there is a third condition value in sql: unknown. In C #, if (null==null) Gets the result that is true, not equal to the time is false. However, when SQL uses =, <, >, <>, <=, >= to determine null values, it does not get the expected true or false, but rather unknown.
There are two main types of unknown processing in SQL: 1. In the Where, on, and having clauses, the unknown is handled in front of false. So there was the beginning of my question. 2. The unknown value in the check constraint is treated as true. The construction table contains a CHECK constraint that requires that the value of the salary column must be greater than 0, which can be accepted when inserting salary to the table, because (null>0) equals unknown and is treated as true in the check constraint. According to the above explanation, it is known why it is null or not NULL to filter for NULL in a query, but a regular conditional expression cannot be filtered out.
A brief analysis on the three-valued logic of Sql\sql server
