如何加入每一行 ON NOT IN ( SELECT … )？

假設我有一張使用者表、歌曲表和播放的歌曲表。

users
id: int

songs
id: int

songs_played
user_id: int
song_id: int

如果我想，為每個使用者找到一首尚未播放的歌曲。如何在一個查詢中做到這一點？

對我來說，這樣做非常簡單：

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;

但這非常低效。

謝謝！

您可以使用 not exists 子句來執行此操作。

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

另一種方法是像這樣使用 EXCEPT：

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
;

結果將是u.id, s.id在 中找不到的所有對songs_played。

引用自：https://dba.stackexchange.com/questions/108069