1. No category

Módulo IV: Consultas Básicas y Avanzadas



Ejecución de Consultas Básicas
Consultas de Múltiples Tablas
Técnicas Avanzadas
© Maximiliano Odstrcil - 1
Ejecución de Consultas Básicas



Recuperación de Datos
Formato de un ResultSet
Modificación de Datos
© Maximiliano Odstrcil - 2
Recuperación de Datos




Uso de la sentencia SELECT
Especificación de columnas
Uso de la cláusula WHERE
Condiciones de búsqueda de WHERE
© Maximiliano Odstrcil - 3
Uso de la sentencia SELECT
 La
sentencia SELECT se usa para recuperar los datos.
SELECT
SELECT [ALL
[ALL || DISTINCT]
DISTINCT] <<lista_selección
lista_selección>>
FROM
FROM {<tabla_fuente>}
{<tabla_fuente>} [,…
[,…nn]]
WHERE
WHERE <<predicado_búsqueda
predicado_búsqueda>>
 lista_selección:
especifica las columnas a devolver.
 FROM: indica la tabla origen de los datos.
 WHERE: especifica las filas a devolver:
 Se puede restringir el número de filas usando operadores de
comparación, cadenas y operadores lógicos.
© Maximiliano Odstrcil - 4
Especificación de columnas (1)
 La
lista de selección contiene las columnas, expresiones,
variables o constantes a listar.
 Consideraciones:
 La lista de selección devuelve los resultados en el orden listado.
 Los nombres de las columnas se separan con comas.
 El (*) devuelve todas las columnas (evitar su uso).
© Maximiliano Odstrcil - 5
Especificación de columnas (2)
USE
USE Northwind
Northwind
SELECT
SELECT EmployeeID,
EmployeeID, LastName,
LastName, FirstName,
FirstName, Title
Title
FROM
FROM Employees
Employees
EmployeeID
EmployeeID
LastName
LastName
FirstName
FirstName
Title
Title
11
22
33
44
55
66
77
88
99
Davolio
Davolio
Fuller
Fuller
Leverling
Leverling
Peacock
Peacock
Buchanan
Buchanan
Suyama
Suyama
King
King
Callahan
Callahan
Dodsworth
Dodsworth
Nancy
Nancy
Andrew
Andrew
Janet
Janet
Margaret
Margaret
Steven
Steven
Michael
Michael
Robert
Robert
Laura
Laura
Anne
Anne
Sales
SalesRepresentative
Representative
Vice
VicePresident,
President,Sales
Sales
Sales
SalesRepresentative
Representative
Sales
SalesRepresentative
Representative
Sales
SalesManager
Manager
Sales
SalesRepresentative
Representative
Sales
SalesRepresentative
Representative
Inside
InsideSales
SalesCoordinator
Coordinator
Sales
SalesRepresentative
Representative
© Maximiliano Odstrcil - 6
Uso de la cláusula WHERE (1)
 Con
la cláusula WHERE se pueden obtener filas
específicas basadas en una condición de búsqueda
(limita el número de filas devueltas por la sentencia
SELECT).
 Sólo
se devuelven las filas que cumplen con los criterios
especificados en la cláusula.
© Maximiliano Odstrcil - 7
Uso de la cláusula WHERE (2)
 Consideraciones:
 Los datos char, nchar, varchar, nvarchar, text, datetime y
smalldatetime van entre comillas simples (SQL Server).
 En lo posible se deben emplear condiciones de búsqueda
positivas.
© Maximiliano Odstrcil - 8
Uso de la cláusula WHERE (3)
USE
USE Northwind
Northwind
SELECT
SELECT EmployeeID,
EmployeeID,
FROM
FROM Employees
Employees
WHERE
WHERE EmployeeID
EmployeeID ==
EmployeeID
EmployeeID
55
© Maximiliano Odstrcil - 9
LastName,
LastName, FirstName,
FirstName, Title
Title
55
LastName
LastName FirstName
FirstName
Buchanan
Buchanan Steven
Steven
Title
Title
Sales
SalesManager
Manager
Condiciones de búsqueda de WHERE
 En
una cláusula WHERE se puede usar lo siguiente:
Descripción
Condición de Búsqueda
Comparadores
=, >, <, >=, <= y <>
Comparadores de Cadena
LIKE y NOT LIKE - %
Operadores Lógicos
AND, OR, NOT
Rango de Valores
BETWEEN y NOT BETWEEN
Lista de Valores
IN y NOT IN
Valores desconocidos
IS NOT e IS NOT NULL
© Maximiliano Odstrcil - 10
Comparadores
 Comparan
columnas,
expresiones,
variables
constantes entre sí.
 Usar condiciones positivas (son más rápidas).
SELECT
SELECT LastName,
LastName, City
City
FROM
FROM Employees
Employees
WHERE
WHERE Country
Country == 'USA'
'USA'
LastName
LastName
Davolio
Davolio
Fuller
Fuller
Leverling
Leverling
Peacock
Peacock
Callahan
Callahan
© Maximiliano Odstrcil - 11
City
City
Seattle
Seattle
Tacoma
Tacoma
Kirkland
Kirkland
Redmond
Redmond
Seattle
Seattle
y
Comparadores de Cadena (1)
 Se
puede usar el operador LIKE en combinación con
caracteres especiales para seleccionar filas comparando
cadenas de caracteres.
 Consideraciones:
 Todos los caracteres en el patrón son significantes, incluidos los
