SQL example: retrieve musicianID with no gigs played?

Question

Suppose you have two tables:

Musicians

• musicianID (primary key, int)
• name (varchar)
• instrument (varchar)

Gigs

• GigID (primary key, int)
• musicianID (foreign key from Musicians)
• location (varchar)

How would I retrieve the musicianIDs which have not played a gig i.e. that don't have a reference in the Gigs table?

Thanks!

Answer 1

For example:

SELECT *
FROM Musicians m
WHERE NOT EXISTS (
    SELECT 1
    FROM Gigs g
    WHERE g.musicianID = m.musicianID
)

Here's the (e.g.) MySQL documentation on EXISTS and NOT EXISTS.

Answer 2

SELECT
   M.*
FROM Musicians M
  LEFT OUTER JOIN Gigs G
      ON G.musicianId = M.musicianID
WHERE G.musicianId IS NULL

Answer 3

This may be more efficient than the query suggested by martin because it doesn't use a subquery:

select musicianID
from musicians m
left join gigs g on g.musicianId = m.musicianID
where g.musicianId is null