您可以始终使用“不存在”而不是“不是”？

发布于 2025-01-17 20:47:05 字数 425 浏览 0 评论 0原文

我有以下模式：书（ISBN，标题，类型）已发布（ISBN，PublisherNumber）

查询：发布者的出版商号码是什么，这些发布者专门出版了类型的“计算机科学”书籍？

我尝试使用“不在中”构建查询，并且效果很好。

SELECT p.Publishernumber 

FROM published p , book b 

WHERE b.ISBN=p.ISBN AND b.Genre = "CS" AND p.publishernumber not in  (

SELECT p.publishernumber 

 FROM Book b , published p 

 WHERE b.ISBN=p.ISBN AND b.Genre != "CS" );

如何使用“不存在”构建相同的查询？

原文

I have the Following Schema :
Book(ISBN,Title,Genre)
Published(ISBN,Publishernumber)

The Query : What are the publisher numbers of the publishers that exclusively publish books of the genre “Computer Science”?

I tried building the query using "Not in" and it worked fine.

SELECT p.Publishernumber 

FROM published p , book b 

WHERE b.ISBN=p.ISBN AND b.Genre = "CS" AND p.publishernumber not in  (

SELECT p.publishernumber 

 FROM Book b , published p 

 WHERE b.ISBN=p.ISBN AND b.Genre != "CS" );

How can i build the same query using "Not Exists" ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

还不是爱你 2025-01-24 20:47:05

要使用NOT EXISTS，您需要使用相关子查询。

SELECT p.Publishernumber 
FROM published p 
JOIN book b ON b.ISBN=p.ISBN
WHERE b.Genre = "CS" AND NOT EXISTS  (
    SELECT 1  
    FROM Book b
    JOIN published p2 ON b.ISBN=p.ISBN
    WHERE b.Genre != "CS" AND p2.publishernumber = p.publishernumber);

To use NOT EXISTS you need to use a correlated subquery.

SELECT p.Publishernumber 
FROM published p 
JOIN book b ON b.ISBN=p.ISBN
WHERE b.Genre = "CS" AND NOT EXISTS  (
    SELECT 1  
    FROM Book b
    JOIN published p2 ON b.ISBN=p.ISBN
    WHERE b.Genre != "CS" AND p2.publishernumber = p.publishernumber);

回复收藏 0 原文

~没有更多了~