SQLite中如何通过连接表排除指定关联记录？

数据库结构 #

CREATE TABLE IF NOT EXISTS song_artist
(
    song INTEGER, 
    artist INTEGER,

    FOREIGN KEY("song") REFERENCES "Songs"("song_id") 
        ON DELETE CASCADE,
    FOREIGN KEY("artist") REFERENCES "Artists"("artist_id") 
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Artists 
(
    "artist_id" INTEGER PRIMARY KEY,
    "artist_name" TEXT
);

CREATE TABLE IF NOT EXISTS Songs 
(
    "song_id" INTEGER PRIMARY KEY,
    "song_name" TEXT
);

数据库数据 #

插入语句 #

INSERT INTO Artists (artist_name) VALUES ("Bob");
INSERT INTO Artists (artist_name) VALUES ("Shawn");

INSERT INTO Songs (song_name) VALUES ("one song");
INSERT INTO Songs (song_name) VALUES ("another song");

INSERT INTO song_artist (artist, song) VALUES (1, 1);
INSERT INTO song_artist (artist, song) VALUES (2, 1);
INSERT INTO song_artist (artist, song) VALUES (2, 2);

表数据 #

Artists表

Songs表

song_artist表

需求：查询仅Shawn参与、Bob未参与的歌曲名称，预期结果为another song。

初始错误查询 #

尝试的查询返回了两首歌，不符合预期：

SELECT song_name
FROM Songs
JOIN Artists ON Artists.artist_id = song_artist.artist
JOIN song_artist ON song_artist.song = Songs.song_id
WHERE Artists.artist_name = "Shawn"
  AND Artists.artist_name != "Bob"

查询结果：

原因：关联表song_artist中同一歌曲有多条记录，排除Bob的记录后，Shawn对应的记录仍会被保留，导致one song也被选中。

现有可行方案 #

以下查询可以得到正确结果，但在多表连接场景下冗长且效率较低：

SELECT song_name
FROM Songs
JOIN Artists ON Artists.artist_id = song_artist.artist
JOIN song_artist ON song_artist.song = Songs.song_id
WHERE Artists.artist_name = "Shawn"
  AND Songs.song_id NOT IN (SELECT song_id
                            FROM Songs
                            JOIN Artists ON Artists.artist_id = song_artist.artist
                            JOIN song_artist ON song_artist.song = Songs.song_id
                            WHERE Artists.artist_name == "Bob")

查询结果：

更优实现方式 #

方式1：LEFT JOIN + IS NULL #

通过左连接Bob参与的歌曲，筛选出未匹配的结果，同时确保歌曲有Shawn参与：

SELECT s.song_name
FROM Songs s
JOIN song_artist sa_shawn ON s.song_id = sa_shawn.song
JOIN Artists a_shawn ON sa_shawn.artist = a_shawn.artist_id
LEFT JOIN song_artist sa_bob ON s.song_id = sa_bob.song
LEFT JOIN Artists a_bob ON sa_bob.artist = a_bob.artist_id AND a_bob.artist_name = "Bob"
WHERE a_shawn.artist_name = "Shawn"
  AND a_bob.artist_id IS NULL

方式2：GROUP BY + HAVING 条件 #

通过分组统计歌曲的参与艺术家，筛选出仅包含Shawn的歌曲：

SELECT s.song_name
FROM Songs s
JOIN song_artist sa ON s.song_id = sa.song
JOIN Artists a ON sa.artist = a.artist_id
GROUP BY s.song_id, s.song_name
HAVING SUM(CASE WHEN a.artist_name = "Shawn" THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN a.artist_name = "Bob" THEN 1 ELSE 0 END) = 0

方式3：EXISTS + NOT EXISTS #

用EXISTS确保Shawn参与，NOT EXISTS确保Bob未参与，避免多次连接整张表：

SELECT s.song_name
FROM Songs s
WHERE EXISTS (
    SELECT 1
    FROM song_artist sa
    JOIN Artists a ON sa.artist = a.artist_id
    WHERE sa.song = s.song_id AND a.artist_name = "Shawn"
)
AND NOT EXISTS (
    SELECT 1
    FROM song_artist sa
    JOIN Artists a ON sa.artist = a.artist_id
    WHERE sa.song = s.song_id AND a.artist_name = "Bob"
)

这些方案都比原有的NOT IN子查询更简洁，且在数据量较大时效率更高，尤其是EXISTS的方式，因为它一旦找到匹配项就会停止检索，不需要遍历所有符合条件的记录。

内容的提问来源于stack exchange，提问作者user19610681