espacios antes y después.
 LIKE puede ser usado con los tipos de datos char, nchar,
varchar, nvarchar o datetime.
© Maximiliano Odstrcil - 12
Comparadores de Cadena (2)
 Caracteres
especiales:
Caracter
Descripción
%
Cualquier cadena de 0 o más caracteres
_
Cualquier caracter
[]
Cualquier caracter del conjunto
[^]
Cualquier caracter menos los del conjunto
© Maximiliano Odstrcil - 13
Comparadores de Cadena (3)
 Ejemplos:
 LIKE 'BR%': todos los nombres que empiecen con
BR.
 LIKE '_er': palabras de tres letras terminadas en er.
 LIKE '[VB]%': palabras que empiecen en V o B.
 LIKE '[0-3][^0]': números que comiencen con 0,1,2 o
3 y cuyo segundo número no sea 0.
© Maximiliano Odstrcil - 14
Comparadores de Cadena (4)
USE
USE Northwind
Northwind
SELECT
SELECT CompanyName
CompanyName
FROM
FROM Customers
Customers
WHERE
WHERE CompanyName
CompanyName LIKE
LIKE '%Restaurant%'
'%Restaurant%'
CompanyName
CompanyName
GROSELLA-Restaurante
GROSELLA-Restaurante
Lonesome
LonesomePine
PineRestaurant
Restaurant
Tortuga
TortugaRestaurante
Restaurante
© Maximiliano Odstrcil - 15
Operadores Lógicos
 Combinan
expresiones. El orden de evaluación es NOT,
AND y OR. Admiten paréntesis.
SELECT
SELECT ProductID,
ProductID, ProductName,
ProductName, SupplierID,
SupplierID, UnitPrice
UnitPrice
FROM
FROM Products
Products
WHERE
WHERE (ProductName
(ProductName LIKE
LIKE 'T%'
'T%' OR
OR ProductID
ProductID == 46)
46) AND
AND
(UnitPrice
(UnitPrice >> 16.00)
16.00)
ProductID
ProductID
14
14
29
29
62
62
© Maximiliano Odstrcil - 16
ProductName
ProductName
Tofu
Tofu
Thüringer
ThüringerRostbratwurst
Rostbratwurst
Tarte
Tarteau
ausucre
sucre
SupplierID
SupplierID
66
12
12
29
29
UnitPrice
UnitPrice
23.25
23.25
123.79
123.79
49.3
49.3
Rango de Valores (1)
 BETWEEN:
se usa para devolver filas cuyos valores de
búsqueda se encuentran dentro de un rango.
 Consideraciones:
 La inclusión de los valores de los extremos del intervalo
depende del SGBDR.
 Es mejor usar BETWEEN que dos comparadores (<= y >=)
 Evitar el uso de NOT BETWEEN por su baja performance.
 Evitar el uso con valores de fecha ya que hay que tener en
cuenta la hora (por defecto medianoche 00:00:00).
© Maximiliano Odstrcil - 17
Rango de Valores (2)
USE
USE Northwind
Northwind
SELECT
SELECT ProductName,
ProductName, UnitPrice
UnitPrice
FROM
FROM Products
Products
WHERE
WHERE UnitPrice
UnitPrice BETWEEN
BETWEEN 10
10 AND
AND 20
20
ProductName
ProductName
Chai
Chai
Chang
Chang
Aniseed
AniseedSyrup
Syrup
Genen
GenenShouyu
Shouyu
Pavlova
Pavlova
Sir
SirRodney’s
Rodney’sScones
Scones
..
..
..
© Maximiliano Odstrcil - 18
UnitPrice
UnitPrice
18
18
19
19
10
10
15.5
15.5
17.45
17.45
10
10
..
..
..
Lista de Valores (1)
 La
condición IN se utiliza para devolver filas cuyos
valores de búsqueda pertenezcan a un conjunto dado.
 Consideraciones:
 Se pueden reemplazar por series de expresiones OR.
 No incluir el valor nulo en el conjunto (resultados inesperados).
 El uso de NOT IN disminuye la performance.
© Maximiliano Odstrcil - 19
Lista de Valores (2)
USE
USE Northwind
Northwind
SELECT
SELECT CompanyName,
CompanyName, Country
Country
FROM
FROM Suppliers
Suppliers
WHERE
WHERE Country
Country IN
IN ('Japan',
('Japan', 'Italy')
'Italy')
CompanyName
CompanyName
Tokyo
TokyoTraders
Traders
Mayumi’s
Mayumi’s
Formaggi
FormaggiFortini
Fortinis.r.l.
s.r.l.
Pasta
PastaButtini
Buttinis.r.l.
s.r.l.
© Maximiliano Odstrcil - 20
Country
Country
Japan
Japan
Japan
Japan
Italy
Italy
Italy
Italy
Valores desconocidos (1)
 Una
columna tiene el valor nulo si no se ingresó valor
alguno.
 Un
valor nulo no equivale a la cadena vacía ni a 0.
 Se
usa IS NULL para devolver filas cuyos valores de
búsqueda sean nulos.
© Maximiliano Odstrcil - 21
Valores desconocidos (2)
 Consideraciones:
 Los valores nulos hacen fallar a todas las comparaciones.
 Se define la posibilidad de agregar nulos en las columnas en la
sentencia CREATE TABLE.
 Usar IS NOT NULL para devolver filas con valores conocidos.
