Null values in SQL Server null value problem

Source: Internet
Author: User

Original: null value problem in SQL Server

SQL uses three-valued predicate logic, so the result returned by a logical expression can be true, false, or unknown, and returning true in the three-value logic is not exactly the same as not returning false.

SQL handling of query filtering conditions: Accept Ture deny false and unknown

SQL handling of CHECK constraints: Accept FALSE deny TRUE and UNKnown

the subtlety of unknown is when it is reversed   results are still unknown ,

The expression (Null=null) that compares two Null values is still evaluated as unknown because the null value represents an unknown value, so it is not really possible to determine whether an unknown value equals another, so SQL provides two predicates is Null and is Not NULL to replace =null and <>null, when filtering data with the not-in predicate, the value is never returned if the in value contains null (SELECT ordernum from [dbo].[ SalesOrder] WHERE ordernum not in (' 100128054 ', NULL))

When grouping and sorting , two null values are considered equal, meaning that the group by clause will reorganize all null values in each group

orderbynullt-sqlnull worth qualifying before the valid values

All of the aggregation functions will ignoreNullValue, with only one exceptionCount (*),Suppose a group has two rows and itsqty 3,null, expression count (*) return 2,count (qty) return 1 because a row is a known value

There are two UNIque constraints of ANSI SQL:

    1. < Span style= "FONT-FAMILY:SIMSUN; font-size:10.5pt; Font-weight:normal; Font-style:normal, "lang=" ZH-CN "> nullnull
    1. < Span style= "FONT-FAMILY:SIMSUN; font-size:10.5pt; Font-weight:normal; Font-style:normal, "lang=" ZH-CN "> nullnull

SQL Server only implements the former

Null values in SQL Server null value problem

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.