Wonderful null in SQL Server

Http://www.cnblogs.com/changbluesky/archive/2010/05/07/1729519.html

PS. Too lazy to write, too lazy to summarize, copy a good summary, but there are some not said, in short, several errors encountered when the null should be considered more test

 

I believe everyone will experience null when writing SQL statements. Insert null in a table and compare it with null.

1. null indicates the missing value ).

2. three-value Logic (three-valued-logic: True, false, unknown ). there are three logical predicates in SQL: True, false, and unknown. in most programming languages, only true and false are supported, while unknown exclusive to SQL is related to null.

For example, the following comparison is performed: NULL> 32; null = NULL; x + null> Y; null <> null. The calculation result is unknown.

It may be somewhat confusing. What is different from the binary logic (not ture = faluse; not false = true) is not unknown = unknown.

3. processing when unknown is false. when querying and filtering in SQL (on, where, having), unknown is treated as false, so that the row with the calculated value of unknown is not added to the next result set.

4. When unknown is used as true, unknown is used as true in the check constraint.

For example, to add a constraint to a table, the age of the constraint must be greater than zero: alter table test1 add constraint ck_age check (age> 0 ), you can still insert a null value when inserting data (provided that the column does not have the not null constraint defined ). insert into test1 (AGE) values (null)

 

Code

create table test
(
Name varchar(10),
age int
)

--add check constraint age>0
alter table test add constraint ck_age check (age>0)

insert into test values
('bluesky',null)

select * from test

 

A null value can be inserted.

 

5. let's talk about the comparison between null and null. As mentioned above (null = NULL; null <> null), the comparison between null and null is unknown. however, for unioue constraints, set operations (such as Union, distinct T), sorting, and grouping, null and null are considered to be equivalent.

5.1 if a column has a unique constraint, two null values cannot be inserted.

--add unique
alter table test add unique (Name asc)

insert into test values
(NULL,12),
(NULL,13)

Define the unique constraint first. The following prompt is displayed when two null values are inserted, indicating that null is treated as equivalence in unique.

5.2 group by will divide null into a group.

Code

--drop the unique constraint
ALTER TABLE [dbo].[test] DROP CONSTRAINT [UQ__test__737584F63A81B327]

--insert two null values
insert into test values
(NULL,12),
(NULL,13)

select * from test

select Name,SUM(age)as sumage from test group by Name

We can see that null is divided into one group:

 

5.2 order by sorts null values together.

5.3 null is treated as an equivalent in a set operation.

 

In short: identifying in different situations: when a null operation is performed, it is used as unknown, true, or false, which is helpful for better SQL writing and DB Design.