【例6.34】在样例数据库pubs中,查询出版过图书的出版社
USE pubs
SELECT * FROM publishers
WHERE pub_id IN (SELECT pub_id FROM titles)
ORDER BY pub_name
运行结果:
pub_id pub_name city state country
------ --------------------------------------------------------
1389 Algodata Infosystems Berkeley CA USA
0877 Binnet & Hardley Washington DC USA
0736 New Moon Books Boston MA USA
(所影响的行数为 3 行)
此例中,首先,SELECT pub_id FROM titles子查询从titles表中返回了1389,0877,0736三个pub_id,然后外部查询SELECT * FROM publishers WHERE pub_id IN (1389,0877,0736)语句查询出最后结果。
【例6.35】在样例数据库pubs中,查询居住在出版社Algodata Infosystems所在城市的作者。
USE pubs
SELECT au_lname,au_fname,city FROM authors
WHERE city = (SELECT city FROM publishers WHERE pub_name ='Algodata Infosystems')
运行结果:
au_lname au_fname city
---------------------------------------- -----
Carson Cheryl Berkeley
Bennet Abraham Berkeley
(所影响的行数为 2 行)
此例中,首先,SELECT city FROM publishers WHERE pub_name = 'Algodata Infosystems'子查询从publishers表中返回了Algodata Infosystems出版社的所在城市Berkeley,然后外部查询SELECT au_lname,au_fname,city FROM authors WHERE city = ('Berkeley')语句查询出最后结果。此例中,使用“=”运算符,当然还可以使用其它运算符引入子查询。
【例6.36】在样例数据库pubs中,查询作者收到的预付款大于New Moon Books出版社支付的最低预付款金额的书名及预付款。
USE pubs
SELECT title,advance FROM titles
WHERE advance > ANY (SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books')
ORDER BY advance
运行结果:
title advance
-------------------------------------------------------------------------------
Is Anger the Enemy? 2275.0000
Emotional Security,A New Algorithm 4000.0000
Fifty Years in Buckingham Palace Kitchens 4000.0000
The Busy Executive's Database Guide 5000.0000
Cooking with Computers,Surreptitious Balance Sheets 5000.0000
Straight Talk About Computers 5000.0000
Life Without Fear 6000.0000
Onions,Leeks,and Garlic,Cooking Secrets of the Mediterranean 7000.0000
Computer Phobic AND Non-Phobic Individuals,Behavior Variations 7000.0000
But Is It User Friendly? 7000.0000
Secrets of Silicon Valley 8000.0000
Sushi,Anyone? 8000.0000
You Can Combat Computer Stress! 10125.0000
The Gourmet Microwave 15000.0000
(所影响的行数为 14 行)
此例中,首先,SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books'子查询返回了New Moon Books出版社的所有书籍的预付款(2000.0000,2275.0000,4000.0000,6000.0000,10125.0000),而ANY关键字的含义是任何一个值,所以>ANY的取值为大于集合中的最小值,即>2000.0000;然后外部查询SELECT title,advance FROM titles WHERE advance>2000.0000语句查询出最后结果。
【例6.37】在样例数据库pubs中,查询作者收到的预付款大于New Moon Books出版社支付的最高预付款金额的书名及预付款。
USE pubs
SELECT title,advance FROM titles
WHERE advance >ALL (SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books')
ORDER BY advance
运行结果:
title advance
--------------------------------------------------
The Gourmet Microwave 15000.0000
(所影响的行数为 1 行)
此例中,首先,SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books'子查询返回了New Moon Books出版社的所有书籍的预付款(2000.0000,2275.0000,4000.0000,6000.0000,10125.0000),而ALL关键字的含义是每一个值,所以>ALL的取值为大于集合中的最大值,即>10125.0000;然后外部查询SELECT title,advance FROM titles WHERE advance>10125.0000语句查询出最后结果。
【例6.38】在样例数据库pubs中,查询出版过psychology类型的书籍的出版社名单。
USE pubs
SELECT * FROM publishers
WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'psychology')
运行结果:
pub_id pub_name city state country
------ ---------------------------------------- -------------
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
(所影响的行数为 2 行)
此例中,首先,EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'psychology')子查询测试是否存在出版过psychology类型书籍的出版社,如果存在,则外部查询从publisher表中查询出该出版社的信息。