Menu
Woocommerce Menu

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

0 Comment

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)

3522vip靠谱吗,(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)

use master;

is null 判断为空的内容

而我目前的情况适合用in来作查询,于是我改写了sql,如下:

NULL 值的处理方式与其他值不同。

select * from Orders;select * from Customers;

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

代码如下复制代码 SELECT ProductID, ProductName FROM
Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5)

is not null实例

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’美国’and not exists (select * from Orders as O where
O.custid=C.custid );–返回–custid companyname–7 雷克萨斯

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

代码如下复制代码

注释:无法比较 NULL 和 0;它们是不等价的。

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;–插入数据成功

如果表中的某个列是可选的,那么我们可以在不向该列添加值的情况下插入新纪录或更新已有的记录。这意味着该字段将以
NULL 值保存。

–所以在含有NULL值的列的时候,就要小心了,not exists与not
in在逻辑上是不等价的

–用IN查询刚刚的需求

select * from t1 where c2 not in (select c2 from t2);no rows
foundselect * from t1 where not exists (select 1 from t2 where
t1.c2=t2.c2);c1 c21 3

代码如下复制代码

代码如下复制代码 SELECT ProductID, ProductName FROM
Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR
CategoryID = 5

下面的查询查找已经不销售的书的名称:

–下面的知识点我们需要认识到:–当列表中有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表插入一行数据

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

set identity_insert Customers on;

使用 EXISTS 和 NOT EXISTS
引入的子查询可用于两种集合原理的操作:交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。
差集包含只属于两个集合中的第一个集合的元素。
EXISTS:指定一个子查询,检测行的存在。 本示例所示查询查找由位于以字母 B
开头的城市中的任一出版商出版的书名:

正如所看到的,not in
出现了不期望的结果集,存在逻辑错误。如果看一下上述两个select语句的执行计划,也会不同。后者使用了hash_aj。因此,请尽量不要使用not
in(它会调用子查询),而尽量使用not
exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值,则查询将不返回任何记录,正如上面例子所示。除非子查询字段有非空限制,这时可以使用not
in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接。

–创建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));

请看下面的例子:

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

结果显示有一些作者属于少于 50% 的一类。 NOT IN:通过 NOT IN
关键字引入的子查询也返回一列零值或更多值。
以下查询查找没有出版过商业书籍的出版商的名称。

in适合内外表都很大的情况,exists适合外表结果集很小的情况。exists 和 in
使用一例 ===========================================================
今天市场报告有个sql及慢,运行需要20多分钟,如下:

代码如下复制代码 SELECT pub_name FROM publishers WHERE NOT EXISTS
(SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
‘business’)

代码如下复制代码

insert into t1 values (1,2);insert into t1 values (1,3);insert into t2
values (1,2);insert into t2 values (1,null);

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;

–下面是正确的解决方法

接着看

请注意not in 逻辑上不完全等同于not exists,如果你误用了not
in,小心你的程序存在致命的BUG:

–咱们回到正题,比较Exists与in,not exists与 not in

create database DbTest;go

发表评论

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

相关文章

网站地图xml地图