© Maximiliano Odstrcil - 22
Valores desconocidos (3)
USE
USE Northwind
Northwind
SELECT
SELECT CompanyName,
CompanyName, Fax
Fax
FROM
FROM Suppliers
Suppliers
WHERE
WHERE Fax
Fax IS
IS NULL
NULL
CompanyName
CompanyName
Exotic
ExoticLiquids
Liquids
New
NewOrleans
OrleansCajun
CajunDelights
Delights
Tokyo
TokyoTraders
Traders
Cooperativa
Cooperativade
deQuesos
Quesos‘Las
‘LasCabras’
Cabras’
..
..
..
© Maximiliano Odstrcil - 23
Fax
Fax
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Formato de un ResultSet




Orden de los Datos
Eliminación de Duplicados
Cambio de Nombres a las Columnas
Uso de Constantes
© Maximiliano Odstrcil - 24
Orden de los Datos (1)
 La
cláusula ORDER BY ordena las filas devueltas en
orden ascendente (ASC) o descendente (DESC).
 Consideraciones:
 No se garantiza un orden determinado sin ORDER BY.
 Por defecto se ordena ascendentemente (ASC).
 Las columnas por las que se ordenan no necesitan aparecer en
la lista de selección.
 Se puede ordenar por columnas o expresiones y referirse a las
mismas por su número de orden en la lista de selección.
© Maximiliano Odstrcil - 25
Orden de los Datos (2)
SELECT
SELECT ProductID,
ProductID, ProductName,
ProductName, CategoryID,
CategoryID, UnitPrice
UnitPrice
FROM
FROM Products
Products ORDER
ORDER BY
BY CategoryID,
CategoryID, UnitPrice
UnitPrice DESC
DESC
productid
productid
38
38
43
43
22
..
..
..
63
63
88
61
61
..
..
..
productname
productname
Cote
Cotede
deBlaye
Blaye
Ipoh
IpohCoffee
Coffee
Chang
Chang
categoryid
categoryid
11
11
11
Vegie-spread
22
Vegie-spread
Northwoods
NorthwoodsCranberry
CranberrySauce
Sauce 22
Sirop
22
Siropd'érable
d'érable
© Maximiliano Odstrcil - 26
unitprice
unitprice
263.5
263.5
46
46
19
19
43.9
43.9
40
40
28.5
28.5
Eliminación de Duplicados (1)
 La
cláusula DISTINCT permite eliminar filas duplicadas.
 Consideraciones:
 La combinación de valores en la lista de selección es la que
determina la desigualdad entre filas.
 Solo se muestra un elemento de todas las filas que tienen la
misma combinación de valores de la lista de selección.
 DISTINCT no ordena, para ello se debe usar ORDER BY.
© Maximiliano Odstrcil - 27
Eliminación de Duplicados (2)
USE
USE Northwind
Northwind
SELECT
SELECT DISTINCT
DISTINCT Country
Country
FROM
FROM Suppliers
Suppliers
ORDER
ORDER BY
BY Country
Country
© Maximiliano Odstrcil - 28
Country
Country
Australia
Australia
Brazil
Brazil
Canada
Canada
Denmark
Denmark
Finland
Finland
France
France
Germany
Germany
Italy
Italy
Japan
Japan
Netherlands
Netherlands
Norway
Norway
Singapore
Singapore
Spain
Spain
Sweden
Sweden
UK
UK
USA
USA
Cambio de Nombres a las Columnas (1)
 La
cláusula AS permite crear alias a las columnas de
manera que resulten más legibles o bien den nombres a
las expresiones.
 Consideraciones:
 Por defecto, se muestran los nombres de las columnas.
 Encerrar los alias entre comillas simples si incluyen espacios.
 Se pueden crear alias para expresiones.
 Se puede omitir la cláusula AS y dejar un espacio entre la
expresión y el alias.
© Maximiliano Odstrcil - 29
Cambio de Nombres a las Columnas (2)
USE
USE Northwind
Northwind
SELECT
SELECT FirstName
FirstName AS
AS First,
First, LastName
LastName AS
AS Last,
Last,
EmployeeID
EmployeeID AS
AS 'Employee
'Employee ID:'
ID:'
FROM
FROM Employees
Employees
First
First
Nancy
Nancy
Andrew
Andrew
Janet
Janet
Margaret
Margaret
Steven
Steven
Michael
Michael
Robert
Robert
Laura
Laura
Anne
Anne
© Maximiliano Odstrcil - 30
Last
Last
Davolio
Davolio
Fuller
Fuller
Leverling
Leverling
Peacock
Peacock
Buchanan
Buchanan
Suyama
Suyama
King
King
Callahan
Callahan
Dodsworth
Dodsworth
Employee
EmployeeID:
ID:
11
22
33
44
55
66
77
88
99
Uso de Constantes
 Las
constantes o literales son letras, números o
símbolos.
SELECT
SELECT FirstName,
FirstName, LastName,
LastName,
'Identification
'Identification Number:',
Number:', EmployeeID
EmployeeID
FROM
FROM Employees
Employees
FirstName
FirstName
Nancy
Nancy
Andrew
Andrew
Janet
Janet
Margaret
Margaret
Steven
Steven
Michael
Michael
Robert
Robert
Laura
Laura
Anne
Anne
© Maximiliano Odstrcil - 31
LastName
LastName
Davolio
Davolio
Fuller
Fuller
Leverling
Leverling
Peacock
Peacock
Buchanan
Buchanan
Suyama
Suyama
King
King
Callahan
Callahan
Dodsworth
Dodsworth
EmployeeID
EmployeeID
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
Identification
IdentificationNumber:
Number:
11
22
33
44
55
66
77
88
99
Modificación de Datos



