Como consultar várias tabelas de banco de dados de uma só vez com associações SQL

Um dos maiores benefícios de usar bancos de dados relacionais como o MySQL é que sua estrutura relacional permite armazenar e consultar informações facilmente em várias tabelas.

Vamos explorar como recuperar exatamente os dados que você deseja de várias tabelas de banco de dados e as várias junções disponíveis que permitem obter os resultados exatos que você deseja.

Inicializar banco de dados de amostra

Isso não é obrigatório, mas se desejar seguir os exemplos neste artigo, você pode inicializar um banco de dados de amostra localmente com os comandos de terminal abaixo:

 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;

Você deve obter um resultado informando que há 2.000 linhas na tabela de clientes .

Associação padrão / INNER

A junção padrão usada nos bancos de dados MySQL é chamada de junção INNER e é a mais comum e direta. Essa junção retorna todos os registros para os quais há registros correspondentes em ambas as tabelas e descarta todos os outros registros.

Por exemplo, se você quiser ver o nome e o sobrenome do cliente, além do valor do pedido e a data para todos os pedidos superiores a $ 1000, poderá usar a seguinte instrução 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;

Algumas notas sobre a consulta acima:

• Cinco colunas diferentes estão sendo selecionadas, três da tabela de clientes e duas da tabela de pedidos.
• Na cláusula FROM, as duas tabelas são definidas, mas com o sufixo das letras "c" e "o". Eles simplesmente especificam aliases no SQL, podem ser qualquer coisa que você desejar e são usados ​​para encurtar a consulta SQL.
• O o.customer_id = c.id é o aspecto de junção da consulta e garante a correlação adequada entre clientes e pedidos.

Uma maneira diferente e tecnicamente mais sintaticamente correta de escrever a mesma consulta está abaixo:

 
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;

A consulta acima tende a ser um pouco mais fácil de ler, pois você pode ver facilmente a junção entre a tabela de clientes e pedidos. Para todas as intenções, porém, essas duas consultas são iguais e produzirão exatamente os mesmos registros.

LEFT Joins

As junções à esquerda retornarão todos os registros da tabela à esquerda que também correspondem aos registros da tabela à direita e descartarão todos os outros registros. Por exemplo, talvez você queira ver o valor total das vendas de cada produto no banco de dados, você pode tentar usar uma 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

Isso resulta em uma bela visualização de duas colunas mostrando o nome do produto com o valor total das vendas e funciona conforme o esperado. A consulta passou por todos os produtos na tabela orders_items, juntou-os aos registros na tabela de produtos e retornou o valor total de vendas de cada um.

RIGHT Joins

Usando o exemplo acima, observe o fato de que a consulta acima retornou apenas 19 registros, enquanto há um total de 22 produtos no banco de dados. Isso ocorre porque a consulta começou com a tabela orders_items e juntou-a à tabela de produtos e, como alguns produtos nunca foram pedidos, nenhum registro desses produtos existe na tabela orders_items.

O que acontece se você quiser obter uma lista de todos os produtos com valores de venda, incluindo produtos que não foram pedidos? Tente uma junção certa com a seguinte 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

Isso é melhor, e a consulta agora retorna os 22 produtos completos, com três deles tendo uma quantidade nula . Isso ocorre porque, em vez de usar orders_items como a tabela primária que se junta à tabela de produtos, a junção certa inverte o pedido e junta a tabela de produtos à tabela orders_items.

Várias associações em uma consulta

Às vezes, você precisa juntar três ou mais tabelas para obter um conjunto específico de resultados.

Por exemplo, talvez você queira uma lista de todos os clientes que compraram o micro-ondas (ID do produto # 1), incluindo seus nomes e data do pedido. Isso requer um SELECT em três tabelas, que pode ser feito usando duas junções com a seguinte 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 retorna todos os 426 pedidos do microondas e funciona conforme o esperado. Primeiro, ele faz a correspondência de todos os clientes com seus respectivos pedidos e, em seguida, faz outras consultas ao conjunto de resultados, combinando todos os pedidos apenas com aqueles na tabela orders_items que contêm o produto de micro-ondas (id # 1).

Nunca use subconsultas com cláusulas IN

Como uma nota rápida, a todo custo, você deve sempre evitar usar subconsultas em suas consultas SQL, como:

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

Consultas como as acima são muito ineficientes, usam um grande número de recursos e devem ser evitadas tanto quanto possível. Em vez disso, use junções adequadas conforme descrito nas seções acima. Por exemplo, a consulta acima deve ser reescrita 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;

Economize tempo com junções SQL

Esperamos que este artigo tenha ajudado a mostrar o poder dos bancos de dados relacionais, como MySQL, e como construir consultas SQL que recuperam registros de várias tabelas em uma consulta usando junções, permitindo que você recupere os resultados exatos desejados.

Você aprendeu três associações diferentes no SQL, como criar nomes de colunas e tabelas, usar várias associações em uma consulta e por que deve evitar subconsultas. Nunca mais tente compilar manualmente diferentes conjuntos de dados em um e comece a usar junções para impressionar seus colegas de trabalho e economizar tempo.