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

Question

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

Answer 1

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

---

To find out the publisher that has published more than 1 book you can use GROUP BY & HAVING with IN like below.

SELECT PublisherID 
FROM @Publisher 
WHERE PublisherID IN (
    SELECT PublisherID
    FROM @Book 
    WHERE PublisherID IS NOT NULL
    GROUP BY PublisherID
    HAVING COUNT(PublisherID) > 1
);

Answer 2

NOT IN can return incorrect results when NULL values are involved. Instead use a NOT EXISTS

SELECT p.PublisherID 
FROM Publisher p
WHERE NOT EXISTS (SELECT 1
    FROM Book b
    WHERE b.PublisherID = p.PublisherID
);