EXISTS:指定一个子查询,检测行的存在。
本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:
SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
'business')
SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')
两者的区别:
EXISTS:后面可以是整句的查询语句如:SELECT * FROM titles
IN:后面只能是对单列:SELECT pub_id FROM titles
NOT EXISTS:
例如,要查找不出版商业书籍的出版商的名称:
SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
'business')
下面的查询查找已经不销售的书的名称:
SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id)