La Página de DriverOp

Obtener los registros de una tabla que NO están en la otra usando SQL.

En este artículo resolveré el problema que consiste en dada una tabla que tiene una relación de uno a muchos[1] con otra tabla, obtener los registros que no tienen registros relacionados en la otra tabla.

El escenario del problema puede ejemplificarse así. Se tiene una tabla "noticias" donde cada registro es una noticia (de un blog, de un diario, etc.), el sistema permite que por cada noticia se le puedan hacer comentarios que se almacenan en otra tabla llamada "comentarios". Esto establece una relación "suave" de uno a muchos entre "noticias" y "comentarios". Es decir, a cada noticia le puede corresponder uno o muchos comentarios, o también, en el caso que nos ocupa, ninguno.

Otros ejemplos prácticos pueden ser: Se tiene una tabla de usuarios y cada usuario puede dejar comentarios en un foro o no tener comentarios. Se tiene una tabla de clientes relacionada con una tabla de pedidos, a cada cliente le corresponde uno o más pedidos, pero también puede que un cliente no tenga pedidos. Se tiene una tabla de vendedores y se quiere saber cuántas ventas ha hecho cada vendedor, pero también qué vendedores no ha tenido ventas.

Todos estos ejemplos son relaciones de uno a muchos "suaves", esto significa que no se viola la consistencia de la base de datos porque no haya registros en la parte "muchos" de la relación. En otras situaciones la consistencia sí se violaría, por ejemplo si te tiene una tabla con facturas de ventas, a cada factura le debe corresponder al menos un detalle (donde se especifica el o los artículos vendidos, precios, etc.) no pudiendo existir una factura sin detalles. En este caso estamos hablando de una relación uno a muchos "dura".

Si ese fuera el caso las reglas de normalización de bases de datos nos indican que la tabla maestra ("noticias" en el ejemplo inicial) debe tener un identificador unívoco, es decir, un dato que identifica sin ambigüedad a ese registro y solo a ese registro. Ese dato se almacenará en un campo que se llama campo de la clave primaria, o índice de la tabla. Mientras que en la tabla detalle ("comentarios" en el ejemplo inicial) su clave será la unión de un identificador más el valor de la clave primaria en la tabla maestra. Los pares así formados deben cumplir la unicidad, es decir, que cada par debe ser único en toda la tabla. Ambos valores pueden repetirse por separado pero nunca juntos.

El diseño de las tablas se vería así:

Noticias
id*titulo
1Lorem Ipsum
2Dolor sit amet
Comentarios [2]
id*id_noticias*texto
11Vivamus vel purus non nibh
21Curabitur vestibulum tellus eu ipsum imperdiet pha...
31Suspendisse nulla est, interdum eget ornare porta,...
42Praesent aliquam ultrices dolor at varius
52Nulla imperdiet ante vel diam aliquam id ultrices ...

Los * indican que esos campos forman la clave primaria de las tablas.

Como se puede ver hasta aquí, la relación uno a muchos es "dura". Si quisiéramos obtener todos los comentarios de una noticia, usando SQL es tan simple como hacer[3]:

SELECT * FROM `noticias`, `comentarios`
WHERE `noticias`.`id` = 1 AND `comentarios`.`id_noticias` = `noticias`.`id`;

En la cláusula WHERE se establece que se debe seleccionar el registro en la tabla "noticias" cuyo campo "id" es igual a 1 y además debe cumplir que en la tabla "comentarios" la parte foránea de la clave debe ser igual a la clave primara de la tabla "noticias". Es ese segundo operando del operador AND el que se encarga de respetar la relación uno a muchos entre las dos tablas.

El resultado es éste:

idtituloidid_noticiastexto
1Lorem Ipsum11Vivamus vel purus non nibh
1Lorem Ipsum21Curabitur vestibulum tellus eu ipsum imperdiet pha...
1Lorem Ipsum31Suspendisse nulla est, interdum eget ornare porta,...

Como se ve, se recuperan todos los comentarios que corresponden a la noticia "Lorem Ipsum".

Ahora bien. Supongamos que queremos hacer una especie de ranquin de las noticias más comentadas. Lo que se quiere obtener es un listado con los títulos de las noticias y la suma de los comentarios por cada noticia.

A simple vista se ve que la noticia 1 tiene tres comentarios y la noticia 2 tiene dos comentarios. La consulta SQL para lograr esto es esta:

SELECT `noticias`.`titulo`, COUNT(`comentarios`.`id_noticias`) AS `cuenta`
FROM `noticias`, `comentarios`
WHERE `noticias`.`id` = `comentarios`.`id_noticias`
GROUP BY `comentarios`.`id_noticias`;

