Consultas Multitabla Base de datos ing. de sistemas

Consultas Multita

1. Consultas Multitabla Base de Datos I

2. Introducción En este tema veremos cómo obtener datos de diferentes tablas. Ampliaremos la cláusula FROM y descubriremos nuevas palabras reservadas (UNION, EXCEPT, INTERSECT, CROSS JOIN, INNER JOIN) que corresponden a operaciones relacionales. Para obtener datos de varias tablas tenemos que combinar estas tablas mediante alguna operación basada en el álgebra relacional. El álgebra relacional define una serie de operaciones cuyos operandos son tablas y cuyo resultado es también una tabla.

3. Introducción En todo el tema cuando hablemos de tablas nos referiremos tanto a las tablas que físicamente están almacenadas en la base de datos como a las tablas temporales y a las resultantes de una consulta o vista.

4. La unión de tablas UNION La unión de tablas consiste en coger dos tablas y obtener una tabla con las filas de las dos tablas, en el resultado aparecerán las filas de una tabla y, a continuación, las filas de la otra tabla. Para poder realizar la operación, las dos tablas tienen que tener el mismo esquema (mismo número de columnas y tipos compatibles) y la tabla resultante hereda los encabezados de la primera tabla. La sintaxis es la siguiente: {< consulta >|(< consulta >)} UNION [ALL] {< consulta >|(< consulta >)} [{UNION [ALL] {< consulta >|(< consulta >)}}[ ...n ] ] [ORDER BY { expression_columna|posicion_columna [ASC|DESC]} [ ,...n ]]

5. La unión de tablas UNION Ejemplo: Suponemos que tenemos una tabla Valencia con las nuevas oficinas de Valencia y otra tabla Madrid con las nuevas oficinas de Madrid y queremos obtener una tabla con las nuevas oficinas de las dos ciudades: SELECT oficina as OFI, ciudad FROM Valencia UNION ALL SELECT oficina, ciudad FROM Madrid; El resultado sería: OFI ciudad 11 Valencia 28 Valencia 23 Madrid El resultado coge los nombres de columna de la primera consulta y aparecen primero las filas de la primera consulta y después las de la segunda.

6. La unión de tablas UNION Otro ejemplo: Obtener todos los productos cuyo precio exceda de 20 € o que se haya vendido más de 300 euros del producto en algún pedido. SELECT idfab , idproducto FROM productos WHERE precio > 20 UNION SELECT fab , producto FROM pedidos WHERE importe > 300;

7. La diferencia EXCEPT Aparecen en la tabla resultante las filas de la primera consulta que no aparecen en la segunda. Las condiciones son las mismas que las de la unión. {<consulta>|(<consulta>)} EXCEPT {<consulta>|(<consulta>)} [{EXCEPT {<consulta>|(<consulta>)}}[ ...n ] ] [ORDER BY { expression_columna|posicion_columna [ASC|DESC]} [ ,...n ]]

8. La diferencia EXCEPT Por ejemplo tenemos las tablas T1 y T2. SELECT cod FROM T1 EXCEPT SELECT codigo FROM T2; Devuelve: Cod 1 6 T2 Codigo 2 3 4 5 T1 Cod 1 2 4 5 6

9. La diferencia EXCEPT Ejemplo: Listar los productos que no aparezcan en ningún pedido. SELECT idfab , idproducto FROM productos EXCEPT SELECT DISTINCT fab , producto FROM pedidos;

10. La intersección INTERSECT Tiene una sintaxis parecida a las anteriores pero en el resultado de la intersección aparecen las filas que están simultáneamente en las dos consultas. Las condiciones son las mismas que las de la unión. { <consulta>|(<consulta>)} INTERSECT {< especificacion_consulta >|(< especificacion_consulta >)} [{INTERSECT {<consulta>|(<consulta>)}} [ ...n ] ] [ORDER BY { expression_columna|posicion_columna [ASC|DESC]} [ ,...n ]]

11. La intersección INTERSECT Retomando el ejemplo anterior: SELECT cod FROM T1 INTERSECT SELECT cod FROM T2; Devuelve : Cod 2 4 5

12. La intersección INTERSECT Ejemplo: Obtener todos los productos que valen más de 20 euros y que además se haya vendido en un pedido más de 300 euros de ese producto. SELECT idfab , idproducto FROM productos WHERE precio > 20 INTERSECT SELECT fab , producto FROM pedidos WHERE importe > 300;

13. La composición de tablas Hasta ahora hemos operado con tablas que tenían el mismo esquema, pero muchas veces lo que necesitamos es obtener una tabla que tenga en una misma fila datos de varias tablas, por ejemplo, obtener las facturas y que en la misma fila de factura aparezca el nombre y dirección del cliente. La composición de tablas consiste en obtener a partir de dos tablas cualesquiera una nueva tabla fusionando las filas de una con las filas de la otra, concatenando los esquemas de ambas tablas. Consiste en formar parejas de filas. La sentencia SELECT permite realizar esta composición, incluyendo dos o más tablas en la cláusula FROM.

