mysql - SQL : How to join tables right?

Question

假设我有 3 个表:

CREATE TABLE animals 
(
    animal_id   INT PRIMARY KEY,
    animal_name VARCHAR(100)
);

CREATE TABLE zoos 
(
     zoo_id   INT PRIMARY KEY,
     zoo_name VARCHAR(100)
);

CREATE TABLE zoo_has_animals 
(
     zoo_id    INT,
     animal_id INT
);

我需要得到所有空的动物园。我尝试使用下一个脚本加入他们:

SELECT zoos.zoo_name
FROM zoos
LEFT JOIN zoo_has_animals ON zoos.zoo_id = zoo_has_animals.zoo_id;

但它返回所有非空动物园而不是所需的空动物园。我做错了什么？

Answer 1

根本不需要JOIN:

SELECT z.zoo_name
FROM zoos as z
WHERE NOT EXISTS(SELECT 1 FROM zoo_has_animals
                 WHERE zoo_id = z.zoo_id);

使用JOIN:

SELECT z.zoo_name
FROM zoos as z
LEFT JOIN zoo_has_animals as h
     ON z.zoo_id = h.zoo_id 
WHERE h.zoo_id IS NULL;