sql中IN与EXISTS,NOT IN与NOT EXISTS的事必躬亲介绍

一经表中的有些列是可选的,那么大家能够在不向该列增加值的情况下插入新记录或更新本来就有的记录。那象征该字段将以
NULL 值保存。

set identity_insert Orders off;

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;它们是不等价的。

让市场职员测量试验,结果运营时刻在1分钟内。难题消除了,看来exists和in确实是要依照表的数据量来支配使用。

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)

本小说介绍一篇有关sql中IN与EXISTS,NOT IN与NOT
EXISTS的详实介绍,有亟待精晓的同学能够参谋一下。

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

–外界查询重回来自中华夏儿女民共和国的顾客消息,对于这一个客商,exists谓词在Orders表查找是否起码存在二个与表面客商行新闻一致的custid订单行

is not null实例

代码如下复制代码 SELECT au_lname, au_fname FROM authors WHERE au_id
IN (SELECT au_id FROM titleauthor WHERE royaltyper 50)

NULL 值的管理格局与别的值区别。

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’U.S.’and not exists (select * from Orders as O where
O.custid=C.custid 卡塔尔(قطر‎;–重返–custid companyname–7 雷克萨斯

is null 判定为空的源委

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

上边包车型客车询问检索已经不贩卖的书的名称:

–上边是情有可原的缓和办法

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

接着看

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

应用 EXISTS 和 NOT EXISTS
引进的子查询可用于二种集结原理的操作:交集与差集。七个聚众的滥竽充数包罗同不经常间归属五个原集合的享有因素。
差集带有只归属五个聚众中的第二个集聚的因素。
EXISTS:钦命贰个子查询,检验行的留存。 本示例所示查询检索由坐落于以字母 B
开头的城市中的任一出版商出版的书名:

代码如下复制代码 update p_container_decl cdset
cd.ANNUL_FLAG=’0001′,ANNUL_DATE = sysdatewhere exists(select 1from
(select tc.decl_no,tc.goods_nofrom p_transfer_cont tc,P_AFFIRM_DO
adwhere tc.GOODS_DECL_NO = ad.DECL_NOand ad.DECL_NO =
‘sssssssssssssssss’) awhere a.decl_no = cd.decl_noand a.goods_no =
cd.goods_no)

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

create table t1 (c1 number,c2 number);create table t2 (c1 number,c2
number);

–用IN查询刚刚的要求

create database DbTest;go

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

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

set identity_insert Orders on;

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

地点提到的3个表的记录数都很大,均在百万左右。依据这种境况,笔者想开了近年看的tom的一篇文章,说的是exists和in的区分,in
是把外部和那表作hash
join,而exists是对外表作loop,每一回loop再对那表举办询问。那样的话,in符合内外表都超大的景色,exists契合外表结果集非常小的图景。

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

in相符内外表都十分大之处,exists切合外表结果集非常的小的动静。exists 和 in
使用一例 ===========================================================
明日市道报告有个sql及慢,运行须求20多分钟,如下:

–查询来自中华夏儿女民共和国,而且下过订单的装有客商

可是,假诺应用 IN,少键入一些字符也足以收获平等的结果:

select * from Orders;select * from Customers;

代码如下复制代码

代码如下复制代码 update p_container_decl cdset
cd.ANNUL_FLAG=’0001′,ANNUL_DATE = sysdatewhere (decl_no,goods_no)
in(select tc.decl_no,tc.goods_nofrom p_transfer_cont
tc,P_AFFIRM_DO adwhere tc.GOODS_DECL_NO = ad.DECL_NOand ad.DECL_NO
= ssssssssssss)

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

发表评论

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

网站地图xml地图