14. El producto cartesiano CROSS JOIN El producto cartesiano obtiene todas las posibles concatenaciones de filas de la primera tabla con filas de la segunda tabla. Se indica escribiendo en la cláusula FROM los nombres de las tablas separados por una coma o utilizando el operador CROSS JOIN. FROM {< tabla_origen >} [ ,...n ] |< tabla_origen > CROSS JOIN < tabla_origen >

15. El producto cartesiano CROSS JOIN Ejemplo : SELECT * FROM empleados CROSS JOIN oficinas ; Si ejecutamos esta consulta veremos que las filas del resultado están formadas por las columnas de empleados y las columnas de oficinas. En las filas aparece cada empleado combinado con la primera oficina, luego los mismos empleados combinados con la segunda oficina y así hasta combinar todos los empleados con todas las oficinas. Si ejecutamos : SELECT * FROM empleados , oficinas ; Obtenemos lo mismo .

16. El producto cartesiano CROSS JOIN Este tipo de operación no es la que se utiliza más a menudo, lo más frecuente sería combinar cada empleado con los datos de SU oficina. Lo podríamos obtener añadiendo a la consulta un WHERE para filtrar los registros correctos: SELECT * FROM empleados, oficinas WHERE empleados.oficina = oficinas.oficina ; Aquí nos ha aparecido la necesidad de cualificar los campos ya que el nombre oficina es un campo de empleados y de oficinas por lo que si no lo cualificamos, el sistema nos da error. Hemos utilizado en la lista de selección *, esto nos recupera todas las columnas de las dos tablas.

17. El producto cartesiano CROSS JOIN SELECT empleados.*,ciudad, region FROM empleados, oficinas WHERE empleados.oficina = oficinas.oficina ; Recupera todas las columnas de empleados y las columnas ciudad y región de oficinas. También podemos combinar una tabla consigo misma, pero en este caso hay que definir un alias de tabla, en al menos una, sino el sistema da error ya que no puede nombrar los campos. SELECT * FROM oficinas, oficinas as ofi2;

18. Reunión Interna: INNER JOIN / ON Si antes se dijo que el SGBD realiza el producto cartesiano entre dos tablas y posteriormente mediante la cláusula WHERE ignora aquellos registros que carecen de sentido y muestra los que guardan una relación, ahora podemos verlo del siguiente modo: el SGBD recorrerá la tabla hijo(CURSO) y para cada uno asociará el registro de la tabla padre(PROFESOR) que satisface la cláusula ON. Para asociar el profesor no es necesario realizar, para cada curso, un recorrido secuencial sobre la tabla PROFESOR hasta encontrarlo, puesto que en la cláusula ON estamos indicando su clave primaria, por lo que el motor SQL usará el índice que la clave lleva implícito para localizar un profesor de forma mucho más eficiente. Igual que haría usted para localizar un capítulo concreto de un libro, usando el índice.

19. Reunión Interna: INNER JOIN / ON Veamos otro ejemplo de la lección anterior usando esta cláusula, concretamente del apartado de ejercicios, donde se pedía los cursos en que se ha matriculado el alumno con identificador 1: select  C.TITULO CURSO from  ALUMNO_CURSO AC  inner   join  CURSO C on  AC.IDCURSO = C.IDCURSO where  AC.IDALUMNO = 1 Observe como en la cláusula WHERE se establece un filtro propiamente dicho, y en la cláusula ON se establece la condición de reunión que el motor debe aplicar entre las tablas a ambos lados de la cláusula INNER JOIN. CURSO Programación PHP SQL desde cero

20. Reunión Interna: INNER JOIN / ON Veamos un último ejemplo de reunión interna en la que aparezcan tres tablas, para ello tomemos otro ejemplo de la lección anterior, la reunión de los alumnos con los cursos que cursa cada uno. Tomando ejemplos equivalentes construidos únicamente con la cláusula WHERE se pueden observar mejor las diferencias. select  C.TITULO CURSO ,  concat (A.APELLIDOS,', ', A.NOMBRE ) ALUMNO from  ALUMNO_CURSO AC  inner   join  ALUMNO A on  AC.IDALUMNO = A.IDALUMNO  inner   join   CURSO C on  AC.IDCURSO  = C.IDCURSO order   by  C.TITULO , A.NOMBRE , A.APELLIDOS Si ahora sobre este consulta se quisiera reducir el resultado a un curso o un alumno en concreto, se añadiría la pertinente cláusula WHERE con el filtro deseado justo antes de la cláusula ORDER BY.  CURSO ALUMNO Dibujo técnico Dilma Perna, Carmen Dibujo técnico Santo Lote, Jeremias Modelos abstracto de datos Fuego García, Marta Programación PHP Hernandaz Mata, Pablo Programación PHP Ot Dirmet, Sergio Programación PHP Lomas Trillo, Teresa SQL desde cero Hernandaz Mata, Pablo SQL desde cero Ot Dirmet , Sergio

