MySQL combinaciones con Inner Join

Para este tutorial utilizaremos una de las bases de datos de ejemplo que nos brinda gratuitamente Mysql, se llama Sakila, también podemos descargar otros ejemplos desde la web oficial de Mysql.

Muchas universidades las utilizan para hacer pruebas con MySQL y para programación.

A continuación descomprimimos el archivo descargado y veremos dos archivos sakila-schema.sql que contiene la estructura de la base de datos y sakila-data.sql que contiene los datos de ejemplo.

Esta base de datos tiene por objetivo es proporcionar un entorno de pruebas, ya sea para desarrollar software o probar consultas sql y así poder realizar test con las posibilidades que brinda MySQL. La estructura de Sakila es compleja, posee 1000 registros y múltilpes relaciones.

La base de datos Sakila también contiene ejemplos de vistas, procedimientos almacenados y triggers o disparadores.
Esta base de datos supone un tienda de alquiler de películas que puede tener sucursales y vendedores,

El proceso supone que para alquilar una película, primero deberemos confirmar que la película esta disponible o en stock, y luego deberemos consultar si el cliente existe en la base de datos y asignaremos la película o DVD al cliente. También deberemos insertar la venta en la tabla de pagos. Dependiendo de las reglas de negocio, también puede ser necesario para comprobar si el cliente tiene un saldo pendiente.

Para verificar si el cliente existe por el nombre podemos hacer una consulta simple:

select customer.customer_id, customer.store_id, CONCAT(customer.first_name,' ',customer.last_name) as Cliente

from customer

where customer.first_name like 'Carlos%

En este tutorial nos centraremos en la consultas combinadas realizando varios ejemplos que serán explicados en detalle.

Clausula SQL Inner Join

Una cláusula SQL JOIN se utiliza para combinar filas a partir de dos o más tablas, basado en un campo común entre ellos.

INNER JOIN devuelve todas las filas combinadas de las tablas que cumplen con la condición de unión.

Ejemplo A

Queremos saber que clientes alquilaron películas para ello tenemos la tabla Customer y la tabla Rental, por lo tanto debemos saber que clientes están en la tabla Rental.

select rental.rental_id,customer.first_name,customer.last_name,rental.rental_date,rental.return_date

from rental
INNER JOIN customer

ON  rental.customer_id=customer.customer_id

El resultado de ejecutar esta clausula será el siguiente:

En este caso es lo mismo hacer la consulta con un Where:

select rental.rental_id,customer.first_name,customer.last_name,rental.rental_date,rental.return_date
from rental, customer

where rental.customer_id=customer.customer_id

La diferencia radica en que el inner join en la optimización de la consulta SQL que sera algo mas rápida.

Ejemplo B

Tenemos dos tiendas o sucursales que generan una lista diaria de los alquileres atrasados para que los clientes pueden ser contactados y pedirles que devuelvan la película.

Para generar este listado, deberemos buscar en la tabla del rental las películas con una fecha de regreso que es NULL osea sin fecha y que la fecha de alquiler supere el tiempo o cantidad de días establecidos que puede conservar el cliente la película. Si se cumple esta condición, entonces el cliente esta adeudando la película y se deberá mostrar en el listado el nombre de la película junto con el nombre del cliente, número de teléfono y email. También mostraremos si la película esta pagada, en que fecha se pago y cuanto se pago.

SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title,customer.email,rental.return_date, payment.payment_date, payment.amount

FROM rental
INNER JOIN customer ON rental.customer_id = customer.customer_id

INNER JOIN address ON customer.address_id = address.address_id
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id

INNER JOIN film ON inventory.film_id = film.film_id
INNER JOIN payment ON payment.rental_id = rental.rental_id

WHERE rental.return_date IS NULL
AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()

Ejemplo C

Queremos averiguar cual es el cliente que más películas alquilo para ello escribimos la siguiente clausula

Select CONCAT(customer.first_name,' ',customer.last_name) as Cliente, count(rental.customer_id) as Total from rental

inner join customer
on rental.customer_id=customer.customer_id

group by rental.customer_id
***** by total desc

Así podremos hacer muchas pruebas con Mysql para testear rendimiento. En otro tutorial avanzaremos en consultas más complejas:

Ejemplo C

Queremos averiguar cual es el cliente que más películas alquilo para ello escribimos la siguiente clausula

Select CONCAT(customer.first_name,' ',customer.last_name) as Cliente, count(rental.customer_id) as Total from rental

inner join customer

on rental.customer_id=customer.customer_id
group by rental.customer_id

***** by total desc

Así podremos hacer muchas pruebas con MySQL para testear rendimiento. En otro tutorial avanzaremos en consultas mas complejas.