Q:

SELECT statement that only shows rows where there is a NULL in a specific column

I've got an issue I've been racking my brain on this and the code I have makes sense to me but still doesn't work.
Here is the question:

Give me a list of the names of all the unused (potential) caretakers and the names and types of all unclaimed pieces of art (art that does not yet have a caretaker).

Here is how the tables are set up:

CareTakers: CareTakerID, CareTakerName
Donations: DonationID, DonorID, DonatedMoney, ArtName, ArtType, ArtAppraisedPrice, ArtLocationBuilding, ArtLocationRoom, CareTakerID
Donors: DonorID, DonorName, DonorAddress

Here is the code I have:
SELECT 
    CareTakerName, ArtName, ArtType
FROM 
    CareTakers 
JOIN 
    Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE 
    Donations.CareTakerID = ''

Any help would be very much appreciated!

A:

I would suggest two queries for the reasons I noted in my comment on the OP above... However, since you requested one query, the following should get you what you asked for, although the result sets are not depicted side-by-side.
SELECT 
    CareTakerName, ArtName, ArtType
FROM 
    CareTakers 
LEFT JOIN 
    Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE 
    NULLIF(Donations.CareTakerID,'') IS NULL
UNION    -- Returns a stacked result set
SELECT 
    CareTakerName, ArtName, ArtType
FROM 
    CareTakers 
RIGHT JOIN 
    Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE 
    NULLIF(CareTakers.CareTakerID,'') IS NULL

If this is not sufficient, I can supply two separate queries as I suggested above.
*EDIT: Included NULLIF with '' criteria to treat blank and NULL equally in the where clause.

