????null??????
?????????????????????????????????unknown????
????????true??false???ν?????????????????“δ?”????true??false????null???????????????????????????????????????￡??????????????塣???????????????????????null??????????????????÷???
????1.check?????null
????????SQL ServerCentral.com????????????check?????null????
????????????????????????orderstatus????????check????????????????value???У???????????????????????е?value?null?????????м??п??????????
??????????￡?
I want to ensure that the status column for my Orders table only contains specific values. I decide to use this code:
create table Orders
( OrderID int primary key
?? OrderTotal MONEY
?? OrderStatus VARCHAR(20)
constraint Orders_Status_Code check( OrderStatus in ('ACTIVE'?? 'INACTIVE'?? 'TBD'))
);
go
Now I want to insert data into the table. I run this batch.
insert Orders select 1?? 435.43?? 'Active'
insert Orders select 2?? 554.66?? 'InActive'
insert Orders select 3?? 129.12?? 'Not Active'
insert Orders select 4?? 1228.00?? NULL
How many rows are in the table? I am running on a default?? SQL Server 2014 instance with US English defaults.
???????????????????????а???????????????????
??????T-SQL?????????й???check?????null?????????????????????????????“check??????false??????true??null”??
????????????????У???orderstatus?‘Avative’??’InActive’???check????ж??????true?????????????????'Not Active’?ж?????false??????????????'Null’????ж??????null??????????
????????????????3??
????2.?????????null
????null?????????????????????????????null???κ?????κα??????????????null????unique?????????unique????У?null?????????????β????????????null??
?????????ж?null=null??????null???ж?null<>null?????????null???????????????(<>)????????????????null???ж??
????????????????????????????????????????orderstatus????null??
if object_id(N’Orders’) is not null drop table orders
create table Orders
( OrderID int primary key
?? OrderTotal MONEY
?? OrderStatus VARCHAR(20)
);
go
insert Orders select 1?? 435.43?? 'Active'
insert Orders select 2?? 554.66?? 'InActive'
insert Orders select 3?? 129.12?? 'Not Active'
insert Orders select 4?? 1228.00?? NULL
??????????????????where orderstatus<>'Active' ??????
????select * from orders where OrderStatus<>'Active'
???????????null??????л?????????????檔
????????????????У???orderstatus?'InActive' ??'Not Active' ???where?????ж?????true??????orderstatus?'null' ???where OrderStatus<>'Active'?????where null <>'Active'????null???κ???????????????null??????where?????ж?????null??
??????SQL Server?У?where?????????“????true?????false??null”????T-SQL????????????????orderstatus?'InActive' ??'Not Active'??????????????????orderstatus?null???в?????????????С?
????????????????????????

????3.Not in??null??Not exists??null
????not in??not exists???????????ж?????????????????????????????????????滻??????????null???????????????????true|false|unknow???ж???????????true??false??????????????к?????
???????????????????????????????????????????not in??not exists??????????????OrderStatus ???'Active'??'InActive'???С?
if object_id(N’Orders’) is not null drop table orders
create table Orders
( OrderID int primary key
?? OrderTotal MONEY
?? OrderStatus VARCHAR(20)
);
go
insert Orders select 1?? 435.43?? 'Active'
insert Orders select 2?? 554.66?? 'InActive'
insert Orders select 3?? 129.12?? 'Not Active'
insert Orders select 4?? 1228.00?? NULL