Alex Alex - 5 months ago 14
MySQL Question

SQL: How to join tables right?

Suppose I have 3 tables:

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
);


I need to get all empty zoos. I've tried to join them using the next script:

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


But it returns all not empty zoos instead of required empty zoos. What am I doing wrong?

Answer

No need for a JOIN at all:

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

With a 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;