Inserción de Filas
Borrado de Filas
Modificación de Filas
© Maximiliano Odstrcil - 32
Inserción de Filas (1)
 La
sentencia INSERT agrega filas a una tabla.
INSERT
INSERT
VALUES
VALUES
[INTO]
[INTO] tabla
tabla [(lista_columnas)]
[(lista_columnas)]
(lista_valores)
(lista_valores) || DEFAULT
DEFAULT VALUES
VALUES
 Consideraciones:
 Usar la lista de columnas para forzar el orden de los valores.
 Especificar los datos a ingresar luego de la palabra VALUES. El
orden y tipo de datos deben coincidir con los de la tabla.
 La sentencia INSERT falla si se violan las constraints o Reglas.
© Maximiliano Odstrcil - 33
Inserción de Filas (2)
USE
USE Northwind
Northwind
INSERT
INSERT Customers
Customers
(CustomerID,
(CustomerID, CompanyName,
CompanyName, ContactName,
ContactName, ContactTitle,
ContactTitle,
Address,
Address, City,
City, Region,
Region, PostalCode,
PostalCode, Country,
Country, Phone,
Phone,
Fax)
Fax)
VALUES
VALUES ('PECOF',
('PECOF', 'Pecos
'Pecos Coffee
Coffee Company',
Company', 'Michael
'Michael Dunn',
Dunn',
'Owner',
'Owner', '1900
'1900 Oak
Oak Street',
Street', 'Vancouver',
'Vancouver', 'BC',
'BC',
'V3F
'V3F 2K1',
2K1', 'Canada',
'Canada', '(604)
'(604) 555-3392',
555-3392',
'(604)
'(604) 555-7293')
555-7293')
© Maximiliano Odstrcil - 34
Inserción de Filas (3)
 Cuando
se insertan filas a una tabla, se pueden emplear
las cláusulas DEFAULT o DEFAULT VALUES.
 Consideraciones sobre DEFAULT:
 Se inserta un valor nulo para aquellas columnas que no tienen
definido un valor por efecto y admiten valores nulos.
 Si la columna no admite valores nulos ni tiene valor por defecto,
la sentencia fallará.
 No se puede usar DEFAULT para columnas del tipo IDENTITY.
INSERT
INSERT
VALUES
VALUES
© Maximiliano Odstrcil - 35
Shippers
Shippers (CompanyName,
(CompanyName, Phone)
Phone)
('Kenya
('Kenya Coffee
Coffee Co.',
Co.', DEFAULT)
DEFAULT)
Inserción de Filas (4)
 DEFAULT
VALUES permite insertar toda una fila por
defecto en la tabla.
 Consideraciones:
 En columnas con tipos de datos timestamp o propiedad
IDENTITY, se inserta el siguiente valor apropiado.
 Se puede usar DEFAULT VALUES para generar datos de
prueba y poblar tablas de manera sencilla.
 No disponible para MySQL.
INSERT
INSERT Clientes
Clientes DEFAULT
DEFAULT VALUES
VALUES
© Maximiliano Odstrcil - 36
Inserción de Filas (5)
 Si
la columna tiene valores por defecto o acepta valores
nulos, se puede omitir el nombre en la sentencia
INSERT.
 Consideraciones:
 Listar sólo los nombres de columnas para los cuales haya
valores (no listar las columnas IDENTITY).
 Las columnas no nombradas adoptarán el valor por defecto.
 Especificar el valor nulo escribiendo NULL (sin comillas).
© Maximiliano Odstrcil - 37
Inserción de Filas (6)
Agregando
Agregandonuevos
nuevosdatos
datos
USE
USE Northwind
Northwind
INSERT
INSERT Shippers
Shippers (CompanyName)
(CompanyName)
VALUES
VALUES ('Fitch
('Fitch && Mather')
Mather')
Verificando
Verificandonuevos
nuevosdatos
datos
USE
USE Northwind
Northwind
SELECT
SELECT **
FROM
FROM Shippers
Shippers
WHERE
WHERE CompanyName
CompanyName == 'Fitch
'Fitch && Mather'
Mather'
Permite valores nulos
© Maximiliano Odstrcil - 38
ShipperID
ShipperID
CompanyName
CompanyName Phone
Phone
37
37
Fitch
Fitch&&Mather
Mather
NULL
NULL
Borrado de Filas (1)
 La
sentencia DELETE:
 Borra una o más filas de una tabla.
 Se puede limitar las filas a borrar (WHERE).
 Cada fila borrada se registra en el RT como una transacción.
 SQL Server:
DELETE
DELETE Orders
Orders
WHERE
WHERE DATEDIFF(MONTH,
DATEDIFF(MONTH, GETDATE(),
GETDATE(), ShippedDate)
ShippedDate) >=
>= 66

MySQL:
DELETE
DELETE FROM
FROM Orders
Orders
WHERE
WHERE DATEDIFF(CURDATE(),
DATEDIFF(CURDATE(), ShippedDate)
ShippedDate) // 30
30 >=
>= 6;
6;
© Maximiliano Odstrcil - 39
Borrado de Filas (2)
 La
sentencia TRUNCATE TABLE:
 Se usa para borrar todas las filas de una tabla, reteniendo su
estructura y sus objetos asociados.
 Se ejecuta mucho más rápido que DELETE ya que no se
registra en el Registro de Transacciones.
 Si la tabla tiene una columna IDENTITY, vuelve el valor de la
semilla al valor inicial.
TRUNCATE
TRUNCATE TABLE
TABLE Orders
Orders
© Maximiliano Odstrcil - 40
Modificación de Filas (1)
 La
sentencia UPDATE modifica los datos existentes de
las filas de las tablas.
UPDATE
UPDATE Products
Products
SET
SET UnitPrice
UnitPrice == (UnitPrice
(UnitPrice ** 1.1)
1.1)
© Maximiliano Odstrcil - 41
Modificación de Filas (2)
 Consideraciones:
 Las filas a modificar se especifican en la cláusula WHERE.
 Los valores nuevos se especifican con la cláusula SET.
 Los valores ingresados deben ser del mismo tipo de las
columnas a modificar y no deben violar las constraints y
Reglas.
 Se pueden cambiar los datos de una tabla a la vez.
 Se pueden usar expresiones basadas en columnas de la tabla
u otras tablas, variables y constantes.
© Maximiliano Odstrcil - 42
Consultas de Múltiples Tablas



Combinación de Múltiples Tablas
Combinación de Varios Resultsets
Creación de Tabla desde Resultset
© Maximiliano Odstrcil - 43
Combinación de Múltiples Tablas






Introducción a Joins
Uso de Inner Joins
Uso de Outer Joins
Uso de Cross Joins
Joins de más de dos tablas
Join de una tabla con sí misma
© Maximiliano Odstrcil - 44
Introducción a Joins (1)
 Se
unen (join) tablas para producir un conjunto simple de
resultados que incorpora filas de dos o más tablas.
SELECT
SELECT lista_selección
lista_selección
FROM
FROM tabla1
tabla1 [INNER|LEFT|RIGHT|OUTER]
[INNER|LEFT|RIGHT|OUTER] JOIN
JOIN tabla2
tabla2 ON
ON condición_join
condición_join
La cláusula JOIN especifica qué tablas son las que se unen y
cómo se unen.
 La cláusula ON especifica las columnas en común que tienen las
tablas.

© Maximiliano Odstrcil - 45
Introducción a Joins (2)
 Consideraciones:
 Especificar las condiciones del join basadas en las claves
primarias y propagadas de las tablas.
 Si la tabla tiene una clave compuesta, se deben referenciar
todas las columnas luego de la palabra ON.
 Si los nombres de las columnas son los mismos en las tablas,
usar el formato nombre_tabla.nombre_columna.
 Limitar el número de tablas en un join ya que también aumenta
el tiempo de proceso de la consulta.
© Maximiliano Odstrcil - 46
Uso de Inner Joins (1)
 Los
Inner Joins combinan tablas comparando los valores
en las columnas comunes a ambas tablas y devolviendo
sólo las filas que cumplen con la condición.
 Consideraciones:
 Los inner joins son los joins por defecto (se puede omitir “inner”)
 Especificar las columnas a listar en el ResultSet incluyéndolas
en la lista de selección con los nombres cualificados.
 Si se incluye WHERE, se restringe el nº de filas devueltas.
 No usar el valor nulo como condición del join.
 No se garantiza un orden en el resultado (usar ORDER BY).
© Maximiliano Odstrcil - 47
Uso de Inner Joins (2)
SELECT
SELECT buyer_name,
buyer_name, sales.buyer_id,
sales.buyer_id, qty
qty
FROM
FROM Buyers
Buyers INNER
INNER JOIN
JOIN Sales
Sales
ON
Buyers.buyer_id
=
Sales.buyer_id
ON Buyers.buyer_id = Sales.buyer_id
Buyers
Sales
buyer_name
buyer_name buyer_id
buyer_id
Adam
11
AdamBarr
Barr
Sean
22
SeanChai
Chai
Eva
33
EvaCorets
Corets
Erin
44
ErinO’Melia
O’Melia
buyer_id
buyer_id prod_id
prod_id qty
qty
11
22
15
15
11
33
55
44
11
37
37
33
55
11
11
44
22
1003
1003
Resultado
buyer_name
buyer_name buyer_id
buyer_id qty
qty
Adam
11
15
AdamBarr
Barr
15
11
Adam
55
AdamBarr
Barr
Erin
44
37
ErinO’Melia
O’Melia
37
Eva
33
11
EvaCorets
Corets
11
Erin
44
1003
ErinO’Melia
O’Melia
1003
© Maximiliano Odstrcil - 48
Uso de Outer Joins (1)
 Los
Left o Right Joins combinan filas desde dos tablas
que satisfacen la condición del join más las filas de la
tabla izquierda o derecha que no la satisfacen.
 Se usan para obtener los datos que están en una tabla
adicionando los que están en la otra, si es que están.
 Consideraciones:
 A LEFT JOIN B = B RIGHT JOIN A
 No usar valores nulos en la condición del join.
 Usar los Outer Joins entre dos tablas preferentemente.
 Se puede abreviar omitiendo la palabra “Outer”.
© Maximiliano Odstrcil - 49
Uso de Outer Joins (2)
SELECT
SELECT buyer_name,
buyer_name, sales.buyer_id,
sales.buyer_id, qty
qty
FROM
Buyers
LEFT
OUTER
JOIN
Sales
FROM Buyers LEFT OUTER JOIN Sales
ON
ON Buyers.buyer_id
Buyers.buyer_id == Sales.buyer_id
Sales.buyer_id
Buyers
Sales
buyer_name
buyer_name buyer_id
buyer_id
Adam
11
AdamBarr
Barr
Sean
22
SeanChai
Chai
Eva
33
EvaCorets
Corets
Erin
44
ErinO’Melia
O’Melia
buyer_id
buyer_id prod_id
prod_id qty
qty
11
22
15
15
11
33
55
44
11
37
37
33
55
11
11
44
22
1003
1003
© Maximiliano Odstrcil - 50
Resultado
buyer_name
buyer_name buyer_id
buyer_id qty
qty
Adam
11
15
AdamBarr
Barr
15
Adam
11
55
AdamBarr
Barr
Erin
44
37
ErinO’Melia
O’Melia
37
33
Eva
11
EvaCorets
Corets
11
Erin
44
1003
ErinO’Melia
O’Melia
1003
Sean
NULL
SeanChai
Chai
NULL NULL
NULL
Uso de Cross Joins (1)
 Los
Cross Joins realizan el producto cartesiano de dos
tablas (no requieren columnas en común).
 Consideraciones:
 Son raramente usados en una BD normalizada, aunque se los
utiliza para poblar rápidamente tablas para pruebas.
 Cardinalidad(A Cross Join B) = Cardinalidad(A) *Cardinalidad(B)
© Maximiliano Odstrcil - 51
Uso de Cross Joins (2)
SELECT
SELECT buyer_name,
buyer_name, qty
qty
FROM
FROM Buyers
Buyers CROSS
CROSS JOIN
JOIN Sales
Sales
Buyers
buyer_id
buyer_id
11
22
33
44
buyer_name
buyer_name
Adam
AdamBarr
Barr
Sean
SeanChai
Chai
Eva
EvaCorets
Corets
Erin
ErinO’Melia
O’Melia
© Maximiliano Odstrcil - 52
Sales
buyer_id
buyer_id prod_id
prod_id qty
qty
11
22
15
15
11
33
55
44
11
37
37
33
55
11
11
44
22
1003
1003
Resultado
buyer_name
buyer_name
Adam
AdamBarr
Barr
Adam
AdamBarr
Barr
Adam
AdamBarr
Barr
qty
qty
15
15
55
37
37
Adam
AdamBarr
Barr
Adam
AdamBarr
Barr
Sean
SeanChai
Chai
Sean
SeanChai
Chai
Sean
SeanChai
Chai
Sean
SeanChai
Chai
Sean
SeanChai
Chai
Eva
EvaCorets
Corets
Eva
EvaCorets
Corets
......
11
11
1003
1003
15
15
55
37
37
11
11
1003
1003
15
15
55
......
Joins de más de dos tablas (1)
 Es
posible unir más de dos tablas, ya que cada tabla en
un join tiene alguna columna en común con otra tabla.
 Consideraciones:
 A join B join C = (A join B) join C = D join C, con D = A join B.
 Se deben tener tablas relacionadas con claves foráneas.
 Se deben considerar las claves compuestas en los joins.
 Se puede incluir una cláusula WHERE para limitar las filas.
 El número de cláusulas joins es siempre uno menor al número
de tablas participantes.
 Los joins son conmutativos.
© Maximiliano Odstrcil - 53
Joins de más de dos tablas (2)
SELECT
SELECT buyer_name,
buyer_name, prod_name,
prod_name, qty
qty
FROM
Buyers
JOIN
Sales
FROM Buyers JOIN Sales
ON
ON Buyers.buyer_id
Buyers.buyer_id == Sales.buyer_id
Sales.buyer_id
JOIN
Produce
JOIN Produce
ON
ON Sales.prod_id
Sales.prod_id == Produce.prod_id
Produce.prod_id
Buyers
buyer_id
buyer_id buyer_name
buyer_name
11
Adam
AdamBarr
Barr
22
Sean
SeanChai
Chai
33
Eva
EvaCorets
Corets
44
Erin
ErinO’Melia
O’Melia
Sales
buyer_id
buyer_id prod_id
prod_id qty
qty
11
22
15
15
11
33
55
33
4
11
37
37
44
3
55
11
11
22
4
22
1003
1003
Resultado
buyer_name
prod_name
buyer_name
prod_name qty
qty
Erin
Apples
37
ErinO’Melia
O’Melia
Apples
37
Adam
Pears
15
AdamBarr
Barr
Pears
15
Erin
Pears
1003
ErinO’Melia
O’Melia
Pears
1003
Adam
Oranges
55
AdamBarr
Barr
Oranges
Eva
EvaCorets
Corets
© Maximiliano Odstrcil - 54
Peaches
Peaches
11
11
Produce
prod_id
prod_id prod_name
prod_name
11
Apples
Apples
22
Pears
Pears
33
Oranges
Oranges
44
Bananas
Bananas
55
Peaches
Peaches
Join de una tabla con sí misma (1)
 Se
realiza para encontrar filas que tengan valores en
común con otras filas de la misma tabla.
 Consideraciones:
 Hay que especificar alias de la tabla para referenciar dos copias
de la misma tabla (se las trata como dos tablas separadas).
 Cuando se generan las filas, al cumplir las condiciones del ON
dos veces, se generan repetidas. Se soluciona aplicando en el
WHERE una condición de desigualdad para eliminarlas.
© Maximiliano Odstrcil - 55
Join de una tabla con sí misma (2)
SELECT
SELECT a.buyer_id
a.buyer_id AS
AS buyer1,
buyer1, a.prod_id,
a.prod_id,
b.buyer_id
AS
buyer2
b.buyer_id AS buyer2
FROM
Sales
FROM Sales aa JOIN
JOIN Sales
Sales bb
ON
ON a.prod_id
a.prod_id == b.prod_id
b.prod_id
WHERE
a.buyer_id
WHERE a.buyer_id >> b.buyer_id
b.buyer_id
Sales a
Sales b
buyer_id
buyer_id prod_id
prod_id qty
qty
11
22
15
15
11
33
55
44
11
37
37
33
55
11
11
44
22
1003
1003
buyer_id
buyer_id prod_id
prod_id qty
qty
11
22
15
15
11
33
55
44
11
37
37
33
55
11
11
44
22
1003
1003
Resultado
buyer1
buyer1 prod_id
prod_id buyer2
buyer2
44
22
11
© Maximiliano Odstrcil - 56
Combinación de Varios ResultSets (1)
 El
operador UNION combina los resultados de dos o más
sentencias SELECT en un solo ResultSet.
sentencia_select
sentencia_select UNION
UNION [ALL]
[ALL] sentencia_select...
sentencia_select...
 Se
lo usa cuando los datos residen en diferentes
ubicaciones y no pueden ser accedidos en una sola
consulta.
© Maximiliano Odstrcil - 57
Combinación de Varios ResultSets (2)
 SQL
Server:
 MySQL:
© Maximiliano Odstrcil - 58
Combinación de Varios ResultSets (3)
 Consideraciones:
 Los resultados deben tener el mismo tipo de datos, número y
orden de columnas en la lista de selección.
 Automáticamente se remueven las filas duplicadas, a menos
que se emplee la cláusula ALL.
 El resultado se devuelve en cualquier orden. Para que salga
ordenado se debe emplear ORDER BY.
© Maximiliano Odstrcil - 59
Creación de Tabla desde Resultset (1)
 Se
puede crear una nueva tabla en base al resultado de
una consulta mediante la sentencia SELECT INTO (SQL
Server) o CREATE SELECT (MySQL).
 Nueva tabla en base a una consulta (SQL Server):
SELECT
SELECT ProductName
ProductName AS
AS Products,
Products, UnitPrice
UnitPrice AS
AS Price
Price
INTO
INTO PriceTable
PriceTable
FROM
FROM Products
Products
 Nueva
tabla en base a una consulta (MySQL):
CREATE
CREATE TABLE
TABLE PriceTable
PriceTable
SELECT
SELECT ProductName,
ProductName, UnitPrice
UnitPrice
FROM
FROM Products;
Products;
© Maximiliano Odstrcil - 60
Creación de Tabla desde Resultset (2)
 Consideraciones:
 Siempre se crea una tabla y se inserta el Resultset en la misma.
 Todas las columnas deben tener nombre (usar alias).
 En SQL Server se debe tener el permiso select into/bulk copy.
 Al crear la tabla se puede especificar que la misma sea
temporaria. Una tabla temporaria brinda un espacio de trabajo
para resultados intermedios.
© Maximiliano Odstrcil - 61
Creación de Tabla desde Resultset (3)
En SQL Server se pueden tener tablas temporales:
 Locales (#): el espacio que ocupan es liberado cuando se
termina la sesión que las creó.
 Globales (##): el espacio que ocupan es liberado cuando
termina la última sesión abierta en el servidor.
 En el caso de MySQL, las tablas temporales sólo pueden ser
locales.

© Maximiliano Odstrcil - 62
Creación de Tabla desde Resultset (4)
 Tabla
temporal en base a una consulta (SQL Server):
SELECT
SELECT ProductName
ProductName AS
AS Products,
Products, UnitPrice
UnitPrice AS
AS Price
Price
INTO
INTO #PriceTable
#PriceTable
FROM
FROM Products
Products
 Tabla
temporal en base a una consulta (MySQL):
CREATE
CREATE TEMPORARY
TEMPORARY TABLE
TABLE PriceTable
PriceTable
SELECT
SELECT ProductName,
ProductName, UnitPrice
UnitPrice
FROM
FROM Products;
Products;
© Maximiliano Odstrcil - 63
Técnicas Avanzadas

EXISTS y NOT EXISTS

Modificación de Datos
© Maximiliano Odstrcil - 64
EXISTS y NOT EXISTS (1)
 Definiciones:
 Subconsulta: consulta anidada dentro de otra.
 Subconsulta correlacionada: subconsulta que
utiliza los valores de la consulta externa. La
subconsulta se evalúa una vez por cada fila
procesada por la consulta externa.
© Maximiliano Odstrcil - 65
EXISTS y NOT EXISTS (2)
 Se
pueden usar las cláusulas EXISTS y NOT EXISTS
para determinar la existencia de filas.
 Formato:
 [NOT] EXISTS subconsulta
 Ejemplo:
SELECT
SELECT LastName,
LastName, EmployeeID
EmployeeID
FROM
FROM Employees
Employees ee
WHERE
WHERE EXISTS
EXISTS (SELECT
(SELECT ** FROM
FROM Orders
Orders
WHERE
WHERE e.EmployeeID
e.EmployeeID == Orders.EmployeeID
Orders.EmployeeID
AND
OrderDate
=
'9/5/97')
AND OrderDate = '9/5/97')
© Maximiliano Odstrcil - 66
EXISTS y NOT EXISTS (3)
 Consideraciones:
 Devuelven un valor TRUE o FALSE basado en la existencia.
 La consulta externa prueba la existencia de filas que la
subconsulta interna retorna (no produce datos).
© Maximiliano Odstrcil - 67
Modificación de Datos

Uso de la sentencia INSERT..SELECT

Borrado de filas basadas en otras tablas

Modificación de filas basadas en otras tablas
© Maximiliano Odstrcil - 68
Uso de la sentencia INSERT..SELECT (1)
 Para
agregar filas a una tabla existente, se pueden
emplear las sentencias INSERT ... SELECT:
INSERT
INSERT Customers
Customers
SELECT
SELECT SUBSTRING(FirstName,
SUBSTRING(FirstName, 1,
1, 3)
3) ++ SUBSTRING(LastName,
SUBSTRING(LastName, 1,
1, 2),
2),
LastName,
FirstName,
Title,
NULL
LastName, FirstName, Title, NULL
FROM
Employees
FROM Employees
INSERT
INSERT Customers
Customers
SELECT
CONCAT(SUBSTRING(FirstName,1,3),SUBSTRING(LastName,1,2)),
SELECT CONCAT(SUBSTRING(FirstName,1,3),SUBSTRING(LastName,1,2)),
LastName,FirstName,Title,NULL,NULL,NULL,NULL,NULL,NULL,NULL
LastName,FirstName,Title,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM
FROM Employees;
Employees;
© Maximiliano Odstrcil - 69
Uso de la sentencia INSERT..SELECT (2)
 Se
puede insertar el resultado de una consulta, por más
compleja que ésta sea.
 Consideraciones:
 Se agregan todas las filas que satisfacen la condición del
SELECT.
 Las columnas de la tabla destino deben tener tipos de datos
compatibles a la lista de selección.
 Se debe determinar la existencia de valores por defecto o valores
nulos en caso de omitir alguna columna.
 Siempre se agregan filas a una tabla solamente.
© Maximiliano Odstrcil - 70
Borrado de filas basadas en otras tablas (1)
 Para
borrar filas basadas en datos almacenados en otras
tablas, se puede usar DELETE con joins o subconsultas:
 SQL Server:
DELETE
DELETE FROM
FROM [Order
[Order Details]
Details]
FROM
Orders
o
JOIN
[Order
FROM Orders o JOIN [Order Details]
Details] od
od
ON
o.OrderID
=
od.OrderID
ON o.OrderID = od.OrderID
WHERE
WHERE OrderDdate
OrderDdate == '4/14/1998'
'4/14/1998'
© Maximiliano Odstrcil - 71
Borrando filas basadas en otras tablas (2)
Si se emplea una subconsulta:
 SQL Server:

DELETE
DELETE FROM
FROM [Order
[Order Details]
Details]
WHERE
OrderID
IN
WHERE OrderID IN
(SELECT
(SELECT OrderID
OrderID FROM
FROM Orders
Orders WHERE
WHERE OrderDate
OrderDate == '4/14/1998')
'4/14/1998')

MySQL:
DELETE
DELETE FROM
FROM `Order
`Order Details`
Details`
WHERE
OrderID
IN
WHERE OrderID IN
(SELECT
(SELECT OrderID
OrderID FROM
FROM Orders
Orders WHERE
WHERE OrderDate
OrderDate == '1998/4/14');
'1998/4/14');
© Maximiliano Odstrcil - 72
Modific. de filas basadas en otras tablas (1)
 Para
modificar filas basadas en datos almacenados en otras
tablas, se puede usar UPDATE con joins o subconsultas.
UPDATE
UPDATE Products
Products
SET
UnitPrice
SET UnitPrice == UnitPrice
UnitPrice ++ 22
FROM
FROM Products
Products JOIN
JOIN Suppliers
Suppliers
ON
ON Products.SupplierID
Products.SupplierID == Suppliers.SupplierID
Suppliers.SupplierID
WHERE
WHERE Suppliers.Country
Suppliers.Country == 'USA'
'USA'
 Consideraciones:
 Nunca se modifica la misma fila dos veces en una sentencia
UPDATE. Esta restricción permite el ahorro de mucho tiempo.
 Usar la cláusula FROM en la sentencia UPDATE.
© Maximiliano Odstrcil - 73
Modificando filas basadas en otras tablas (2)
UPDATE
UPDATE Products
Products
SET
SET UnitPrice
UnitPrice == UnitPrice
UnitPrice ++ 22
WHERE
WHERE SupplierID
SupplierID IN
IN
(SELECT
SupplierID
(SELECT SupplierID FROM
FROM Suppliers
Suppliers WHERE
WHERE Country
Country == 'USA')
'USA')
 Consideraciones:
 La subconsulta se ejecuta una vez para cada fila que va a ser
modificada. La subconsulta retorna un único valor.
 Si la subconsulta no puede retornar un único valor, se pueden usar
las cláusulas: IN, EXISTS, ANY y ALL.
 Considerar el uso de funciones agregadas con subconsultas
correlacionadas ya que nunca se calculan los valores 2 veces.
© Maximiliano Odstrcil - 74