使用 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)
select * from (select * from a)b where ... 就是在查询里面再查询咯
--IN 与子查询一起使用 USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper < 50)
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'
USE pubs SELECT pub_name --父查詢 FROM publishers WHERE EXISTS (SELECT * --子查詢 FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')
使用 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)
就是在查询里面再查询咯
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE royaltyper < 50)
子查询可以嵌套在 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'
SELECT pub_name --父查詢
FROM publishers
WHERE EXISTS
(SELECT * --子查詢
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')