CODEWITHSUNDEEP
mysqlnot-exists
atassi122
atassi122

Reputation: 1

Can you always use "NOT EXISTS" instead of "Not in"?

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" ?

Upvotes: 0

Views: 33

Answers (1)

Barmar
Barmar

Reputation: 781210

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);

Upvotes: 2

Related Questions