4. Consultas Avanzadas

Introducción a las consultas avanzadas

Uso de Múltiples Tablas (JOIN): Cuando los datos que necesitas se encuentran en diferentes tablas, puedes combinarlas utilizando la cláusula JOIN.
Consultas Anidadas: Las consultas anidadas, también conocidas como consultas dentro de consultas, se utilizan para realizar operaciones complejas en una consulta principal.
Subconsultas: Las subconsultas son consultas que se utilizan dentro de una cláusula WHERE, HAVING o FROM de una consulta principal.
Tablas Derivadas: Las tablas derivadas, también conocidas como vistas de subconsulta, son consultas que se utilizan como si fueran tablas en una consulta principal.

¿Que son los JOINs?

En SQL Server, los JOINs son una poderosa operación que nos permite combinar filas de dos o mas tablas basadas en una columna relacionada.

Tipos de JOINs

INNER JOIN

Es un de los tipos de JOINs más comunes.
Nos permite obtener solo las filas que tienen coincidencias en ambas tablas que estamos uniendo.
```
SELECT columnas FROM tabla1 INNER JOIN tabla2 ON tabla1.columna = tabla2.columna;
```

LEFT JOIN (LEFT OUTER JOIN)

Devuelve todas las filas de la tabla de la izquierda y las coincidencias de la tabla de la derecha.
Si no hay coincidencias, aun obtendremos las filas de la tabla de la izquierda, pero con valores nulos en las columnas de la tabla de la derecha.
```
SELECT columnas FROM tabla1 LEFT JOIN tabla2 ON tabla1.columna = tabla2.columna;
```

RIGHT JOIN (RIGHT OUTER JOIN)

Devuelve todas las filas de la tabla de la derecha y las coincidencias de la tabla de la izquierda.
Si no hay coincidencias, aun obtendremos las filas de la tabla de la derecha, con valores nulos en las columnas de la tabla de la izquierda.
```
SELECT columnas FROM tabla1 RIGHT JOIN tabla2 ON tabla1.columna = tabla2.columna;
```

FULL JOIN (FULL OUTER JOIN)

Nos devuelve todas las filas de ambas tablas, incluyendo las coincidencias y las filas sin coincidencias.

En caso de no haber coincidencias, las columnas correspondientes tendrán valores nulos.

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

CROSS JOIN: devuelve el producto cartesiano de las filas de ambas tablas, es decir, combina cada fila de la tabla izquierda con todas las filas de la tabla derecha.

SELECT columnas FROM tabla1 CROSS JOIN tabla2;

SELF JOIN: Dos copias de la misma tabla, pero con distintos alias (nombres temporales) para diferenciarlas.

SELECT columnas FROM tabla t1 JOIN tabla t2 ON condiciones_de_relacion;

Consultas anidadas y subconsultas

Consultas anidadas

Una consulta anidada es una consulta que se encuentra dentro de otra consulta más grande. En otras palabras, una consulta anidada se utiliza como parte de una cláusula de la consulta principal.

La consulta anidada se resuelve primero y su resultado se utiliza en la consulta principal para realizar una operación adicional. Las consultas anidadas son útiles cuando necesitamos realizar operaciones complejas o cálculos que dependen de los resultados de otra consulta.

SELECT OrderID, Quantity, (SELECT MAX(Quantity) FROM OrderDetails) AS MAXQuantity FROM OrderDetails

Subconsultas

Las subconsultas se utilizan para filtrar los resultados de una consulta principal mediante el uso de una consulta secundaria.

SELECT * FROM OrderDetails WHERE Quantity = (SELECT MAX(Quantity) FROM OrderDetails)

Vistas

Las vistas (“views”) en SQL son un mecanismo que permite generar un resultado a partir de una consulta (query) almacenado, y ejecutar nuevas consultas sobre este resultado como si fuera una tabla normal. Las vistas tienen la misma estructura que una tabla: filas y columnas.
Representación virtual de una tabla que se deriva de una o más tablas existentes o de otras vistas.
Una vista no es una tabla física, sino más bien una consulta almacenada.
Las vistas proporcionan una forma de abstraer la complejidad de las consultas y permiten a los usuarios acceder a datos específicos sin tener que escribir consultas complejas repetidamente.
```
CREATE VIEW nombre_vista AS SELECT columna1, columna2, ... FROM nombre_tabla WHERE condiciones;
```

Consultas con múltiples conjuntos de resultados

UNION

El operador UNION se utiliza para combinar el conjunto de resultados de dos o más instrucciones SELECT.
El operador UNION selecciona sólo valores distintos de forma predeterminada. Para permitir valores duplicados, utilice UNION ALL.

SELECT City FROM Customers 
UNION 
SELECT City FROM Suppliers ORDER BY City;

INTERSECT INTERSECT devuelve filas distintas que son el resultado del operador de las consultas de entrada izquierda y derecha.

-- Uses AdventureWorks
SELECT ProductID FROM Production.Product;
--Result: 504 Rows

SELECT ProductID FROM Production.Product
INTERSECT
SELECT ProductID FROM Production.WorkOrder;
--Result: 238 Rows (products that have work orders)

EXCEPT EXCEPT devuelve filas distintas de la consulta de entrada izquierda que no son de salida en la consulta de entrada derecha.

-- Uses AdventureWorks
SELECT ProductID FROM Production.Product
EXCEPT
SELECT ProductID FROM Production.WorkOrder;
--Result: 266 Rows (products without work orders)

SELECT ProductID FROM Production.WorkOrder
EXCEPT
SELECT ProductID FROM Production.Product;
--Result: 0 Rows (work orders without products)

Operaciones de conjunto

SELECT item FROM ( 
	SELECT item FROM Lunch 
	EXCEPT SELECT item FROM Dinner 
	) Only_Lunch 
	UNION 
	SELECT item FROM ( 
	SELECT item FROM Dinner 
	EXCEPT SELECT item FROM Lunch 
	) Only_Dinner; --Items you only ate once in the day