Aquí hago uso de la función COUNT() que, como su nombre sugiere, cuenta registros. Como parámetro se le indica qué campo debe usar para contar, además al resultado le asignamos un alias, en este caso "cuenta". En la cláusula WHERE se indica la relación existente entre las dos tabla. Y finalmente la cláusula GROUP BY agrupa los registros de la tabla "comentarios" según el valor del campo "id_noticias"; los que son iguales los agrupa formando un solo registro. Esto nos devolverá tantos registros como grupos se puedan formar con los valores del campo "id_noticias" de la tabla "comentarios" (en nuestro caso 2). Si a esos grupos le aplicamos COUNT() para que cuente cuántas veces aparece ese mismo caso, obtendremos el número de comentarios por noticia. Que es lo que queríamos obtener.

titulocuenta
Lorem Ipsum3
Dolor sit amet2

Hasta aquí todo bien. El problema surge cuando a la tabla "noticias" se le agrega un nuevo registro

Noticias
id*titulo
1Lorem Ipsum
2Dolor sit amet
3Suspendisse urna sapien, condimentum

Si se ejecuta la sentencia SQL que cuenta los comentarios sigue devolviendo los mismos resultados, pero queremos que además obtener todas las noticias que no tienen comentarios, es decir, "cuenta" tendría que valer cero. La sentencia SQL antes indicada no puede hacer eso debido a la relación que se establece en la cláusula WHERE. Como el registros 3 de la tabla "noticias" no tiene detalles en la tabla "comentarios", WHERE hace que se ignore el registro 3.

Para solucionar este problema SQL cuenta con mecanismos apropiados. Y para entender cómo funcionan esos mecanismos primero debemos entender cómo trabaja SQL con los datos que maneja. En SQL los registros son elementos que forman conjuntos, los conjuntos predefinidos son las tablas, pero a partir de ellos se pueden crear otros conjuntos, que se llaman conjuntos de resultados. Caja elemento, es decir, registros, tienen propiedades que son los campos de cada registro. Si nos vamos a la primera sentencia SQL podemos observar que en la cláusula WHERE se le está diciendo al motor de base de datos que use una propiedad en común entre dos conjuntos para formar un nuevo conjunto en la parte que dice "`comentarios`.`id_noticias` = `noticias`.`id`".

En la teoría de conjuntos esto no es más que hacer una intersección, como se muestra en el siguiente gráfico:

La zona celeste es un subconjunto de aquellos registros de la tabla "comentarios" cuyo campo "id_noticias" es igual al campo "id" de la tabla "noticias" (por supuesto, el gráfico es engañoso puesto que no puede haber comentarios que no correspondan a ninguna noticia).

En SQL se usa la cláusula JOIN para indicar cuál es la intersección entre dos conjuntos de datos, o dicho de otro modo, cuál es la propiedad de los elementos que se busca para hacer la intersección de los conjuntos.

De esta forma, la primera sentencia SQL se puede reescribir usando JOIN así:

SELECT * FROM `noticias`
JOIN `comentarios` AS `c` ON `c`.`id_noticias` = `noticias`.`id`
WHERE `noticias`.`id` = 1

Esto viene a decirle al motor de base de datos que produzca la intersección del conjunto "noticias" y el conjunto "comentarios" en las propiedades "id_noticias" de comentarios y "id" de "noticias". Se usa un alias "c"[4] para "comentarios" porque esta tabla no es parte del resultado del SELECT, sino que es el nombre de la intersección producida. Finalmente de ese conjunto de resultado, se seleccionan solo aquellos registros de "noticias" cuyo "id" es igual a 1 (cláusula WHERE, que es opcional).

Luego de haber comprendido esto y observando el gráfico en relación a nuestro problema podemos ver que para solucionarlo, no solo necesitamos la intersección de los conjuntos, sino que además el resto del conjunto "noticias", en teoría de conjuntos se diría que necesitamos la intersección entre el conjunto "noticias" y el conjunto "comentarios" más la diferencia del conjunto "noticias"[5] como se muestra en este gráfico:

La cláusula JOIN nos puede dar esto. Si vemos la última sentencia SQL vista hasta ahora, podemos apreciar que los conjuntos de datos están en el mismo orden que en los gráficos, "noticias" a la izquierda de JOIN y "comentarios" a la derecha. Simplemente agregando LEFT JOIN obtendremos lo que buscamos:

SELECT * FROM `noticias`
LEFT JOIN `comentarios` AS `c` ON `c`.`id_noticias` = `noticias`.`id`

He removido la cláusula WHERE porque obviamente queremos todas las noticias, no solo la 1. El resultado es:

