嵌套子查询和相关子查询的区别

Question

请大虾讲件嵌套子查询和相关子查询的区别，并给个例子many thanks

Answer 1

UPDATE、DELETE 和 INSERT 语句中的子查询
子查询可以嵌套在 UPDATE、DELETE 和 INSERT 语句以及 SELECT 语句中。下面的查询使由 New Moon Books 出版的所有书籍的价格加倍。该查询更新 titles 表；其子查询引用 publishers 表。UPDATE titles
SET price = price * 2
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE pub_name = 'New Moon Books')下面是使用联接的等效 UPDATE 语句：UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
   AND pub_name = 'New Moon Books'通过下面嵌套的查询，可以删除商业书籍的所有销售记录：DELETE sales
WHERE title_id IN
   (SELECT title_id
   FROM titles
   WHERE type = 'business')下面是使用联接的等效 DELETE 语句：DELETE sales
FROM sales INNER JOIN titles ON sales.title_id = titles.title_id
   AND type = 'business'

Answer 2

使用 EXISTS 的子查询
使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回 TRUE 或 FALSE 值。使用 EXISTS 引入的子查询语法如下：WHERE[NOT]EXISTS(subquery)下面的查询查找所有出版商业书籍的出版商的名称：USE pubs
SELECT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
      AND type = 'business')下面是结果集：pub_name
--------------------
New Moon Books
Algodata Infosystems(2 row(s) affected)若要确定该查询的结果，请按照顺序考虑每个出版商的名称。该值是否会使子查询至少返回一行？换句话说，该值是否会使存在测试的计算值为 TRUE？在此例中，第一个出版商的名称为 Algodata Infosystems，标识号为 1389。titles 表中是否有 pub_id 为 1389 并且 type 为 business 的行？如果有，那么 Algodata Infosystems 应为所选值之一。对其它每个出版商名称重复相同的过程。注意，使用 EXISTS 引入的子查询在以下几方面与其它子查询略有不同： EXISTS 关键字前面没有列名、常量或其它表达式。
由 EXISTS 引入的子查询的选择列表通常几乎都是由星号 (*) 组成。由于只是测试是否存在符合子查询中指定条件的行，所以不必列出列名。 
由于通常没有备选的、非子查询的表示法，所以 EXISTS 关键字很重要。尽管一些使用 EXISTS 表示的查询不能以任何其它方法表示，但所有使用 IN 或由 ANY 或 ALL 修改的比较运算符的查询都可以通过 EXISTS 表示。 以下是使用 EXISTS 和等效的备选方法的查询示例。 有两种方法可以找到与出版商住在同一城市中的作者：USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city =ANY
   (SELECT city
   FROM publishers)
-- Or
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)下面是任一查询的结果集：au_lname         au_fname
--------         --------
Carson         Cheryl
Bennet         Abraham(2 row(s) affected)以下两个查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名：USE pubs
SELECT title
FROM titles
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE city LIKE 'B%')
— 或
USE pubs
SELECT title
FROM titles
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE pub_id = titles.pub_id
      AND city LIKE 'B%')下面是任一查询的结果集：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(11 row(s) affected)

Answer 3

使用 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，而且会得到相同的答案。而对子查询来说，情况则并非如此。

Answer 4

使用 NOT EXISTS 的子查询
NOT EXISTS 与 EXISTS 的工作方式类似，只是如果子查询不返回行，那么使用 NOT EXISTS 的 WHERE 子句会得到令人满意的结果。例如，要查找不出版商业书籍的出版商的名称：USE pubs
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
      AND type = 'business')下面是结果集：pub_name                                 
---------------------------------------- 
Binnet & Hardley                         
Five Lakes Publishing                    
Ramona Publishers                        
GGG&G                                    
Scootney Books                           
Lucerne Publishing                       (6 row(s) affected)下面的查询查找已经不销售的书的名称。USE pubs
SELECT title
FROM titles
WHERE NOT EXISTS
   (SELECT title_id
   FROM sales
   WHERE title_id = titles.title_id)下面是结果集：title
----------------------------------
The Psychology of Computer Cooking
Net Etiquette(2 row(s) affected)

Answer 5

使用 NOT IN 的子查询
通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。以下查询查找没有出版过商业书籍的出版商的名称。USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')除了用 NOT IN 代替 IN，该查询与"使用 IN 的子查询"中的查询完全相同。但是，该语句无法转换为一个联接。这种类似的不等于联接有不同的含义：它会查找曾出版过一些书但不是商业书籍的出版商的名称。