🧩 JOINS

JOIN (INNER JOIN)

🔗 INNER JOIN

💡 Escribir INNER es opcional (INNER significa “interior”)

SELECT * 
FROM orders 
JOIN customers 
  ON orders.customer_id = customers.customer_id;

📤 Resultado (fragmento):

order_id	customer_id	order_date	first_name	last_name
1	3	2019-01-15	Ana	López
2	5	2019-02-10	Luis	Rojas
3	2	2019-03-05	María	Gómez

Ejercicio Joins 1

🧾 JOIN con columnas específicas

SELECT order_id, first_name, last_name
FROM orders 
JOIN customers 
  ON orders.customer_id = customers.customer_id;

📤 Resultado:

order_id	first_name	last_name
1	Ana	López
2	Luis	Rojas
3	María	Gómez

❗ Ambigüedad en nombres

SELECT order_id, customer_id, first_name, last_name
FROM orders 
JOIN customers 
  ON orders.customer_id = customers.customer_id;

⚠️ Error: customer_id es ambiguo, ya que existe en ambas tablas.

✅ Solución: especificar la tabla

SELECT order_id, orders.customer_id, first_name, last_name
FROM orders 
JOIN customers 
  ON orders.customer_id = customers.customer_id;

📤 Resultado:

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

Ejercicio Joins 2

✨ Uso de alias

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id;

📤 Resultado:

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

Ejercicio Joins 3

🔍 Combinación con WHERE

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE order_id = 4;

📤 Resultado:

order_id	customer_id	first_name	last_name
4	6	Juan	Torres

🔡 JOIN + LIKE + ORDER

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE first_name LIKE '%s';

📤 Resultado:

order_id	customer_id	first_name	last_name
6	8	Jesús	Pérez
9	10	Carlos	Díaz
10	11	Andrés	Ruiz

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
ORDER BY order_date;

📤 Resultado (ordenado por fecha):

order_id	order_date	first_name
1	2019-01-10	Ana
2	2019-02-08	Luis
3	2019-03-12	María

Ejercicio Joins 4

🔁 JOIN entre diferentes bases

JOIN entre diferentes bases

USE sql_store;

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
  ON oi.product_id = p.product_id;

📤 Resultado:

order_id	product_id	quantity	name
1	2	3	Mouse
1	4	1	Keyboard
2	6	2	Monitor

💡 Cambiar la base activa

USE sql_inventory;

SELECT *
FROM sql_store.order_items oi
JOIN products p
  ON oi.product_id = p.product_id;

📤 Mismo resultado, distinta base activa.

Ejercicio Joins 5

🧑‍🤝‍🧑 SELF JOIN

En la tabla employees de sql_hr, el empleado 37270 no tiene manager porque él mismo es el gerente.

USE sql_hr;

SELECT *
FROM employees e
JOIN employees m
  ON e.reports_to = m.employee_id;

📤 Resultado (fragmento):

e.employee_id	e.first_name	reports_to	m.first_name
37291	Ana	37270	Carlos
37292	Luis	37270	Carlos
37293	Elka	37271	María

✅ Mostrar nombres claros

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m
  ON e.reports_to = m.employee_id;

📤 Resultado:

employee_id	first_name	manager
37291	Ana	Carlos
37292	Luis	Carlos
37293	Elka	María

Ejercicio Joins 5

🧩 JOIN múltiples tablas

USE sql_store;

SELECT 
  o.order_id,
  o.order_date,
  c.first_name,
  c.last_name,
  os.name AS status
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
JOIN order_statuses os
  ON o.status = os.order_status_id;

🧩 JOIN múltiples tablas

| order_id | order_date | first_name | last_name | status  |
| -------- | ---------- | ---------- | --------- | ------- |
| 1        | 2019-01-10 | Ana        | López     | Shipped |
| 2        | 2019-02-08 | Luis       | Rojas     | Shipped |
| 3        | 2019-03-12 | María      | Gómez     | Pending |

Ejercicio Joins 6

🧮 Compound Join Conditions

💡 Cuando tenemos múltiples condiciones para unir dos tablas.

USE sql_store;

SELECT *
FROM order_items oi
JOIN order_items_notes oin
  ON oi.order_id = oin.order_id
  AND oi.product_id = oin.product_id;

Resultado

order_id	product_id	quantity	note
6	2	4	Good quality
6	3	5	Discount applied
7	1	2	Fragile

🧭 Resumen visual

Tipo de JOIN	Descripción breve
INNER JOIN	Une filas con coincidencias en ambas tablas
SELF JOIN	Une una tabla consigo misma
MULTIPLE JOIN	Conecta más de dos tablas
COMPOUND JOIN	Usa más de una condición en el ON