La consulta devuelve las coincidencias en lugar de las no coincidencias, en MySQL

Question

Estoy teniendo un problema con MYSQL y creo que el problema está en los JOIN, en cómo los estoy escribiendo, miren:
Yo tengo estas tablas, departamento, persona y profesor:

 CREATE TABLE departamento (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL
);

CREATE TABLE persona (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nif VARCHAR(9) UNIQUE,
    nombre VARCHAR(25) NOT NULL,
    apellido1 VARCHAR(50) NOT NULL,
    apellido2 VARCHAR(50),
    ciudad VARCHAR(25) NOT NULL,
    direccion VARCHAR(50) NOT NULL,
    telefono VARCHAR(9),
    fecha_nacimiento DATE NOT NULL,
    sexo ENUM('H', 'M') NOT NULL,
    tipo ENUM('profesor', 'alumno') NOT NULL
);
 
CREATE TABLE profesor (
    id_profesor INT UNSIGNED PRIMARY KEY,
    id_departamento INT UNSIGNED NOT NULL,
    FOREIGN KEY (id_profesor) REFERENCES persona(id),
    FOREIGN KEY (id_departamento) REFERENCES departamento(id)
);

y tengo estos valores:

    /* Departamento */
INSERT INTO departamento VALUES (1, 'Informática');
INSERT INTO departamento VALUES (2, 'Matemáticas');
INSERT INTO departamento VALUES (3, 'Economía y Empresa');
INSERT INTO departamento VALUES (4, 'Educación');
INSERT INTO departamento VALUES (5, 'Agronomía');
INSERT INTO departamento VALUES (6, 'Química y Física');
INSERT INTO departamento VALUES (7, 'Filología');
INSERT INTO departamento VALUES (8, 'Derecho');
INSERT INTO departamento VALUES (9, 'Biología y Geología');
 
 /* Persona */
INSERT INTO persona VALUES (1, '26902806M', 'Salvador', 'Sánchez', 'Pérez', 'Almería', 'C/ Real del barrio alto', '950254837', '1991/03/28', 'H', 'alumno');
INSERT INTO persona VALUES (2, '89542419S', 'Juan', 'Saez', 'Vega', 'Almería', 'C/ Mercurio', '618253876', '1992/08/08', 'H', 'alumno');
INSERT INTO persona VALUES (3, '11105554G', 'Zoe', 'Ramirez', 'Gea', 'Almería', 'C/ Marte', '618223876', '1979/08/19', 'M', 'profesor');
INSERT INTO persona VALUES (4, '17105885A', 'Pedro', 'Heller', 'Pagac', 'Almería', 'C/ Estrella fugaz', NULL, '2000/10/05', 'H', 'alumno');
INSERT INTO persona VALUES (5, '38223286T', 'David', 'Schmidt', 'Fisher', 'Almería', 'C/ Venus', '678516294', '1978/01/19', 'H', 'profesor');
INSERT INTO persona VALUES (6, '04233869Y', 'José', 'Koss', 'Bayer', 'Almería', 'C/ Júpiter', '628349590', '1998/01/28', 'H', 'alumno');
INSERT INTO persona VALUES (7, '97258166K', 'Ismael', 'Strosin', 'Turcotte', 'Almería', 'C/ Neptuno', NULL, '1999/05/24', 'H', 'alumno');
INSERT INTO persona VALUES (8, '79503962T', 'Cristina', 'Lemke', 'Rutherford', 'Almería', 'C/ Saturno', '669162534', '1977/08/21', 'M', 'profesor');
INSERT INTO persona VALUES (9, '82842571K', 'Ramón', 'Herzog', 'Tremblay', 'Almería', 'C/ Urano', '626351429', '1996/11/21', 'H', 'alumno');
INSERT INTO persona VALUES (10, '61142000L', 'Esther', 'Spencer', 'Lakin', 'Almería', 'C/ Plutón', NULL, '1977/05/19', 'M', 'profesor');
INSERT INTO persona VALUES (11, '46900725E', 'Daniel', 'Herman', 'Pacocha', 'Almería', 'C/ Andarax', '679837625', '1997/04/26', 'H', 'alumno');
INSERT INTO persona VALUES (12, '85366986W', 'Carmen', 'Streich', 'Hirthe', 'Almería', 'C/ Almanzora', NULL, '1971-04-29', 'M', 'profesor');
INSERT INTO persona VALUES (13, '73571384L', 'Alfredo', 'Stiedemann', 'Morissette', 'Almería', 'C/ Guadalquivir', '950896725', '1980/02/01', 'H', 'profesor');
INSERT INTO persona VALUES (14, '82937751G', 'Manolo', 'Hamill', 'Kozey', 'Almería', 'C/ Duero', '950263514', '1977/01/02', 'H', 'profesor');
INSERT INTO persona VALUES (15, '80502866Z', 'Alejandro', 'Kohler', 'Schoen', 'Almería', 'C/ Tajo', '668726354', '1980/03/14', 'H', 'profesor');
INSERT INTO persona VALUES (16, '10485008K', 'Antonio', 'Fahey', 'Considine', 'Almería', 'C/ Sierra de los Filabres', NULL, '1982/03/18', 'H', 'profesor');
INSERT INTO persona VALUES (17, '85869555K', 'Guillermo', 'Ruecker', 'Upton', 'Almería', 'C/ Sierra de Gádor', NULL, '1973/05/05', 'H', 'profesor');
INSERT INTO persona VALUES (18, '04326833G', 'Micaela', 'Monahan', 'Murray', 'Almería', 'C/ Veleta', '662765413', '1976/02/25', 'H', 'profesor');
INSERT INTO persona VALUES (19, '11578526G', 'Inma', 'Lakin', 'Yundt', 'Almería', 'C/ Picos de Europa', '678652431', '1998/09/01', 'M', 'alumno');
INSERT INTO persona VALUES (20, '79221403L', 'Francesca', 'Schowalter', 'Muller', 'Almería', 'C/ Quinto pino', NULL, '1980/10/31', 'H', 'profesor');
INSERT INTO persona VALUES (21, '79089577Y', 'Juan', 'Gutiérrez', 'López', 'Almería', 'C/ Los pinos', '678652431', '1998/01/01', 'H', 'alumno');
INSERT INTO persona VALUES (22, '41491230N', 'Antonio', 'Domínguez', 'Guerrero', 'Almería', 'C/ Cabo de Gata', '626652498', '1999/02/11', 'H', 'alumno');
INSERT INTO persona VALUES (23, '64753215G', 'Irene', 'Hernández', 'Martínez', 'Almería', 'C/ Zapillo', '628452384', '1996/03/12', 'M', 'alumno');
INSERT INTO persona VALUES (24, '85135690V', 'Sonia', 'Gea', 'Ruiz', 'Almería', 'C/ Mercurio', '678812017', '1995/04/13', 'M', 'alumno');
 
