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