http://cr173.com/html/13104_1.html
先看一下关于 IN 的帮助
IN 操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。
SQL IN 语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
原始的表 (在实例中使用:)
Persons 表:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
IN 操作符实例
现在,我们希望从上表中选取姓氏为 Adams 和 Carter 的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
结果集:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
3 | Carter | Thomas | Changan Street | Beijing |
IN
确定给定的值是否与子查询或列表中的值相匹配。
语法
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
参数
test_expression
是任何有效的 Microsoft® sql server™ 表达式。
subquery
是包含某列结果集的子查询。该列必须与 test_expression 有相同的数据类型。
expression [,...n]
一个表达式列表,用来测试是否匹配。所有的表达式必须和 test_expression 具有相同的类型。
结果类型
布尔型
结果值
如果 test_expression 与 subquery 返回的任何值相等,或与逗号分隔的列表中的任何expression 相等,那么结果值就为 TRUE。否则,结果值为 FALSE。
使用 NOT IN 对返回值取反。
示例
A. 对比 OR 和 IN
下面的示例选择名称和州的列表,列表中列出所有居住在加利福尼亚、印地安纳或马里兰州的作者。
USE pubs
SELECT au_lname, state
FROM authors
WHERE state = 'CA' OR state = 'IN' OR state = 'MD'
但是,也可以使用 IN 获得相同的结果:
USE pubs
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
以下是上面任一查询的结果集:
au_lname state
-------- -----
White CA
Green CA
Carson CA
O'Leary CA
Straight CA
Bennet CA
Dull CA
Gringlesby CA
Locksley CA
Yokomoto CA
DeFrance IN
Stringer CA
MacFeather CA
Karsen CA
Panteley MD
Hunter CA
McBadden CA
(17 row(s) affected)
B. 将 IN 与子查询一起使用
下面的示例在 titleauthor 表中查找从任一种书得到的版税少于 50% 的所有作者的au_ids,然后从 authors 表中选择 au_ids 与 titleauthor 查询结果匹配的所有作者的姓名。结果显示有一些作者属于得到的版税少于 50% 的一类。
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE royaltyper < 50)
下面是结果集:
au_lname au_fname
---------------------------------------- --------------------
Green Marjorie
O'Leary Michael
Gringlesby Burt
Yokomoto Akiko
MacFeather Stearns
Ringer Anne
(6 row(s) affected)
C. 将 NOT IN 与子查询一起使用
NOT IN 将找到那些与值列表中的项目不匹配的作者。下面的示例查找至少有一种书取得不少于 50% 的版税的作者姓名:
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id NOT IN
(SELECT au_id
FROM titleauthor
WHERE royaltyper < 50)
下面是结果集:
au_lname au_fname
---------------------------------------- --------------------
White Johnson
Carson Cheryl
Straight Dean
Smith Meander
Bennet Abraham
Dull Ann
Locksley Charlene
Greene Morningstar
Blotchet-Halls Reginald
del Castillo Innes
DeFrance Michel
Stringer Dirk
Karsen Livia
Panteley Sylvia
Hunter Sheryl
McBadden Heather
Ringer Albert
(17 row(s) affected)
使用 IN 的子查询
通过 IN(或 NOT IN)引入的子查询结果是一列零值或更多值。子查询返回结果之后,外部查询将利用这些结果。
下列查询会找到所有曾出版过商业书籍的出版商的名称。
USE pubs SELECT pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')
下面是结果集:
pub_name ---------------------------------------- Algodata Infosystems New Moon Books (2 row(s) affected)
该语句分两步进行评估。首先,内部查询返回出版过商业书籍的出版商的标识号(1389 和 0736)。然后,这些值被代入外部查询中,在 publishers 中查找与上述标识号相配的名字。
USE pubs SELECT pub_name FROM publishers WHERE pub_id in ('1389', '0736')
使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于,联接使您可以在结果中显示多个表中的列。例如,如果要在结果中包括商业书籍的书名,就必须使用联接来查询。
USE pubs SELECT pub_name, title FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id AND type = 'business'
下面是结果集:
pub_name title ---------------------- ------------------------------------------------- Algodata Infosystems The Busy Executive's Database Guide Algodata Infosystems Cooking with Computers: Surreptitious Balance Sheets New Moon Books You Can Combat Computer Stress! Algodata Infosystems Straight Talk About Computers (4 row(s) affected)
该查询显示出联接产生四行,而不是像前面的子查询那样产生两行。
下面是查询的另一个示例,它既可用子查询亦可用联接来表达。该查询查找所有住在 California,并且收到的某本书的版税低于 30 % 的第二作者的姓名。
USE pubs SELECT au_lname, au_fname FROM authors WHERE state = 'CA' AND au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper < 30 AND au_ord = 2)
下面是结果集:
au_lname au_fname ---------------------------------------- -------------------- MacFeather Stearns (1 row(s) affected)
评估内部查询后,产生符合子查询限定条件的三个作者的 ID 号。然后评估外部查询。注意,在内部和外部查询的 WHERE 子句中,都可以包括多个条件。
使用联接,同一查询可以用如下方式表示:
USE pubs SELECT au_lname, au_fname FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id WHERE state = 'CA' AND royaltyper < 30 AND au_ord = 2
联接总是可以表示为子查询。子查询经常(但不总是)可以表示为联接。这是因为联接是对称的:可以以任意顺序将表 A 联接到表 B,而且会得到相同的答案。而对子查询来说,情况则并非如此。
IN 与 EXISTS 的区别
IN
确定给定的值是否与子查询或列表中的值相匹配。
EXISTS
指定一个子查询,检测行的存在。
比较使用 EXISTS 和 IN 的查询
这个例子比较了两个语义类似的查询。
第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
-- Or, using the IN clause:
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
下面是任一查询的结果集:
pub_name
----------------------------------------
Algodata Infosystems
New Moon Books
exits 相当于存在量词:表示集合存在,也就是集合不为空只作用一个集合.
例如 exist P表示P不空时为真; not exist P表示p为空时为真in表示一个标量和一元关系的关系。
例如:s in P表示当s与P中的某个值相等时 为真; s not in P 表示s与P中的每一个值都不相等时 为真
在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢?
1)性能上的比较
比如Select * from T1 where x in ( select y from T2 )
执行的过程相当于:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
相对的
select * from t1 where exists ( select null from t2 where y = x )
执行的过程相当于:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
表 T1 不可避免的要被完全扫描一遍
分别适用在什么情况?
以子查询 ( select y from T2 )为考虑方向
如果子查询的结果集很大需要消耗很多时间,但是T1比较小执行( select null from t2 where y = x.x )非常快,那么exists就比较适合用在这里
相对应得子查询的结果集比较小的时候就应该使用in.
in和exists
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。
一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的
2:
select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'的结果是相同的