/* Profesor */
INSERT INTO profesor VALUES (3, 1);
INSERT INTO profesor VALUES (5, 2);
INSERT INTO profesor VALUES (8, 3);
INSERT INTO profesor VALUES (10, 4);
INSERT INTO profesor VALUES (12, 4);
INSERT INTO profesor VALUES (13, 6);
INSERT INTO profesor VALUES (14, 1);
INSERT INTO profesor VALUES (15, 2);
INSERT INTO profesor VALUES (16, 3);
INSERT INTO profesor VALUES (17, 4);
INSERT INTO profesor VALUES (18, 5);
INSERT INTO profesor VALUES (20, 6);

¿Qué pasa? Cuando intento hacer una consulta como esta:

/*Intento que me devuelva un listado con los departamentos que no tienen profesores asociados.*/
SELECT d.nombre
from persona p
JOIN departamento d on (p.id = d.id)
JOIN profesor f ON( p.id = f.id_departamento)
WHERE not p.tipo = 'profesor'
ORDER BY d.nombre ASC;

Me da como resultado los departamentos que sí tienen profesor asociado, en vez de darme los que no, que supuestamente son 'Filología', 'Derecho', 'Biología y Geología'. La verdad es que no sé qué estoy haciendo mal.

Answer 1

EDIT

Tras revisar un poco la consulta de @CRIS19N, es cierto que sólo era necesario un LEFT JOIN en tu consulta para obtener tu resultado esperado.

Vamos a dividirla por partes (de nuevo).

SELECT D.NOMBRE FROM DEPARTAMENTO D LEFT JOIN PROFESOR PRO ON PRO.ID_DEPARTAMENTO = D.ID WHERE ID_PROFESOR IS NULL;

SELECT D.NOMBRE FROM DEPARTAMENTO D -> Te devolverá los nombres de los departamentos.

LEFT JOIN PROFESOR PRO ON PRO.ID_DEPARTAMENTO = D.ID -> Te devuelve una lista tabla donde se relaciona cada departamento, con su profesor, rellenando con NULL las entradas que no tenga relación alguna.

Y, como a ti lo que te interesa son los departamentos sin profesor, filtramos finalmente donde WHERE ID_PROFESOR IS NULL. Devolviendo los 3 departamentos sin columnas.

FIN EDIT

Debido a tu modelo, llevo un buen rato buscando sacar una query eficiente y completa, pero nunca avanzo más de esto. Odio los subselects (un select dentro de otro), porque implica menor eficiencia y me hace pensar que la persona que ha hecho esa query no se ha quebrado mucha la cabeza. Pero, tras casi 1h, no he podido avanzar más de esto.

SELECT D.NOMBRE FROM DEPARTAMENTO D WHERE D.NOMBRE NOT IN (SELECT DISTINCT(D.NOMBRE) FROM DEPARTAMENTO D JOIN PROFESOR PF ON PF.ID_DEPARTAMENTO = D.ID);

Una vez expuesta la query, vamos a dividirla en dos partes, la que está antes del NOT IN y la que está dentro del paréntesis. Vamos a empezar con la de dentro del paréntesis.

SELECT DISTINCT(D.NOMBRE) FROM DEPARTAMENTO D JOIN PROFESOR PF ON PF.ID_DEPARTAMENTO = D.ID

Query sencilla que devuelve los nombres de los departamentos que tienen un profesor (eliminando la referencia de devolver varias veces el mismo nombre con el DISTINCT(D.NOMBRE)). Una vez comprendido esto, podemos comprender la query por completa.

SELECT D.NOMBRE FROM DEPARTAMENTO D WHERE D.NOMBRE NOT IN (...)

Esta query devuelve los departamentos cuyo nombre no se encuentra dentro de la lista del paréntesis (que sería la parte previa), devolviendo las consultas que no hagan match con el paréntesis, que en tu solución serian Filología, Derecho y ByG. Si consiguiera una mejor solución, editaré la respuesta.