CODEWITHSUNDEEP
mysqlsql
cryptomath
cryptomath

Reputation: 141

MySQL Query Produces Empty Set

How do I show the names of all artists that have not recorded any titles? If I first run the following query:

SELECT * FROM Titles WHERE ArtistID is null;

It produces an empty set.

The tables in question look like this:

Artists

ArtistID, ArtistName, City, Region, Country, EntryDate

Titles

TitleID, ArtistID, Title, StudioID, UPC, Genre,

There are no null records for Artists or Titles, e.g., each record has some data. Since the above query produces an empty set, does this mean that all artists have recorded titles?

Upvotes: 1

Views: 1820

Answers (4)

Ryan
Ryan

Reputation: 96

SELECT Artists.* 
FROM Artists 
WHERE Artists.ArtistID NOT IN (SELECT Titles.ArtistID FROM Titles)

This should work. It can be written in other ways, but this should suffice.

Upvotes: 0

jpw
jpw

Reputation: 44881

Since the above query produces an empty set, does this mean that all artists have recorded titles?

No, it means that all titles have an associated artist.

To get artists without any titles you would have to do something like:

SELECT Artist.* 
FROM Artist 
WHERE ArtistId NOT IN (SELECT DISTINCT ArtistId FROM Titles)

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Try something like this:

SELECT * 
FROM Artists
WHERE artistID NOT IN (
    SELECT artistID  
    FROM Titles
    );

Upvotes: 0

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

I think you are looking for the following:

SELECT ArtistName FROM Artists WHERE NOT ArtistID IN (SELECT t.ArtistID FROM Titles t)

An other option, which probably is a bit faster but a bit harder to read:

SELECT ArtistName FROM Artists LEFT JOIN Titles ON Titles.ArtistID = Artists.ArtistID WHERE TitleID IS NULL

Upvotes: 2

Related Questions