本文章介绍一篇关于sql中IN与EXISTS,NULL 值的处理方式与其他值不同

NULL 值保存,NULL 值的处理方式与其他值不同,本文章介绍一篇关于sql中IN与EXISTS,country) VALUES(5

即便表中的有个别列是可选的,那么我们能够在不向该列增添值的情况下插入新记录或更新已部分记录。这意味该字段将以
NULL 值保存。

本小说介绍一篇关于sql中IN与EXISTS,NOT IN与NOT
EXISTS的详尽介绍,有亟待领会的校友能够参照一下。

NULL 值的管理形式与任何值差别。

重申下:在含蓄NULL值的列的时候,将在小心了,not exists与not
in在逻辑上是不等价的–先创造2张表

NULL 用作未知的或不适用的值的占位符。

代码如下复制代码

评释:不只怕相比 NULL 和 0;它们是不等价的。

use master;

is not null实例

if db_id(‘DbTest’) is not null drop database DbTest;

5 create table Billings (6 BankerID INTEGER,7 BillingNumber INTEGER,8
BillingDate datetime,9 BillingTotal INTEGER,10 TermsID INTEGER,11
BillingDueDate datetime ,12 PaymentTotal INTEGER,13 CreditTotal
INTEGER1415 );16 GO12 INSERT INTO Billings VALUES (1, 1, ‘2005-01-22′,
165, 1,’2005-04-22’,123,321);3 GO

(1 rows affected)1 INSERT INTO Billings VALUES (2, 2, ‘2001-02-21′,
165, 1,’2002-02-22’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (3, 3, ‘2003-05-02′,
165, 1,’2005-04-12’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (4, 4, ‘1999-03-12′,
165, 1,’2005-04-18’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (5, 5, ‘2000-04-23′,
165, 1,’2005-04-17’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (6, 6, ‘2001-06-14′,
165, 1,’2005-04-18’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (7, 7, ‘2002-07-15′,
165, 1,’2005-04-19’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (8, 8, ‘2003-08-16′,
165, 1,’2005-04-20’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (9, 9, ‘2004-09-17′,
165, 1,’2005-04-21’,123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (0, 0, ‘2005-10-18′,
165, 1,’2005-04-22’,123,321);2 GO

(1 rows affected)123 SELECT *4 FROM Billings5 WHERE BillingTotal IS
NOT NULL6 GOBankerID BillingNumber BillingDate BillingTotal TermsID
BillingDueDate PaymentTotal CreditTotal———– ————-


———————– ———— ———– 1 1 2005-01-22
00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321 2 2 2001-02-21
00:00:00.000 165 1 2002-02-22 00:00:00.000 123 321 3 3 2003-05-02
00:00:00.000 165 1 2005-04-12 00:00:00.000 123 321 4 4 1999-03-12
00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321 5 5 2000-04-23
00:00:00.000 165 1 2005-04-17 00:00:00.000 123 321 6 6 2001-06-14
00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321 7 7 2002-07-15
00:00:00.000 165 1 2005-04-19 00:00:00.000 123 321 8 8 2003-08-16
00:00:00.000 165 1 2005-04-20 00:00:00.000 123 321 9 9 2004-09-17
00:00:00.000 165 1 2005-04-21 00:00:00.000 123 321 0 0 2005-10-18
00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321

(10 rows affected)12 drop table Billings;3 GO

create database DbTest;go

is null 判定为空的从头到尾的经过

use DbTest;go

45 CREATE TABLE titleauthor(6 au_id varchar(20),7 title_id
varchar(20),8 au_ord tinyint NULL,9 royaltyper int NULL10 )11 GO12
insert titleauthor values(null, ‘2’, 1, 60)3 insert titleauthor
values(‘2’, ‘3’, 1, 100)4 insert titleauthor values(‘3’, ‘4’, 1, 100)5
insert titleauthor values(‘4’, ‘5’, 1, 100)6 insert titleauthor
values(‘5’, ‘6’, 1, 100)7 insert titleauthor values(‘6’, ‘7’, 2, 40)8
insert titleauthor values(‘7’, ‘8’, 1, 100)9 insert titleauthor
values(‘8’, ‘9’, 1, 100)10 GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)12 select * from titleauthor where au_id is null;3
GOau_id title_id au_ord royaltyper——————–
——————– —— ———–NULL 2 1 60

(1 rows affected)1 select * from titleauthor where au_id = null;2
GOau_id title_id au_ord royaltyper——————–


(0 rows affected)

–创建Customers表create table Customers( custid INT NOT NULL IDENTITY,
companyname NVARCHAR(40) NOT NULL, country NVARCHAR(15) NOT NULL,
constraint pk_customer primary key(custid));

–创建Orders表

代码如下复制代码

CREATE TABLE Orders( orderid INT NOT NULL IDENTITY, custid INT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid), CONSTRAINT
FK_Orders_Customers FOREIGN KEY(custid) REFERENCES
Customers(custid),); set identity_insert Customers on;

INSERT INTO Customers(custid, companyname,country) VALUES(1, N’大众’,
N’中国’); INSERT INTO Customers(custid, companyname,country) VALUES(2,
N’宝马’, N’美国’); INSERT INTO Customers(custid, companyname,country)
VALUES(3, N’奔驰’, N’中国’); INSERT INTO Customers(custid,
companyname,country) VALUES(4, N’奇瑞’, N’德国’); INSERT INTO
Customers(custid, companyname,country) VALUES(5, N’福特’, N’美国’); set
identity_insert Customers off; set identity_insert Orders
on;–custid代表工作者号 INSERT INTO Orders(orderid, custid卡塔尔(قطر‎ VALUES(1,1卡塔尔;
INSERT INTO Orders(orderid, custid卡塔尔(قطر‎ VALUES(2,2卡塔尔(قطر‎; INSERT INTO
Orders(orderid, custidState of Qatar VALUES(3,3卡塔尔国; INSERT INTO Orders(orderid, custid卡塔尔VALUES(4,4State of Qatar; INSERT INTO Orders(orderid, custid卡塔尔VALUES(5,5卡塔尔国;–查看表的多寡select custid,companyname,country from
Customers;select orderid,custid from Orders;–插入数据成功

–我们回到正题,相比Exists与in,not exists与 not in

–查询来自华夏,而且下过订单的具有顾客

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’中国’and exists (select * from Orders as O where
O.custid=C.custid);–返回–custid companyname–1 大众–3 奔驰

–外界查询重回来自华夏的客商信息,对于这么些客商,exists谓词在Orders表查找是不是起码存在一个与外表顾客行消息相同的custid订单行

–用IN查询刚刚的供给

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’中国’and custid in(select custid from Orders卡塔尔;

–结果跟下边包车型客车回到同样的值

–下边包车型客车知识点大家必要意识到:–当列表中有NULL时,in实际会发出多个UNKNOWN的结果,举个例子a in(d,b,null卡塔尔的结果是UNKNOWN,而a not in (d,b,null卡塔尔(قطر‎再次来到的是not
unknowd仍为unknowd–而not in与not exists则结果会比比较差异,举例a
in(a,b,null卡塔尔再次来到的是TRUE,而a not in(a,b,null卡塔尔国重返的早晚是not
true即为false–有了上面的认知,好持续开工了….–我们几日前向Orders表插入一行数据

代码如下复制代码

set identity_insert Orders on;

insert into Orders(orderid,custid) values(6,null);

set identity_insert Orders off;

set identity_insert Customers on;

insert into Customers(custid,companyname,country卡塔尔国values(7,N’雷克萨斯’,N’美利哥’卡塔尔;

set identity_insert Customers off;

select * from Orders;select * from Customers;

–要是今后要重临来自米国且尚未订单的顾客

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’U.S.A.’and not exists (select * from Orders as O where
O.custid=C.custid 卡塔尔;–再次回到–custid companyname–7 Lexus

–大家再用IN方法

代码如下复制代码 select custid,companyname from Customers as Cwhere
country=N’美利坚同盟友’and custid not in(select custid from Orders卡塔尔;