Consultas DML en SQL: operaciones multitabla y subqueries

Consultas DML

unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES [TEMA 5-PARTE 2 Consultas. DML] Consulta la información almacenada en una base de datos empleando asistentes, herramientas gráficas y el lenguaje de manipulación de datos

Sumario de Consultas

Sumario

• Consultas multitablas.

Composición Interna

• Composición interna
  • Composiciones cruzadas (producto cartesiano).
  • Composiciones internas (Intersección)
  • Uso del operador JOIN (SQL 2).
  • Composiciones cruzadas (producto cartesiano)
  • Composiciones internas

Composición Externa

• Composición externa
  • LEFT OUTER JOIN
  • RIGTH OUTER JOIN
  • FULL OUTER JOIN

Subconsultas

• Subconsultas
  • Test de Comparación
  • Test de pertenencia a conjunto (IN - NOT IN)
  • Test de existencia (EXITS - NOT EXITS)
  • Test cuantificados ALL y ANY

Bibliografía

• Bibliografía ¡Error! Marcador no definido.

7 7 8unendo BY afa® LA UNIVERSIDAD DE LAS PROFESIONES

Objetivos de SQL

OBJETIVOS SQL es un lenguaje estándar en la mayoría de las bases de datos relacionales con el que normalmente se crean accesos a la información que contienen, mediante la realización de diferentes tipos de operaciones, lanzando consultas y obteniendo datos de interés para el usuario, de una forma sencilla. En esta unidad conocerás los distintos tipos de consultas que puedes realizar tanto a una tabla como a varias, aprovechando las relaciones del modelo creado. Por tanto los objetivos son:

• Conocer el estándar SQL como lenguaje para manipular las tablas y sus datos.
• Utilizar las sentencias, operadores y funciones que constituyen la base de SQL.
• Trabajar con múltiples tablas y vistas.

2unendo BY afa® LA UNIVERSIDAD DE LAS PROFESIONES

Consultas Multitablas

5 .- Consultas multitablas. Recuerda que una de las propiedades de las bases de datos relacionales era que distribuíamos la información en varias tablas que a su vez estaban relacionadas por algún campo común. Así evitábamos repetir datos. Por tanto, también será frecuente que tengamos que consultar datos que se encuentren distribuidos por distintas tablas. Hasta ahora las consultas que hemos usado se referían a una sola tabla, pero también es posible hacer consultas usando varias tablas en la misma sentencia SELECT. Esto permitirá realizar distintas

Ejemplo de Tablas

TFabricante código nombre 1 Asus 2 Lenovo 3 Hewlett-Packard TProducto código nombre precio fkcodigo 1 Monitor 24 LED Full HD 202 1 2 Monitor 27 LED Full HD 245.99 1 3 Portátil Yoga 520 559 2 4 Portátil Ideapd 320 444 2 operaciones como son:

• La composición interna.
• La composición externa.

La única diferencia respecto a las consultas sencillas es que vamos a tener que especificar en la cláusula FROM cuáles son las tablas que vamos a usar y como las vamos a relacionar entre sí. Para realizar este tipo de consultas podemos usar dos alternativas, la sintaxis de SQL 1 (SQL-86), que consiste en realizar el producto cartesiano de las tablas y añadir un filtro para relacionar los datos que tienen en común, y la sintaxis de SQL 2 (SQL-92 y SQL-2003) que incluye todas las cláusulas de tipo JOIN.

Composición Interna de Datos

5.1 .- Composición interna

Composiciones Cruzadas (Producto Cartesiano)

5.1.1 .- Composiciones cruzadas (producto cartesiano) ¿Qué ocurre si combinamos dos o más tablas sin ninguna restricción? El resultado será un producto cartesiano. El producto cartesiano entre dos tablas da como resultado todas las combinaciones de todas las filas de esas dos tablas. Se indica poniendo en la cláusula FROM las tablas que queremos componer separadas por comas. La sintaxis (SQL1) general sería: SELECT * FROM tabla1, tabla2 [, tabla3 ... ] ; Ejemplo:

