如果表中的某个列是可选的,那么我们可以在不向该列添加值的情况下插入新纪录或更新已有的记录。这意味着该字段将以
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, custid) VALUES(3,3); INSERT INTO Orders(orderid, custid)
VALUES(4,4); 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’美国’and not exists (select * from Orders as O where
O.custid=C.custid );–返回–custid companyname–7 雷克萨斯

–我们再用IN方法

代码如下复制代码 select custid,companyname from Customers as Cwhere
country=N’美国’and custid not in(select custid from Orders);

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注