Ejecute múltiples combinaciones en una consulta en MYSQL

1. Ejecute múltiples combinaciones en una consulta en MYSQL - Construcción de consultas
2. Ejecute múltiples combinaciones en una consulta en MYSQL: combinación de tres tablas con una combinación natural
3. Ejecute múltiples combinaciones en una consulta en MYSQL: combinación de tres tablas con la palabra clave ON
4. Ejecute múltiples combinaciones en una consulta en MYSQL - Unión de tres tablas dentro del bloque WHERE
5. Ejecute múltiples combinaciones en una consulta en MYSQL: el caso de combinación externa

¿Alguna vez se preguntó cómo incluir múltiples combinaciones en una consulta en MySQL? Has venido al lugar correcto. Recuerde que las uniones nos permiten acceder a información en otras tablas. Esta información está contenida por separado para evitar redundancias. Consideremos el siguiente ejemplo. Comencemos creando tres tablas.

• client(client_id, client_name) define un cliente identificado por client_id y denominado client_name:

CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(255)
);

• product(product_id, product_name, unit_price, supplier_cost) representa un producto en la tienda identificado por product_id y denominado product_name vendido a unit_price. El costo de comprar una unidad del producto al proveedor viene dado por supplier_cost:

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_price INT,
    supplier_cost INT
);

• product_order(order_id, product_id, client_id, quantity) representa un pedido identificado por order_id que hace referencia al producto product_id comprado por el cliente client_id con una cantidad de quantity:

CREATE TABLE product_order (
    order_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    client_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (client_id) REFERENCES client(client_id)
);

Como puede ver, es bastante minimalista, pero funcionará. Tómese un momento para notar que no hay información redundante. El nombre del producto no está presente en la tabla product_order. Si ese fuera el caso, el nombre del producto se habría repetido cada vez que se compró.

Nuestro trabajo aquí es devolver el beneficio obtenido para cada cliente. Desde una perspectiva empresarial, se pueden proponer consultas más complejas y útiles, pero solo mostramos una combinación de varias tablas. Puede completar su base de datos con los siguientes valores para probar las consultas.

INSERT INTO client VALUES (1, 'John');
INSERT INTO client VALUES (2, 'Mehdi');
INSERT INTO client VALUES (3, 'Ali');
INSERT INTO product VALUES (1, 'laptop', 500, 250);
INSERT INTO product VALUES (2, 'tablet', 600, 550);
INSERT INTO product_order VALUES (1, 1, 1, 3);
INSERT INTO product_order VALUES (2, 1, 1, 3);
INSERT INTO product_order VALUES (3, 2, 2, 6);

Ejecute múltiples combinaciones en una consulta en MYSQL - Construcción de consultas

El beneficio asociado a un pedido se calcula de la siguiente manera:

$$profit = quantity * (unit\_price - supplier\_cost)$$

Como puede ver, para nuestra consulta de destino, necesitamos tres valores. La cantidad se encuentra en product_order, el precio unitario y el costo del proveedor se encuentran en producto, y finalmente, el nombre del cliente se encuentra en cliente. De ahí la necesidad de una combinación de tres tablas. Damos los resultados de la consulta después de cada consulta.

Ejecute múltiples combinaciones en una consulta en MYSQL: combinación de tres tablas con una combinación natural

Por diseño, las claves externas en las diferentes tablas tienen el mismo nombre que las claves primarias referenciadas. Podemos usar una combinación natural para vincular las tres tablas de la siguiente manera.

SELECT client_name, SUM(quantity * (unit_price - supplier_cost)) AS profit
FROM product_order 
     NATURAL JOIN product
     NATURAL JOIN client
GROUP BY client_id;

Producción :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

Ejecute múltiples combinaciones en una consulta en MYSQL: combinación de tres tablas con la palabra clave ON

Existe otra posibilidad de cumplir nuestro objetivo. Podemos usar la palabra clave ON como en:

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
        ON product_order.product_id = product.product_id 
     JOIN client
        ON product_order.client_id = client.client_id
GROUP BY client.client_id;

Producción :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

Ejecute múltiples combinaciones en una consulta en MYSQL - Unión de tres tablas dentro del bloque WHERE

Finalmente, las condiciones en las que se realizan las uniones se pueden incorporar en el bloque WHERE.

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
     JOIN client
WHERE product_order.product_id = product.product_id 
      AND product_order.client_id = client.client_id
GROUP BY client.client_id;

Producción :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

Ejecute múltiples combinaciones en una consulta en MYSQL: el caso de combinación externa

Recuerde que una combinación se realiza en condiciones de igualdad entre atributos. Si no existe tal igualdad en ciertas filas de las tablas, la fila combinada no se incluirá en la combinación resultante (la combinación interna es la predeterminada).

Esto puede resultar problemático.

Para la consulta anterior, los clientes que existen en la base de datos pero que nunca compraron ningún producto no aparecerían en el resultado. No tienen líneas asociadas en la tabla product_order, como se muestra en la imagen a continuación.

Tal caso podría surgir cuando se utiliza una aplicación web en la que algunos clientes crearon una cuenta pero aún no compraron nada. Una solución sería utilizar un LEFT OUTER JOIN donde los clientes que no tienen pedidos anteriores se asocian con los atributos NULL product_order.

La consulta final es:

SELECT client_name, SUM(IFNULL(quantity, 0) * (IFNULL(unit_price, 0) - IFNULL(supplier_cost, 0))) AS profit
FROM client
     LEFT OUTER JOIN product_order
         ON product_order.client_id = client.client_id
     LEFT OUTER JOIN product
         ON product.product_id = product_order.product_id
GROUP BY client.client_id;

Producción :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |
| Ali         | 0      |

Como se indicó anteriormente, si no hay un pedido para el cliente actual, los atributos de la tabla product_order se establecen en NULL, incluida la cantidad, lo mismo para los atributos de la tabla product. Si queremos un valor de beneficio cero para esos clientes, podemos transformar el valor de la cantidad NULL a cero utilizando la función IFNULL.

Lo mismo con unit_price y supply_cost. Se puede utilizar cualquier otro valor predeterminado que no sea 0. Haga clic aquí para obtener detalles sobre la función IFNULL.

En la imagen de abajo damos una ilustración de cómo la unión interna se compara con la externa.