21. Resumen A efectos lógicos el producto cartesiano entre varias tablas se puede considerar como una nueva tabla siendo válido todo lo expuesto sobre consultas de una sola tabla, es decir, se considera que el motor SQL primero generará el producto cartesiano sobre las tablas de la cláusula FROM, para después ejecutar el resto de la consulta sobre este resultado. En la clausula WHERE se pueden establecer condiciones comparando campos de un mismo registro entre si. Cuando los campos son de tablas distintas se estarán reuniendo registros entre ambas tablas bajo un concepto o propósito. El caso más amplio de relación es el producto cartesiano, donde se reúnen todos con todos. La reunión interna permite reunir registros de tablas relacionadas ignorando los registros que no satisfacen la condición de reunión especificada en la cláusula WHERE o bien en la cláusula ON en el caso de usar la sintaxis INNER JOIN.

22. Resumen La reunión externa permite reunir registros de tablas relacionadas considerando todos los registros de una primera tablas aunque ninguno de los registros de una segunda tabla presente aciertos contra la primera, obviamente en ese caso los campos de esta última tabla vendrán a nulo. Existen dos sintaxis para realizar las operaciones de reunión, dependiendo del SGBD. Basada en cláusula WHERE o bien basada en cláusula INNER JOIN. Lo ideal sería dejar a criterio del desarrollador el uso de cualquiera de ellas siempre y cuando el SGBD lo soporte.

23. Ejercicios Ejercicio 1: Construya una consulta SQL que devuelva el peso medio de todos los conjuntos confeccionables entre camisas y pantalones. Modifique la consulta para obtener el mismo resultado entre camisas, pantalones y calzados. Solución 1.1: select   avg ( C.PESO_GR + P.PESO_GR) AS PESO_MEDIO_CONJUNTOS from  CAMISA C , PANTALON P Solución 1.2: select   avg ( C.PESO_GR + P.PESO_GR + Z.PESO_GR) AS PESO_MEDIO_CONJUNTOS from  CAMISA C , PANTALON P , CALZADO Z PESO_MEDIO_CONJUNTOS 853.3333 PESO_MEDIO_CONJUNTOS 1695.0000

24. Ejercicios Ejercicio 2: Construya una consulta SQL que devuelva el peso medio de todos los conjuntos confeccionables entre camisas y pantalones agrupado por camisa. Modifique la consulta de manera que devuelva el mismo resultado pero de los grupos que el peso medio es superior a 850 gramos. Solución 2.1: select  C.NOMBRE ,  avg ( C.PESO_GR + P.PESO_GR) AS PESO_MEDIO_CONJUNTOS from  CAMISA C , PANTALON P group   by  C.NOMBRE Solución 2.2: select  C.NOMBRE ,  avg ( C.PESO_GR + P.PESO_GR) AS PESO_MEDIO_CONJUNTOS from  CAMISA C , PANTALON P group   by  C.NOMBRE having   avg ( C.PESO_GR + P.PESO_GR) > 850 NOMBRE PESO_MEDIO_CONJUNTOS algodon naranja 890.0000 lino blanca 810.0000 seda negra 860.0000 NOMBRE PESO_MEDIO_CONJUNTOS algodon naranja 890.0000 seda negra 860.0000

25. Ejercicios Ejercicio 3: Construya una consulta que devuelva los cursos en que se ha matriculado el alumno con identificador 1. Modifique la anterior consulta para que devuelva los nombres y apellidos de los alumnos, y los cursos en que se han matriculado, tales que el nombre de pila del alumno contenga un E. Solución 3.1: select  A.NOMBRE, A.APELLIDOS, C.TITULO CURSOS from  ALUMNO_CURSO AC  inner   join  CURSO C on  AC.IDCURSO = C.IDCURSO inner join ALUMNO A on  AC.IDALUMNO = A.IDALUMNO Where A.IDALUMNO=1 Solución 3.2: select  A.NOMBRE, A.APELLIDOS, C.TITULO CURSOS from  ALUMNO_CURSO AC  inner   join  CURSO C on  AC.IDCURSO = C.IDCURSO inner join ALUMNO A on  AC.IDALUMNO = A.IDALUMNO Where A.NOMBRE like  '%E%'  NOMBRE APELLIDOS CURSOS Pablo Hernandaz Mata Programación PHP Pablo Hernandaz Mata SQL desde cero

26. FIN Gracias!