3unendo BY afa® LA UNIVERSIDAD DE LAS PROFESIONES

Consulta de Producto Cartesiano

La consulta que se ejecutaría sería: SELECT * FROM TFabricante, TProducto cuya salida sería: TFabricante x TProducto código nombre código nombre precio fkcodigo 1 Asus 1 Monitor 24 LED Full HD 202 1 2 Lenovo 1 Monitor 24 LED Full HD 202 1 3 Hewlett-Packard 1 Monitor 24 LED Full HD 202 1 1 Asus 2 Monitor 27 LED Full HD 245.99 1 2 Lenovo 2 Monitor 27 LED Full HD 245.99 1 3 Hewlett-Packard 2 Monitor 27 LED Full HD 245.99 1 1 Asus 3 Portátil Yoga 520 559 2 2 Lenovo 3 Portátil Yoga 520 559 2 3 Hewlett-Packard 3 Portátil Yoga 520 559 2 1 Asus 4 Portátil Ideapd 320 444 2 2 Lenovo 4 Portátil Ideapd 320 444 2 3 Hewlett-Packard 4 Portátil Ideapd 320 444 2 Si se analiza el resultado, cada producto se ha combinado con todos los fabricantes posibles, obteniendo todas las posibles combinaciones. Esta operación no es de las más utilizadas ya que coge una fila de una tabla y la asocia con todos y cada uno de las filas de la otra tabla, independientemente de que tengan relación o no. Lo más normal es que queramos seleccionar los registros según algún criterio. Necesitaremos discriminar de alguna forma para que únicamente aparezcan filas de una tabla que estén relacionadas con la otra tabla. A esto se le llama asociar tablas (JOIN).

4unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

Composiciones Internas (Intersección)

5.1.2 .- Composiciones internas (Intersección) La intersección de dos conjuntos es una operación que resulta en otro conjunto que contiene sólo los elementos comunes que existen en ambos conjuntos. Para hacer una composición interna se parte de un producto cartesiano y se eliminan aquellas filas que no cumplen la condición de composición. Lo importante en las composiciones internas es emparejar los campos que han de tener valores iguales. Las reglas para las composiciones son:

• Pueden combinarse tantas tablas como se desee.
• El criterio de combinación puede estar formado por más de una pareja de columnas.
• En la cláusula SELECT pueden citarse columnas de ambas tablas, condicionen o no, la combinación.
• Si hay columnas con el mismo nombre en las distintas tablas, deben identificarse especificando la tabla de procedencia o utilizando un alias de tabla.

