Question

怎么用用sql语句中的not exists来写语句？

Answer 1

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

Answer 2

使用 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)