BBDD_2024_Unidad05_Pres3_082921ad524f927e00c17cd9f06834e8.pdf

Base de Datos 1 Unidad 5 SQL Combinación de varias Tablas JOINS Introducción Uso de alias en los nombres de las tablas Combinación de datos de varias tablas Combinación de varios conjuntos de resultados JOINS CROSS INNER OUTER Uso de combinaciones cruzadas...

Base de Datos 1 Unidad 5 SQL Combinación de varias Tablas JOINS Introducción Uso de alias en los nombres de las tablas Combinación de datos de varias tablas Combinación de varios conjuntos de resultados JOINS CROSS INNER OUTER Uso de combinaciones cruzadas  Los joins permiten obtener información de múltiples tablas, para poder responder a preguntas como: ¿Qué productos son suministrados por qué proveedores? ¿Qué clientes realizaron qué órdenes? ¿Qué clientes están comprando qué productos? JOINS SINTAXIS SELECT tabla1.columna1, tabla2.columna2 FROM tabla1 JOIN tabla2 ON (tabla1.columna1=tabla2.columna1) WHERE condiciones Introducción a las combinaciones  Realizar un reporte para obtener el EmployeeID y el OrderID de la tabla Ordenes no es complicado: SELECT EmployeeID, OrderID FROM Orders; Pero para que pueda brindar información más útil, podemos hacer lo siguiente: -- Crear un reporte que muestre las ordenes de un empleado. SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate FROM Employees JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID) ORDER BY Orders.OrderDate;  Los joins multi-tabla pueden obtener datos muy complejos y pueden demandar un tiempo largo de procesamiento, pero la sintaxis es relativamente simple. SINTAXIS SELECT tabla1.columna, tabla2.columna, Joins Multi- table3.columna FROM tabla1 tabla JOIN tabla2 ON (tabla1.columna=tabla2.columna) JOIN tabla3 ON (tabla2.columna=tabla3.columna) WHERE condiciones  SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID) JOIN Customers c ON (c.CustomerID = o.CustomerID) WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '19980101' ORDER BY c.CompanyName;  Hasta ahora, los JOIN que hemos trabajado fueron INNER JOIN (Joins Internos). Esto quiere decir que las filas devueltas tienen datos relacionados en ambas tablas. Por ejemplo, cuando hicimos el Outer Joins INNER JOIN entre la tabla Empleados y la tabla Ordenes, en el resultado solo son incluidos los empleados que realizaron órdenes, y las órdenes que tienen empleados relacionados. Otro ejemplo Inner SELECT COUNT(DISTINCT e.EmployeeID) AS numEmpleados, COUNT(DISTINCT c.CustomerID) AS numEmpresas, e.City, c.City FROM Employees e JOIN Customers c ON (e.City = c.City) GROUP BY e.City, c.City ORDER BY numEmpleados DESC; numEmpleados numEmpresas City City ------------ ----------- --------------- --------------- 4 6 London London 2 1 Seattle Seattle 1 1 Kirkland Kirkland Uso de combinaciones externas (LEFT/RIGHT)  Un LEFT JOIN (también llamado LEFT OUTER JOIN) devuelve todos los registros de la primera tabla, incluso si no tienen valores relacionados en la segunda tabla. SINTAXIS SELECT tabla1.columna, tabla2.columna FROM tabla1 Left Join LEFT [OUTER] JOIN tabla2 ON (tabla1.columna=tabla2.columna) WHERE condiciones Todas las filas de la tabla1 serán incluidas en el resultado, aunque no tengan valores relacionados en la tabla2. Ejemplo Left 2 1 Seattle Seattle SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees, 1 0 Tacoma NULL 1 0 Redmond NULL COUNT(DISTINCT c.CustomerID) AS numCompanies, 1 1 Kirkland Kirkland e.City, c.City Warning: Null value is eliminated by an aggregate or other SET operation. FROM Employees e LEFT JOIN Customers c ON Todos los registros de la tabla (e.City = c.City) Empleados son contabilizados, GROUP BY e.City, c.City aunque no haya registros con la ORDER BY numEmployees DESC; misma ciudad en la tabla Clientes. Right Join Un RIGHT JOIN (también llamado RIGHT OUTER JOIN) devuelve todos los registros de la segunda tabla, aunque no tenga registros relacionados en la primera tabla. SINTAXIS SELECT tabla1.columna, tabla2.columna FROM tabla1 RIGHT [OUTER] JOIN tabla2 ON (tabla1.columna=tabla2.columna) WHERE condiciones Todas las filas de la tabla2 serán devueltas, aunque no tengan valores relacionados en la tabla1. Ejemplo Right 2 1 Seattle Seattle 1 1 Kirkland Kirkland SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees, 0 1 NULL Kobenhavn 0 1 NULL Köln COUNT(DISTINCT c.CustomerID) 0 1 NULL Lander AS numCompanies, … e.City, c.City 0 1 NULL Graz 0 1 NULL Helsinki FROM Employees e RIGHT JOIN Customers c ON 0 1 NULL Warning: Null value is eliminated by an aggregate or I. de Margarita (e.City = c.City) other SET operation. Todos los registros de la tabla Clientes son contabilizados incluso cuando GROUP BY e.City, c.City no tengan ciudades relacionadas en la tabla Empleados. ORDER BY numEmployees DESC; Full Outer Joins Un FULL JOIN (también llamado FULL OUTER JOIN) devuelve todos los registros de cada tabla, aun cuando no tengan registros relacionados en la otra tabla. SINTAXIS SELECT tabla1.columna, tabla2.columna FROM tabla1 FULL [OUTER] JOIN tabla2 ON (tabla1.columna=tabla2.columna) WHERE condiciones Todas las filas de la tabla1 y la tabla2 serán devueltos. Full Outer Joins En MySQL hacemos LEFT UNION RIGHT SINTAXIS SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id Ejemplo Full 4 6 London London SELECT COUNT(DISTINCT 2 1 Seattle Seattle e.EmployeeID) AS numEmployees, 1 0 Tacoma NULL 1 0 Redmond NULL COUNT(DISTINCT c.CustomerID) 1 1 Kirkland Kirkland AS numCompanies, 0 1 NULL Aachen e.City AS EmpCity, c.City AS 0 1 NULL Albuquerque CusCity 0 1 NULL Anchorage FROM Employees e FULL JOIN … Customers c ON 0 1 NULL Versailles (e.City = c.City) 0 1 NULL Walla Walla 0 1 NULL Warszawa GROUP BY e.City, c.City Warning: Null value is eliminated by an aggregate or other SET operation. ORDER BY numEmployees DESC; (71 rows affected) Combinación de más de dos tablas Combinación de una tabla consigo misma Combinación de una tabla consigo misma SELECT E.LastName, E.FirstName, J.LastName AS ApelllidoJefe, J.FirstName AS NombreJefe FROM Employees E LEFT JOIN Employees J ON J.EmployeeID= E.ReportsTo LastName FirstName ApelllidoJefe NombreJefe -------------------- ---------- -------------------- ---------- Davolio Nancy Fuller Andrew Fuller Andrew NULL NULL Leverling Janet Fuller Andrew Peacock Margaret Fuller Andrew Buchanan Steven Fuller Andrew Suyama Michael Buchanan Steven King Robert Buchanan Steven Callahan Laura Fuller Andrew Dodsworth Anne Buchanan Steven UNION La operación de UNION es equivalente a la del algebra de conjuntos. Permite juntar en un mismo resultado múltiples tablas o resultados de consultas. Combinación de varios conjuntos de resultados  CompanyName Phone Ejemplo Union ---------------------------------------- --------------- ---- Alfreds Futterkiste 030-0074321 Ana Trujillo Emparedados y helados (5) 555-4729 Around the Horn (171) 555-7788 SELECT CompanyName, Phone Aux joyeux ecclésiastiques (1) 03.83.00.68 Berglunds snabbköp 0921-12 34 65 FROM Shippers Bigfoot Breweries (503) 555-9931 UNION Blauer See Delikatessen 0621-08460 SELECT CompanyName, Phone Blondesddsl père et fils 88.60.15.31 FROM Customers … UNION Vins et alcools Chevalier 26.47.15.10 Wartian Herkku 981-443655 SELECT CompanyName, Phone Wellington Importadora (14) 555-8122 FROM Suppliers White Clover Markets (206) 555-4112 ORDER BY CompanyName; Wilman Kala 90-224 8858 Wolski Zajazd (26) 642-7012 Zaanse Snoepfabriek (12345) 1212 UNION ALL Por defecto, las filas duplicadas son eliminadas en la operación de UNION. Para incluir los duplicados hay que utilizar UNION ALL en lugar de UNION Probar y comparar SELECT 1 AS NRO SELECT 1 AS NRO UNION ALL UNION SELECT 1 AS NRO SELECT 1 AS NRO Reglas de la UNION Cada consulta debe tener el mismo número de columnas Las columnas deben estar en el mismo orden Los tipos de las columnas deben ser compatibles. División Relacional Supongamos que necesito el código de los clientes que fueron atendidos por todos los empleados de Estados Unidos (USA)​ SELECT EmployeeID FROM Employees WHERE Country = 'USA’; EmployeeID ----------- 1 2 3 4 8 SELECT TOP 1 WITH TIES CustomerID, COUNT(DISTINCT O.EmployeeID) AS CantEmpUSA FROM Orders AS O JOIN Employees AS E ON O.EmployeeID = E.EmployeeID WHERE Country = 'USA' GROUP BY CustomerID ORDER BY CantEmpUSA DESC; División Relacional SELECT CustomerID FROM Orders WHERE EmployeeID IN (1, 2, 3, 4, 8) GROUP BY CustomerID HAVING COUNT(DISTINCT EmployeeID) = 5; Esta consulta busca las órdenes atendidas por los empleados de USA, las agrupa por CustomerID y cuenta la cantidad de empleados distintos que atendieron, devolviendo sólo aquellos que hayan sido atendidos por 5.  División Relacional SELECT CustomerID FROM Orders WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE Country = 'USA') GROUP BY CustomerID HAVING COUNT(DISTINCT EmployeeID) = (SELECT COUNT(*) FROM Employees WHERE Country = 'USA');  División Relacional SELECT CustomerID FROM Customers AS C WHERE NOT EXISTS (SELECT * FROM Employees AS E WHERE country = 'USA'​ AND NOT EXISTS (SELECT * FROM Orders AS O WHERE O.CustomerID = C.CustomerID AND O.EmployeeID = E.EmployeeID)); Vistas  Vistas Son objetos almacenados en Son reusables Se pueden utilizar para En definitiva, son consultas la base de datos limitar el acceso a los datos cuya definición se almacena en la base de datos Vistas - Sintaxis SQL Server MySQL CREATE [ OR ALTER ] VIEW [ schema_name. ] CREATE [OR REPLACE] view_name [ (column [ ,...n ] ) ] [ WITH [ ,...n ] ] [ALGORITHM = {UNDEFINED | MERGE | AS select_statement TEMPTABLE}] [ WITH CHECK OPTION ] [DEFINER = user] [;] [SQL SECURITY { DEFINER | INVOKER }] ::= VIEW view_name [(column_list)] { AS select_statement [ ENCRYPTION ] [ SCHEMABINDING ] [WITH [CASCADED | LOCAL] CHECK OPTION] [ VIEW_METADATA ] } Vistas Si buscamos habitualmente a los clientes de “USA”, podemos definirle una vista: CREATE VIEW USACusts AS SELECT CustomerID, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax FROM Customers WHERE country = N'USA'; GO Vistas Y luego podemos usarla SELECT CustomerID, CompanyName FROM USACusts;  Restricciones Todas las columnas deben tener nombre (si uso Vistas expresiones debo agregarle un ALIAS) Los nombres de las columnas deben ser únicos No usar ORDER BY Vistas - DROP VIEW nombre Eliminar