sql select records that don't have relation in a second table

Question

I am trying to run an SQL query in phpMyAdmin in a MySQL database in order to get records that do not have corresponding records in another table.

E.g.

**album**
id, name

**track**
id, album_id, name

I need all album records that do not have relative track records.

I have tried

SELECT album.id
from album 
WHERE album.id NOT IN (
  SELECT track.album_id 
  FROM track 
  WHERE 1
  GROUP BY track.album_id
)

But unfortunately this crashes the MySQL service

I have also tried

SELECT a.id FROM album a
INNER JOIN track t
ON a.id = t.album_id
WHERE t.id IS NULL

but this doesn't work as expected (returns no results)

Answer 1

You can use EXISTS to check for the absence of a record in the related table.

SELECT album.id
FROM album 
WHERE NOT EXISTS (
    SELECT *
    FROM track 
    WHERE track.album_id = album.id
);

See: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html