How to JOIN each row ON NOT IN ( SELECT … )?

Say I have a table of users, songs, and songs played.

users
id: int

songs
id: int

songs_played
user_id: int
song_id: int

If I want to, for each user, find a song that has not yet been played. How do I do that in one query?

It’s pretty straightforward for me to just do something like:

user_ids = SELECT id FROM users;
for user_id in user_ids:
    SELECT *
    FROM songs AS s 
    WHERE s.id NOT IN (
        SELECT p.song_id
        FROM songs_played 
        WHERE id = user_id
    )
    ORDER BY RANDOM()
    LIMIT 1;

But that’s pretty inefficient.

Thanks!

You can use not exists clause to do this.

SELECT
    u. ID AS user_id,
    s. ID AS song_id
FROM
    songs s,
    users u
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            user_songs us
        WHERE
            us.user_id = u. ID
        AND us.song_id = s. ID
    )
ORDER BY
    1,
    2

Another way would be to use EXCEPT like this:

SELECT
  u.id AS user_id,
  s.id AS song_id
FROM
  users AS u
  CROSS JOIN songs AS s

EXCEPT

SELECT
  user_id,
  song_id
FROM
  songs_played
;

The result would be all u.id, s.id pairs that are not found in songs_played.