mysql - 如何从一个表中获取具有特定值或不存在于与第一个表具有 1-1 关系的另一个表中的行？

Question

我有两个表:

CREATE TABLE tblEatables (
    `EatId` int UNSIGNED PRIMARY AUTO_INCREMENT,
    `Fruits` varchar(9) NOT NULL
) Engine=InnoDB;

CREATE TABLE tblConfirm_Eatables (
    Eatables_Id INT UNSIGNED,
    Edible_Status INT,
    FOREIGN KEY Eatables_Id REFERENCES tblEatables (EatId)
) Engine=InnoDB;

我想选择 tblConfirm_Eatables 中 Edible_Status 为 0 的所有 tblEatables.Fruits，以及不在 tblConfirm_Eatables 中的那些。

示例数据:

INSERT INTO tblEatables
(`EatId`, `Fruits`)
    VALUES
(1, 'Apples'),
(2, 'Oranges'),
(3, 'Papaya'),
(4, 'Jackfruit'),
(5, 'Pineapple'),
(6, 'Mango');

INSERT INTO tblConfirm_Eatables
    VALUES
(1,0),
(2,1),
(3,0),
(4,0);

结果应该是:

  Fruits  Apple  Papaya  Jackfruit  Pineapple  Mango

请注意“Orange”不存在，因为它的可食用状态为“1”。

Answer 1

您可以使用 LEFT JOIN 来首先确定该水果是否存在于另一个表中并且是否具有非 0 的 Edible_Status。

SELECT    a.Fruits
FROM      tblEatables a
LEFT JOIN tblConfirm_Eatables b ON 
          a.EatId = b.Eatables_Id AND
          b.Edible_Status > 0
WHERE     b.Eatables_Id IS NULL

然后 WHERE 子句获取 tblEatables 中不满足连接条件的所有行。

如果您在 tblConfirm_Eatables 表中的字段(Eatables_Id、Edible_Status)上构建复合索引，这会非常快。

---

SQLFiddle Demo