主要区别是: exists主要用于片面的,有满足一个条件的即可, in 主要用于具体的集合操作, 有多少满足条件.
exists是判断是否存在这样的记录, in 是判断某个字段是否在指定的某个范围内。 exists快一些吧
比较使用 EXISTS 和 IN 的查询 这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。USE pubs GO SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business') GO-- Or, using the IN clause:USE pubs GO SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business') GO下面是任一查询的结果集:pub_name ---------------------------------------- Algodata Infosystems New Moon Books (2 row(s) affected)--当数据量大时,采用exists效率更高
楼上好多高人 我只知道数据多的时候用exists快不少
SELECT title FROM titles WHERE EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%') GO-- Or, using IN: SELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE 'B%') GO下面是任一查询的结果集:title ------------------------------------------------------------------------ The Busy Executive's Database Guide Cooking with Computers: Surreptitious Balance Sheets You Can Combat Computer Stress! Straight Talk About Computers But Is It User Friendly? Secrets of Silicon Valley Net Etiquette Is Anger the Enemy? Life Without Fear Prolonged Data Deprivation: Four Case Studies Emotional Security: A New Algorithm
exists主要用于片面的,有满足一个条件的即可,
in 主要用于具体的集合操作, 有多少满足条件.
in 是判断某个字段是否在指定的某个范围内。
exists快一些吧
这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
GO-- Or, using the IN clause:USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
GO下面是任一查询的结果集:pub_name
----------------------------------------
Algodata Infosystems
New Moon Books (2 row(s) affected)--当数据量大时,采用exists效率更高
我只知道数据多的时候用exists快不少
SELECT title
FROM titles
WHERE EXISTS
(SELECT *
FROM publishers
WHERE pub_id = titles.pub_id
AND city LIKE 'B%')
GO-- Or, using IN:
SELECT title
FROM titles
WHERE pub_id IN
(SELECT pub_id
FROM publishers
WHERE city LIKE 'B%')
GO下面是任一查询的结果集:title
------------------------------------------------------------------------
The Busy Executive's Database Guide
Cooking with Computers: Surreptitious Balance Sheets
You Can Combat Computer Stress!
Straight Talk About Computers
But Is It User Friendly?
Secrets of Silicon Valley
Net Etiquette
Is Anger the Enemy?
Life Without Fear
Prolonged Data Deprivation: Four Case Studies
Emotional Security: A New Algorithm
从速度来就前者比较快!
exists主要用于片面的,有满足一个条件的即可,
in 主要用于具体的集合操作, 有多少满足条件.