了解MySQL中三个表的联接

我准备了一些例子：https://dbfiddle.uk/mb1_Vlc0

我在表中放置了名称为的附加列。 所以，现在我们有两个同名的电视节目，用于不同的频道：NBC和NBC2。 在 NBC 上，这是喜剧，但在 NBC2 上，这是侦探。 您的第一个示例完全排除了标题“有趣的标题”，但您的第二个查询将为其中一个频道保留此名称。tv_showschannel

现在，如果您需要 sercond 查询的第一个逻辑，则需要进行设计。

数据模型：

insert into tv_shows values
(1, 'First title', 'NBC'),
(2, 'Second title', 'SomeTVShowChannel'),
(3, 'Some title', 'NBC'),
(4, 'Funny title', 'NBC'),
(5, 'Funny title', 'NBC2');
insert into tv_genres values
(1, 'Comedy'),
(2, 'Detective');
insert into tv_show_genres values
(1, 1, 1),
(2, 2, 2),
(3, 3, 2),
(4, 4, 1),
(5, 5, 2);

我的链接截图仅用于演示。

使用测试数据和查询

select *
from tv_shows as s
left join tv_show_genres as sg on sg.show_id=s.id
left join tv_genres g on g.id=sg.genre_id

输出为

A） 正如 @nbk 所评论的那样，一些具有多种类型的标题不会因条件而被淘汰WHERE g.`name` != "Comedy"

B） 如果未为某些分配，则条件等于条件 所以： “喜剧”=null - 是假的 “喜剧”！=null - 是假的，或者
g.name = null - 是假的 g.name ！= null - 是假


的

genre_idtitleWHERE g.`name` != "Comedy"WHERE g.`name` != null

这种情况不能按预期工作。

C） 你的第一个查询可能最好这样写

SELECT title
FROM `tv_shows` AS t
WHERE id not in
    (
     SELECT show_id
     FROM `tv_show_genres` AS s
     INNER JOIN `tv_genres` AS g
           ON s.`genre_id` = g.`id`
     WHERE g.`name` = "Comedy"
   )
 ORDER BY `title`;

以避免出现多行相同
且此查询具有更好性能的情况。Title

在这里演示

让我们从第二个查询开始，它没有提供所需的结果：

SELECT DISTINCT `title`
FROM `tv_shows` AS t
LEFT JOIN `tv_show_genres` AS s
    ON s.`show_id` = t.`id`
LEFT JOIN `tv_genres` AS g
    ON g.`id` = s.`genre_id`
WHERE g.`name` != "Comedy";

首先，不要对字符串文字使用（双引号）。它可以工作（使用默认的MySQL配置），但非标准，应避免使用。请改用（单引号）。"Comedy"'Comedy'

其次，由于子句应用于最右边的表 （），因此联接将隐式更改为联接。WHEREtv_genres.nameLEFTINNER

正如 nbk 在评论中已经解释的那样，您的子句只是包含所有行 where ，这与“没有喜剧类型的电视节目”不同。WHEREg.name != 'Comedy'

现在，让我们跳回第一个查询：

SELECT DISTINCT `title`
  FROM `tv_shows` AS t
       LEFT JOIN `tv_show_genres` AS s
       ON s.`show_id` = t.`id`

       LEFT JOIN `tv_genres` AS g
       ON g.`id` = s.`genre_id`
       WHERE t.`title` NOT IN
             (SELECT `title`
                FROM `tv_shows` AS t
                 INNER JOIN `tv_show_genres` AS s
             ON s.`show_id` = t.`id`

             INNER JOIN `tv_genres` AS g
             ON g.`id` = s.`genre_id`
             WHERE g.`name` = "Comedy"'Comedy')
 ORDER BY `title`;

外部查询中的两个联接什么也没做，所以我们可以摆脱它们。这也意味着不再需要。我们应该使用 ，而不是在 上加入 （for），它有一个额外的好处，即不需要加入子查询：LEFTDISTINCTt.`title` NOT INtitleidtv_shows

SELECT `title`
FROM `tv_shows` AS t
WHERE t.`id` NOT IN (
    SELECT `show_id`
    FROM `tv_show_genres` AS s
    INNER JOIN `tv_genres` AS g
        ON g.`id` = s.`genre_id`
    WHERE g.`name` = 'Comedy'
)
 ORDER BY `title`;

这可以重写为：LEFT JOIN ... IS NULL

SELECT `title`
FROM `tv_shows` AS t
LEFT JOIN `tv_show_genres` AS s
    ON s.`show_id` = t.`id`
    AND s.`genre_id` = (SELECT id FROM `tv_genres` WHERE `name` = 'Comedy')
WHERE s.`genre_id` IS NULL
ORDER BY `title`;

或：NOT EXISTS

SELECT *
FROM tv_shows s
WHERE NOT EXISTS (
    SELECT 1
    FROM tv_show_genres sg
    JOIN tv_genres g ON sg.genre_id = g.id
    WHERE sg.show_id = s.id
    AND g.name = 'Comedy'
)
ORDER BY title;

这是一个 db<>小提琴。

请注意，我已经更改了 db<>fiddle 中的索引。除了少数例外，数据透视表需要双向复合指数 - 和次要 .tv_show_genresPK(show_id, genre_id)(genre_id, show_id)