Consultas en más de una tabla I – Consultas Anidadas

Hasta ahora hemos consultado datos que se encuentran almacenados en una sola tabla.
También sabemos que es posible encontrar datos relacionados almacenados en distintas tablas.

1. ¿Qué podemos hacer cuando queremos obtener datos relacionados a un registro que están almacenados en otra tabla?
   Ej. Si conozco el nombre o correo de un usuario ¿cómo puedo obtener las publicaciones que ha escrito?
   En este contexto nos interesa obtener solo los datos que se encuentran almacenados en una tabla – publicaciones – sin necesidad de presentar los datos relacionados.

2. ¿Cómo podemos obtener y presentar datos que están almacenados en distintas tablas?
   Ej. Quiero mostrar el nombre del usuario junto con las publicaciones que ha escrito.
   En este contexto nos interesa obtener los datos que están en distintas tablas.

Las opciones que podemos utilizar son:
Consultas anidadas – subconsultas – o JOIN.

Antes de comenzar con las consultas anidadas muestro el esquema de la base de datos -tutoriales -. La puedes descargar aquí.

Referencia para importar la Base de Datos con PHPMyAdmin – http://www.youtube.com/watch?v=M6IbthUJjg0 -.

[youtube=http://www.youtube.com/watch?v=M6IbthUJjg0]

Es probable que la interfaz de PHPMyAdmin sea distinta porque ya tiene algunos años el video.

El esquema de la Base de Datos nos sirve para visualizar – tablas, campos con su tipo de dato, y relaciones entre tablas – la estructura de la Base de Datos. En este caso – que vamos a comenzar con consultas anidadas – nos sirve para saber dónde están almacenados los datos. Con esto vamos a poder definir la(s) consulta(s) que tenemos que realizar a partir de los datos que tenemos para obtener los datos que necesitemos.

Consultas Anidadas – Subconsultas

Las consultas anidadas o subconsultas son dos o más consultas donde el resultado de una consulta se utiliza como parámetro – valor – para realizar la siguiente consulta.

Las consultas anidadas se componen de dos partes.

Consulta interior o subconsulta.
Es la primer consulta que se realiza. De esta consulta obtenemos el valor o los valores necesarios para que se realice la consulta exterior.

Consulta exterior.
Esta consulta utiliza el resultado de la consulta interior como parámetro y muestra el resultado que queremos obtener.

Pueden existir cualquier número de consultas anidadas. Las consultas se realizan del interior al exterior.

Ejemplo:
Si queremos mostrar el título de las publicaciones que tienen asignado el nivel con nombre fácil haríamos el siguiente proceso:

-- Primero tenemos que obtener el id del nivel que estamos buscando

SELECT id FROM niveles WHERE nombre = "fácil";

-- "Guardamos el id" - ya sea en el lenguaje de programación, 
-- en un archivo de texto o lo memorizamos para realizar la siguiente consulta.
-- Después filtramos las publicaciones que tengan como valor asignado 
-- en la columna nivel_id el id que obtuvimos.

SELECT titulo FROM publicaciones WHERE nivel_id = 'utilizar el id guardado';

Si queremos realizar las consultas anteriores como consultas anidadas.

Revisamos en el esquema cómo están relacionadas las publicaciones con los niveles. En este caso es una relación 1:M donde un nivel tiene muchas publicaciones – y una publicación pertenece a un nivel -. En la tabla de niveles no hay referencia de las publicaciones, pero en la tabla de publicaciones hay una columna – nivel_id – que hace referencia a la columna id de la tabla niveles.

-- Con los datos que tenemos - el nombre de nivel 
-- - sabemos que necesitamos obtener el id para, 
-- después utilizarlo y filtrar las publicaciones 
-- que en la columna nivel_id tengan ese valor. 

SELECT id FROM niveles WHERE nombre = "fácil";


-- Después definimos la consulta siguiente consulta 
-- - obtener las publicaciones que en la columna nivel_id 
-- tengan el valor que se obtuvo de la consulta anterior - 
-- pero, en lugar de asignar directamente el valor, 
-- utilizamos un paréntesis. 

SELECT titulo FROM publicaciones WHERE nivel_id = ( );

-- Dentro de los paréntesis utilizamos la consulta interna, 
-- la - primer - consulta que nos va a devolver el resultado que necesita 
-- la consulta externa - la segunda consulta - para funcionar.


SELECT titulo FROM publicaciones WHERE nivel_id = (
        SELECT id FROM niveles WHERE nombre = "fácil"
);

Es posible utilizar los distintos operadores de comparación que hemos visto para filtrar los resultados obtenidos de las consultas anidadas.

¿Recuerdas AND y OR y para qué se utilizaban? Si además mostrar el título de las publicaciones que tienen asignado el nivel con nombre fácil queremos mostrar únicamente las publicaciones tengan el término html en el título, además de filtrar por el nivel id, tendríamos que agregar la condición para filtrar por título.

SELECT titulo FROM publicaciones WHERE nivel_id = (
	SELECT id FROM niveles WHERE nombre = "fácil"
) AND titulo LIKE "%html%";

Algunos aspectos a considerar cuando trabajemos con consultas anidadas:

1. Las consultas anidadas deben devolver el valor correspondiente a una columna.
2. Si el resultado arroja más de un registro utilizar el operador apropiado en la consulta exterior – IN / NOT IN en lugar de igual -.
3. Cada consulta se ejecuta de manera individual. Si se utilizan muchas consultas internas afecta el desempeño.

Si queremos obtener el título de las publicaciones que ha escrito “Eileen” (no tenemos el nombre completo) con el nivel fácil quedaría una consulta como la siguiente:

SELECT titulo FROM publicaciones WHERE nivel_id IN (
   SELECT id FROM niveles WHERE nombre LIKE "%facil%"
) AND usuario_id IN (
  SELECT id FROM usuarios WHERE nombre LIKE "%Eileen%"
);

Misma consulta anterior pero filtrando los resultados para mostrar las publicaciones que tengan en el título el término css.

SELECT titulo FROM publicaciones WHERE nivel_id IN (
   SELECT id FROM niveles WHERE nombre LIKE "%facil%"
) AND usuario_id IN (
  SELECT id FROM usuarios WHERE nombre LIKE "%Eileen%"
) AND titulo LIKE "%css%";

Finalmente ¿cómo podemos obtener el título de las publicaciones que tienen la etiqueta html?
En este caso es una relación M:M donde una etiqueta puede clasificar muchas publicaciones y una publicación puede estar clasificada por muchas etiquetas.

Si recordamos, para este tipo de relaciones se crea una tabla de unión – JOIN TABLE – donde se encuentran las llaves primarias de las tablas que está uniendo.

El proceso a realizar sería:

1. Obtener el id de la etiqueta que tiene el nombre “html”.
2. Con el id de la etiqueta tenemos que obtener las llaves primarias de las publicaciones en la tabla de unión.
3. Con las llaves primarias de las publicaciones obtener el título de las mismas.

--  Obtener el id de la etiqueta que tiene el nombre “html”.
-- Vamos a nombrar  esta consulta como "consulta_1"
SELECT id FROM etiquetas WHERE nombre LIKE "%html%";

--  Con el id de la etiqueta tenemos que obtener 
-- las llaves primarias de las publicaciones en la tabla de unión.
-- Vamos a nombrar  esta consulta como consulta_2

SELECT publicacion_id 
FROM  etiquetas_publicaciones
WHERE etiqueta_id = ( "consulta_1" );

--  Con las llaves primarias de las publicaciones obtener el título de las mismas.
-- Vamos a nombrar  esta consulta como consulta_3

SELECT titulo
FROM publicaciones
WHERE id IN ("consulta_2");

-- Preparamos la sintaxis de la consulta
-- anidada con las consultas 1 y 2

SELECT publicacion_id 
FROM  etiquetas_publicaciones
WHERE etiqueta_id = ( 
     SELECT id FROM etiquetas WHERE nombre LIKE "%html%"
);

-- Preparamos la sintaxis de la consulta
-- anidada con las consultas 2 y3

SELECT titulo
FROM publicaciones
WHERE id IN (
  SELECT publicacion_id 
  FROM  etiquetas_publicaciones
  WHERE etiqueta_id = ( 
        "consulta_1 " 
   )
);

-- Ahora si, hacemos la consulta anidada
-- con las consultas 1,2 y 3

SELECT titulo
FROM publicaciones
WHERE id IN (
  SELECT publicacion_id
  FROM  etiquetas_publicaciones
  WHERE etiqueta_id IN ( 
         SELECT id FROM etiquetas WHERE nombre LIKE "%html%"
  )
);

Aunque el desempeño se puede ver afectado por factores como el número de registros en las tablas involucradas, el tipo de relación a filtrar y los filtros adicionales a la relación y al principio pueden parecer complicadas, las consultas anidadas nos pueden ayudar en el primer caso que se planteó:

¿Qué podemos hacer cuando queremos obtener datos relacionados a un registro que están almacenados en otra tabla?

Para poderlas utilizar es conveniente:

1. Revisar los datos con los que contamos.
2. Definir los datos que nos interesa obtener.
3. Analizar la manera en cómo están relacionadas las tablas donde están los datos – de esto depende la cantidad de consultas -.
4. Ir haciendo las consultas de manera individual.
5. Una vez que estén hechas y que se obtenga el resultado, reescribir las consultas individuales en una consulta anidada.

Te preguntarás, si ya hice las consultas ¿para qué convertirlas en una consulta anidada? Para tener la estructura de la consulta y únicamente sustituyas valores cuando necesites que el término de búsqueda sea distinto.

Ej.

Si en lugar de querer el título de las publicaciones que tienen el nivel fácil quieres las que tienen el valor difícil, lo único que tienes que hacer es cambiar el término.