SUBCONSULTAS y VISTAS

🧩 SUBCONSULTAS

💡 Una subconsulta es una instrucción SELECT dentro de otra sentencia SQL.

Comparación simple

-- Encontrar productos más caros que la Lechuga (id=3)
SELECT *
FROM products
WHERE unit_price > (
  SELECT unit_price
  FROM products
  WHERE product_id = 3
);

📤 Resultado:

product_id	name	unit_price
4	Manzana	3.10
6	Tomate	2.95
8	Uvas	4.20

Ejercicio – CComplejas 1 Ejercicio – CComplejas – Subconsultas

🔍 Operador IN

USE sql_store;

-- Encontrar productos que nunca han sido ordenados
SELECT *
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id
  FROM order_items
);

📤 Resultado:

product_id	name	unit_price
11	Sandía	2.00
12	Fresas	3.50
15	Piña	4.00

Ejercicio – CComplejas 2 Ejercicio – CComplejas – IN

🔗 Subconsultas vs JOIN

-- Clientes sin facturas, usando JOIN
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL;

📤 Resultado:

client_id	name	invoice_id
7	SoftX	NULL
9	BioLabs	NULL
12	Neotech	NULL

Ejercicio – Subconsultas vs Joins

🔍 JOIN directo (sin subconsulta)

SELECT DISTINCT customer_id, first_name, last_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3;

📤 Resultado:

customer_id	first_name	last_name
2	María	Gómez
3	Ana	López
5	Luis	Rojas

🧮 Operador ALL

💡 Verifica que se cumpla la condición para todos los valores del conjunto.

USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > ALL (
  SELECT invoice_total
  FROM invoices
  WHERE client_id = 3
);

📤 Resultado:

invoice_id	client_id	invoice_total
9	1	1200
10	4	980
12	2	1500

Ejercicio – CComplejas 3

🔢 Operador ANY

💡 Se cumple si es verdadero para al menos un valor del conjunto.

SELECT *
FROM invoices
WHERE invoice_total > ANY (
  SELECT invoice_total
  FROM invoices
  WHERE client_id = 3
);

📤 Resultado:

invoice_id	client_id	invoice_total
5	1	400
8	2	250
11	4	700

Ejercicio – CComplejas – ANY

🔁 Subconsultas correlacionadas

💡 La subconsulta usa valores de la consulta externa. Se evalúa por cada fila, por lo que puede ser menos eficiente.

-- Ejemplo conceptual
SELECT *
FROM invoices i
WHERE invoice_total > (
   SELECT AVG(invoice_total)
   FROM invoices j
   WHERE j.client_id = i.client_id
);

📤 Resultado:

invoice_id	client_id	invoice_total
5	1	980
8	2	750
9	3	1150

Ejercicio – CComplejas – Subconsultas Relacionadas

🧮 Subconsultas en SELECT

💡 Una subconsulta puede aparecer como columna calculada.

SELECT 
   invoice_id,
   invoice_total, 
   (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
   (invoice_total - (SELECT AVG(invoice_total) FROM invoices)) AS diferencia
FROM invoices;

📤 Resultado (fragmento):

invoice_id	invoice_total	invoice_average	diferencia
1	400	380	20
2	600	380	220
3	200	380	-180

Ejercicio – CComplejas – Subconsultas en SELECT

🧾 Subconsultas en FROM

💡 Permite tratar el resultado de una subconsulta como una tabla temporal.

SELECT *
FROM (
   SELECT 
      client_id,
      name,
      (SELECT SUM(invoice_total)
         FROM invoices
         WHERE client_id = c.client_id) AS total_sales,
      (SELECT AVG(invoice_total) FROM invoices) AS average,
      (SELECT total_sales - average) AS difference
   FROM clients c
) AS ventas_verano;

📤 Resultado:

client_id	name	total_sales	average	difference
1	Myworks	2200	380	1820
2	Flextech	1500	380	1120
3	Omnitech	700	380	320

Filtrar el resultado de la subconsulta

SELECT *
FROM (
   SELECT 
      client_id,
      name,
      (SELECT SUM(invoice_total)
         FROM invoices
         WHERE client_id = c.client_id) AS total_sales,
      (SELECT AVG(invoice_total) FROM invoices) AS average,
      (SELECT total_sales - average) AS difference
   FROM clients c
) AS ventas_verano
WHERE total_sales IS NOT NULL;

📤 Resultado filtrado:

client_id	name	total_sales	difference
1	Myworks	2200	1820
2	Flextech	1500	1120
3	Omnitech	700	320

🪟 VISTAS

💡 Una vista (VIEW) es una consulta almacenada en la base de datos que actúa como una tabla virtual.

Ventajas:

• Capa de abstracción sobre las tablas.
• Encapsula consultas complejas.
• Facilita consultas repetidas.

Sintaxis general de vistas

CREATE [TEMP] VIEW [IF NOT EXISTS] view_name [(column_list)]
AS 
  SELECT statement;

🚫 Las vistas no permiten INSERT, DELETE ni UPDATE directos.

Crear una vista de ejemplo

CREATE VIEW vista_ejem AS  
SELECT 
   client_id,
   name,
   (SELECT SUM(invoice_total)
        FROM invoices
        WHERE client_id = c.client_id) AS total_sales,
   (SELECT AVG(invoice_total) FROM invoices) AS average,
   (SELECT total_sales - average) AS difference
FROM clients c;

📤 Vista creada correctamente.

SELECT * FROM vista_ejem;

📤 Resultado de la vista:

client_id	name	total_sales	average	difference
1	Myworks	2200	380	1820
2	Flextech	1500	380	1120
3	Omnitech	700	380	320

🧠 Resumen final

Tipo de Subconsulta	Ubicación	Ejemplo
Escalar	WHERE o SELECT	(SELECT MAX(...))
En conjunto	IN, ANY, ALL	IN (SELECT …)
Correlacionada	Usa valor externo	WHERE i.client_id = c.client_id
En FROM	Se trata como tabla	FROM (SELECT …)
En vista	Consulta almacenada	CREATE VIEW ... AS SELECT ...