Las columnas que aparecen en la cláusula WHERE se denominan columnas de emparejamiento ya que son las que permiten emparejar las filas de las dos tablas. Éstas no tienen por qué estar incluidas en la lista de selección. La sintaxis (SQL1) general simplificada a dos tablas sería: SELECT * /* alternativa: nombre de las columnas a mostrar * / FROM tabla1, tabla2 WHERE tabla1 . columna = tabla2. columna; Ejemplo: Partiendo del ejemplo anterior. ¿ Cuáles serían los registros que interesa obtener? Obvio, los campos claves de fabricante que coincidan con las claves ajenas en productos. La consulta quedaría: SELECT * FROM TFabricante, TProducto WHERE Tfabricante . pkcodigo=TProducto. fkcodigo_fabricante; TFabricante x TProducto código nombre código nombre precio fkcodigo 1 Asus 1 Monitor 24 LED Full HD 202 1 1 Asus 2 Monitor 27 LED Full HD 245.99 1

5unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

Resultados de la Intersección

2 Lenovo 3 Portátil Yoga 520 559 2 2 Lenovo 4 Portátil Ideapd 320 444 2 Otra representación mas elegante sería utilizando el siguiente código: SELECT TFabricante.cnombre as Fabricante, TProducto.cnombre as Producto, nprecio as Precio FROM TFabricante, TProducto WHERE Tfabricante.pkcodigo=TProducto.fkcodigo_fabricante; Cuyo resultado sería: Fabricante Producto Precio Asus Monitor 24 LED Full HD 202 Asus Monitor 27 LED Full HD 245.99 Lenovo Portátil Yoga 520 559 Lenovo Portátil Ideapd 320 444 Nota: Ten en cuenta que con la operación de intersección sólo obtendremos los elementos de existan en ambos conjuntos. Por lo tanto, en el ejemplo anterior puede ser que existan filas en la tabla fabricante que no aparecen en el resultado porque no tienen ningún producto asociado, al igual que podrían existir filas en la tabla producto que no aparecen en el resultado porque no tienen ningún fabricante asociado.

Uso del Operador JOIN (SQL 2)

5.1.4 .- Uso del operador JOIN (SQL 2) El operador JOIN nació en la versión de SQL de 1999 y el objetivo que se perseguía era separar las condiciones de selección de registros que se usan en la cláusula WHERE de las que se emplean para las condiciones de relación. En la siguiente sentencia se puede comprobar cuáles son las condiciones de selección de registros y cuáles las de asociación: SELECT * FROM Profesor,Asignatura, Ciclo WHERE Siglas='DAM' AND Asignatura.dni=Profesor.dni AND Asignatura.CodCF=Ciclo.CodCF; Aunque toda la expresión lógica de la cláusula WHERE es una condición de selección de registros, se puede distinguir, por una parte, la condición de asociación Asignatura.dni=Profesor.dni AND ignatura.CodCF=Ciclo.CodCF y, por otra de selección, Siglas='DAM'. Esta consulta muestra las asignaturas del ciclo con sigla DAM que imparte cada profesor.

Composiciones Cruzadas con JOIN

5.1.4.1 Composiciones cruzadas (producto cartesiano) Se utiliza: CROSS JOIN

6unendo BY afa® LA UNIVERSIDAD DE LAS PROFESIONES

Ejemplo de CROSS JOIN

Ejemplo: SELECT * FROM TFabricante CROSS JOIN TProducto; sería equivalente a: SELECT * FROM TFabricante, TProducto; Esta consulta nos devolvería el producto cartesiano de las dos tablas en ambos casos.

Composiciones Internas con JOIN

5.1.4.2 Composiciones internas Se utiliza: INNER JOIN o JOIN NATURAL JOIN Sintaxis general composición interna con JOIN: SELECT columna [, columna. . . ] FROM tabla1 { [INNER] JOIN | NATURAL JOIN} tabla2 ON tabla1 . columna = tabla2. columna La palabra reservada INNER es opcional. NOTA: Ten en cuenta que si olvidas incluir la cláusula ON obtendrás el producto cartesiano de las dos tablas. Ejemplo SELECT * FROM Tfabricante INNER JOIN TProducto ON Tfabricante. pkcodigo=TProducto. fkcodigo_fabricante; Seria equivalente a: SELECT * FROM TFabricante, TProducto WHERE Tfabricante . pkcodigo=TProducto. fkcodigo_fabricante; Utilizando NATURAL JOIN: SELECT * FROM Tfabricante INNER JOIN TProducto Esta consulta nos devolvería la intersección de las dos tablas, pero solo en el caso de que las columnas que utilizamos para relacionarlas tuviesen el mismo nombre. Sólo deberíamos utilizar una composición de tipo NATURAL JOIN cuando estemos seguros que los nombres de las columnas sobre las que quiero relacionar las dos tablas se llaman igual en las dos tablas. Lo normal es que no suelan tener el mismo nombre y que debamos usar una composición de tipo INNER JOIN. (como es nuestro ejemplo)

Composición Externa

5.2 .- Composición externa A veces es necesario seleccionar algunas filas de una tabla aunque éstas no tengan correspondencia con las filas de la otra tabla.

Tipos de OUTER JOIN

5.2.1 .- LEFT OUTER JOIN 5.2.2 .- RIGTH OUTER JOIN 5.2.3 .- FULL OUTER JOIN

7