idtituloidid_noticiastexto
1Lorem Ipsum11Vivamus vel purus non nibh
1Lorem Ipsum21Curabitur vestibulum tellus eu ipsum imperdiet pha...
1Lorem Ipsum31Suspendisse nulla est, interdum eget ornare porta,...
2Dolor sit amet42Praesent aliquam ultrices dolor at varius
2Dolor sit amet52Nulla imperdiet ante vel diam aliquam id ultrices ...
3Suspendisse urna sapien, condimentumNULLNULLNULL

Como se puede ver, tenemos el último registro de resultado que es la noticia 3 que no tiene comentarios. Es la diferencia de la que estábamos hablando.

Aquí es importante entender que JOIN devuelve una tabla (conjunto de resultados), por eso está a continuación de la cláusula FROM de la sentencia SQL y debe estar antes de WHERE. Y ese conjunto de resultados como no es ninguna tabla "física" se le debe dar siempre un nombre, que es lo que hace "AS `c`" en la cláusula JOIN.

Ya habíamos visto cómo contar registros agrupados. Sabiendo lo que sabemos ahora, podemos construir la sentencia que nos dará el resultado que nos interesa.

Sabemos que JOIN nos devuelve la intersercción entre "noticias" y "comentarios" más la diferencia de "noticias" y que en ese conjunto de resultados está el campo "id_noticias" de la tabla "comentarios" que, como ya he dicho, ese conjunto se llama simplemente "c" por lo que bastaría con hacer un COUNT() de ese campo para obtener la cuenta:

SELECT *, COUNT(`c`.`id_noticias`) AS `cuenta` FROM `noticias`
LEFT JOIN `comentarios` AS `c` ON `c`.`id_noticias` = `noticias`.`id`

Pero como COUNT() fabrica un solo registro, puesto que solamente cuenta los registros donde aparece el campo en cuestión, el resultado no es el esperado. Sin embargo habíamos visto que agrupando los registros con GROUP BY, COUNT() contará los registros agrupados:

SELECT *, COUNT(`c`.`id_noticias`) AS `cuenta` FROM `noticias`
LEFT JOIN `comentarios` AS `c` ON `c`.`id_noticias` = `noticias`.`id`
GROUP BY `c`.`id_noticias`

Notar que la agrupación se hace sobre la tabla de resultado que devuelve LEFT JOIN, no sobre la tabla "comentarios" puesto que ésta no participa directamente en la sentencia sino que lo hace indirectamente en LEFT JOIN a través de un alias "c". El resultado de esta sentencia es:

idtituloidid_noticiastextocuenta
3Suspendisse urna sapien, condimentumNULLNULLNULL0
1Lorem Ipsum11Vivamus vel purus non nibh3
2Dolor sit amet42Praesent aliquam ultrices dolor at varius2

Que es casi lo que queríamos, solo dos detalles: No necesitamos tantos campos, basta con el título de la noticia y la cuenta de cuántos comentarios tiene. Y además el orden es incorrecto, debería aparecer la noticia que más comentarios tiene al principio. Entonces, la sentencia final que resuelve el problema planteado aquí es esta:

SELECT `noticias`.`titulo`, COUNT(`c`.`id_noticias`) AS `cuenta` FROM `noticias`
LEFT JOIN `comentarios` AS `c` ON `c`.`id_noticias` = `noticias`.`id`
GROUP BY `c`.`id_noticias`
ORDER BY `cuenta` DESC

Que devuelve este resultado:

titulocuenta
Lorem Ipsum3
Dolor sit amet2
Suspendisse urna sapien, condimentum0

 

Puedes descargar las tablas usadas de ejemplo y todas las sentencias SQL mencionadas en este artículo por separado aquí:

Notas:

[1] También se suele llamar a esta relación "de maestro/detalle" como se explica más adelante.

[2] Estrictamente, el tercer registro de esta tabla debería tener la clave "1-2" y no "3-2" pero como en campo "id" el valor se establece usando la función autonumérica de los motores de base de datos, aún así se mantiene la unicidad de la clave.

[3] Para los puristas, soy perfectamente consciente que no se debe usar "*" en la cláusula SELECT sino que se deben listar los campos que realmente se van a usar para no recargar el motor de base de datos ni la transmisión del resultado, pero este es un texto didáctico, permítaseme la licencia.

[4] La elección del alias es arbitraria siempre que no coincida con un nombre de tabla que exista en la base de datos.

[5] Aquí "diferencia" se puede entender como los elementos del conjunto "noticias" que faltan en el conjunto "comentarios".

Por Diego Romero,