Cómo consultar varias tablas de bases de datos a la vez con uniones SQL

Aranzulla de Los Pobres

Uno de los mayores beneficios de usar bases de datos relacionales como MySQL es que su estructura relacional le permite almacenar y consultar información fácilmente en múltiples tablas.

Exploremos cómo recuperar exactamente los datos que desea de varias tablas de bases de datos y las diversas uniones disponibles que le permiten obtener los resultados exactos que desea.

Inicializar la base de datos de muestra

Esto no es necesario, pero si desea seguir los ejemplos de este artículo, puede inicializar una base de datos de muestra localmente con los siguientes comandos de terminal:

 git clone https://github.com/mdizak/sample-select-db.git
 cd sample-select-db
 sudo mysql < store.sql
 sudo mysql sampledb
 mysql> SELECT COUNT(*) FROM customers;

Debería obtener un resultado que indique que hay 2000 filas dentro de la tabla de clientes .

Unión predeterminada / INNER

La combinación predeterminada que se utiliza en las bases de datos MySQL se denomina combinación INNER y es la más común y sencilla. Esta combinación devuelve todos los registros para los que hay registros coincidentes en ambas tablas y descarta todos los demás registros.

Por ejemplo, si desea ver el nombre y apellido del cliente, más el monto del pedido y la fecha para todos los pedidos superiores a $ 1000, puede usar la siguiente declaración SQL:

 
SELECT
 c.id, c.first_name, c.last_name, o.amount, o.created_at
 FROM
 customers c, orders o
 WHERE
 o.customer_id = c.id AND o.amount >= 1000;

Algunas notas sobre la consulta anterior:

  • Se están seleccionando cinco columnas diferentes, tres de la tabla de clientes y dos de la tabla de pedidos.
  • Dentro de la cláusula FROM, las dos tablas están definidas, pero con las letras "c" y "o" como sufijo. Estos simplemente especifican alias dentro de SQL, pueden ser los que desee y se utilizan para acortar la consulta SQL.
  • O.customer_id = c.id es el aspecto de unión de la consulta y garantiza la correlación adecuada entre clientes y pedidos.

A continuación, se muestra una forma diferente y técnicamente más sintácticamente correcta de escribir la misma consulta:

 
SELECT
 c.id, c.first_name, c.last_name, o.amount, o.created_at
 FROM
 customers c INNER JOIN orders o
 ON
 customer_id = c.id
 WHERE
 o.amount >= 1000;

La consulta anterior tiende a ser un poco más fácil de leer, ya que puede ver fácilmente la unión entre la tabla de clientes y pedidos. Sin embargo, a todos los efectos, estas dos consultas son iguales y producirán exactamente los mismos registros.

IZQUIERDA se une

Las uniones izquierdas devolverán todos los registros de la tabla izquierda que también coincidan con los registros de la tabla derecha y descartarán todos los demás registros. Por ejemplo, tal vez desee ver la cantidad total de ventas de cada producto en la base de datos, puede intentar usar una consulta como:

 
SELECT
 p.name, sum(item.amount) AS tamount
 FROM
 orders_items item LEFT JOIN products p
 ON
 item.product_id = p.id
 GROUP BY item.product_id ORDER BY tamount DESC

Esto da como resultado una bonita vista de dos columnas que muestra el nombre del producto con la cantidad total de ventas y funciona como se esperaba. La consulta pasó por todos los productos dentro de la tabla orders_items, los unió a los registros dentro de la tabla de productos y devolvió el monto total de ventas de cada uno.

DERECHO se une

Usando el ejemplo anterior, tenga en cuenta el hecho de que la consulta anterior solo devolvió 19 registros, mientras que hay un total de 22 productos en la base de datos. Esto se debe a que la consulta comenzó con la tabla orders_items y se unió a la tabla de productos, y dado que algunos productos nunca se han pedido, no existen registros de esos productos dentro de la tabla orders_items.

¿Qué sucede si desea obtener una lista de todos los productos con montos de ventas, incluidos los productos que no se han pedido? Pruebe una combinación correcta con la siguiente consulta:

 
SELECT
 p.name, sum(item.amount) AS tamount
 FROM
 orders_items item RIGHT JOIN products p
 ON
 item.product_id = p.id
 GROUP BY p.id ORDER BY tamount DESC

Eso es mejor, y la consulta ahora devuelve los 22 productos completos y tres de ellos tienen una cantidad nula . Esto se debe a que en lugar de usar orders_items como la tabla principal que se une a la tabla de productos, la combinación derecha invierte el pedido y une la tabla de productos a la tabla orders_items.

Varias combinaciones en una consulta

A veces, es necesario unir tres o más tablas para obtener un conjunto específico de resultados.

Por ejemplo, tal vez desee una lista de todos los clientes que han comprado el microondas (identificación de producto n. ° 1), incluido su nombre y fecha de pedido. Esto requiere un SELECT en tres tablas que se puede hacer usando dos combinaciones con la siguiente consulta:

 
SELECT
 c.first_name, c.last_name, o.amount, o.created_at
 FROM
 customers c INNER JOIN orders o
 ON
 c.id = o.customer_id INNER JOIN orders_items item
 ON
 item.order_id = o.id
 WHERE
 item.product_id = 1 ORDER BY o.created_at;

Esta consulta devuelve los 426 pedidos del microondas y funciona como se esperaba. Primero hace coincidir todos los clientes con sus respectivos pedidos, luego las consultas adicionales que se establecen al hacer coincidir todos los pedidos con solo aquellos dentro de la tabla orders_items que contienen el producto de microondas (id # 1).

Nunca use subconsultas con cláusulas IN

Como nota al margen rápida, a toda costa siempre debe evitar el uso de subconsultas dentro de sus consultas SQL como:

 SELECT first_name,last_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'approved' AND amount < 100);

Las consultas como las anteriores son muy ineficientes, usan una gran cantidad de recursos y deben evitarse tanto como sea posible. En su lugar, utilice las combinaciones adecuadas como se describe en las secciones anteriores. Por ejemplo, la consulta anterior debe reescribirse como:

 SELECT c.first_name, c.last_name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'approved' AND o.amount < 100;

Ahorre tiempo con las uniones SQL

Con suerte, este artículo le ha ayudado a mostrarle el poder de las bases de datos relacionales como MySQL, y cómo crear consultas SQL que recuperan registros de varias tablas dentro de una consulta utilizando uniones, lo que le permite recuperar los resultados exactos deseados.

Ha aprendido tres combinaciones diferentes dentro de SQL, cómo asignar un alias a los nombres de columnas y tablas, cómo usar múltiples combinaciones en una consulta y por qué debe evitar las subconsultas. No vuelva a intentar compilar manualmente diferentes conjuntos de datos en uno y comience a utilizar combinaciones para impresionar a sus compañeros de trabajo y ahorrar tiempo.