How to find a value that doesn't exist in one table through another table in SQL-CodePudding

Publisher Table

Book Table

These are the 2 tables in my SQL query and I want to find out the publisher that has not published any book

I use the SQL Server and I used:

SELECT PublisherID 
FROM Publisher 
WHERE PublisherID NOT IN (SELECT PublisherID FROM Book);

but it doesn't work. Am I doing something wrong?

This is the result for this query Result Table

CodePudding user response：

You have to use condition PublisherID IS NOT NULL inside sub query. Your NULL record was returning false for all rows.

SELECT PublisherID 
FROM Publisher 
WHERE PublisherID NOT IN (SELECT PublisherID FROM Book WHERE PublisherID IS NOT NULL);

Alternativaly you can use LEFT JOIN and add a condition with WHERE b.PublisherID IS NULL.

SELECT *
FROM Publisher p
    LEFT JOIN Book b
        ON b.PublisherID = p.PublisherID
WHERE b.PublisherID IS NULL