SQL: JOIN Complexos
Sabemos que no dia a dia de um desenvolvedor a complexidade das consultas SQL podem variar de acordo com o cenário.
Sabemos que no dia a dia de um desenvolvedor a complexidade das consultas SQL podem variar de acordo com o cenário. Pensando por este lado, trouxemos nestes tópicos alguns exemplos do dia a dia que podem lhe ajudar a confeccionar suas queries utilizando o melhor do SQL.
Na prática
Exemplo 1
Considere a tabela tópicos (Tabela 1) onde armazenamos tópicos de um determinado fórum e, em seguida, a tabela likes (Tabela 2) onde armazenamos as curtidas que cada tópico teve.
| id_topico | titulo | data_cadastro |
| 1 | Como utilizar JOINs com três tabelas | 2016-04-18 12:39:10 |
| 2 | Por que a minha query não funciona no MySQL? | 2016-04-20 12:39:10 |
| 3 | Erro ao conectar com o Firebird | 2016-07-04 12:39:10 |
| 4 | Usando TOP no POSTGRES | 2016-07-23 12:39:10 |
| 5 | Consulta não gera os resultados como esperado | 2016-10-17 12:39:10 |
| 6 | Listar apenas os dados duplicados no banco de dados | 2016-10-18 12:39:10 |
| 7 | Listar media de preco na tabela | 2017-07-21 00:00:00 |
Tabela 1. Tabela topicos
| id_like | id_topico | data_criacao | id_usuario |
| 1 | 1 | 2016-07-17 12:41:40 | 1234 |
| 2 | 1 | 2016-07-17 12:41:40 | 432 |
| 3 | 1 | 2016-08-19 12:41:40 | 654 |
| 4 | 3 | 2016-08-22 12:41:40 | 5688 |
| 5 | 3 | 2017-08-24 12:41:40 | 87 |
| 6 | 3 | 2017-12-18 12:41:40 | 5443 |
| 7 | 3 | 2018-01-21 12:41:40 | 654 |
| 8 | 3 | 2018-04-22 12:41:40 | 432 |
| 9 | 2 | 2018-07-17 12:41:40 | 5643 |
| 10 | 2 | 2018-08-16 12:41:40 | 5456 |
| 11 | 7 | 2017-12-12 00:00:00 | 5211 |
| 12 | 4 | 2017-12-11 00:00:00 | 5211 |
| 13 | 4 | 2017-11-10 00:00:00 | 5456 |
Tabela 2. Tabela likes
Desejamos listar todos os tópicos que tiveram mais do que duas curtidas durante o mês de julho. Para isso, podemos utilizar a query abaixo:
SELECT
t.titulo, count(l.id_like) as total
FROM
likes l, topico t
WHERE
l.id_topico = t.id_topico
AND
month(l.data_criacao) = 7
GROUP BY
l.id_topico
HAVING
count(l.id_like) >= 2 Conforme podemos ver acima, a consulta utiliza a função agregadora count para retornar o total de likes, tendo em vista que esta função é feita na coluna l.id_like, cujo apelido l pertence a tabela likes.
Em seguida, na linha 4 fazemos o SELECT a partir de duas tabelas, definindo um apelido para cada uma delas, l para tabela likes e t para tabela tópico.
Na linha 6 definimos como condição para o filtro o identificador do tópico (id_topico) ser idêntico nas duas tabelas.
Perceba que na linha 7 utilizamos o operador AND para criar uma segunda condição que é o mês ser igual a 7 (julho). Em seguida agrupamos os resultados utilizando o id do topico (id_topico). Esse agrupamento é feito, pois precisamos de apenas um resultado por tópico, ou seja, precisamos agrupar todas as ocorrências em uma só.
Por fim utilizamos um último filtro na consulta com o HAVING, que é o número de likes ser maior ou igual a 2. Observe o resultado na Tabela 3.
| titulo | total |
| Como utilizar JOINs com três tabelas | 2 |
Tabela 3. Resultado da busca pelo tópico de mais curtidas
no mês 7
Exemplo 2
Ainda utilizando a mesma estrutura do exemplo anterior (Tabela 1 e Tabela 2), partimos para um segundo exemplo. Suponha que precisamos retornar os três dias em que tivemos mais curtidas no mês de julho. Para isso, podemos utilizar a query abaixo e obtemos como resultado a Tabela 4:
SELECT
DAY(l.data_criacao) as Dia, count(l.id_like) as total
FROM
likes l, topico t
WHERE
l.id_topico = t.id_topico
AND
month(l.data_criacao) = 7
GROUP BY
Dia
ORDER BY total DESC
LIMIT 0, 3; | dia | total |
| 17 | 3 |
| 10 | 1 |
Tabela 4. Resultado da consulta dos dias com mais
curtidas no mês de julho
Na consulta acima, utilizamos o SELECT para recuperar duas colunas: a primeira com o dia da criação, definindo o apelido DIA, e o total de curtidas, verificando pelo total de ocorrências da coluna l.id_like, através da função de agregação COUNT, onde também definimos um apelido como total entre as linhas 1 e 2.
Em seguida utilizamos o FROM para fazer a seleção a partir de duas tabelas: likes, que usaremos o apelido l e topico com o apelido t entre as linhas 3 e 4.
Em seguida definimos duas condições para filtrar os resultados usando as cláusulas WHERE: a primeira era as tabelas like e topico possuírem o mesmo id_topico e, a data_criaçao ter o mês igual a 7 (julho) entre as linhas 5 e 8.
Em seguida agrupamos os resultados com o GROUP BY por dia e, ordenamos os resultados com o ORDER BY por total em ordem decrescente limitando os resultados a, no máximo, três itens entre as linhas 9 e 12.
Exemplo 3
Podemos aproveitar mais o cenário em outro exemplo. Suponha que seja necessário agora trazer os três tópicos que tiveram mais curtidas ainda no mês de julho. Para isso, podemos utilizar a query abaixo e que resulta na Tabela 5:
SELECT
t.titulo, count(l.id_like) as total
FROM
likes l, topico t
WHERE
l.id_topico = t.id_topico
AND
month(l.data_criacao) = 7
GROUP BY
l.id_topico
ORDER BY total DESC
LIMIT 0, 3 | titulo | total |
| Como utilizar JOINs com três tabelas | 2 |
| Por que a minha query não funciona no MySQL? | 1 |
| Usando TOP no POSTGRES | 1 |
Tabela 5. Resultado da consulta
Na consulta acima utilizamos o SELECT para listar os dados das tabelas likes (usando o ALIAS l) e topico (usando o ALIAS t) na linha 4, pedindo as colunas de título da tabela topico e o total usando a função agregadora COUNT de likes na linha 2.
Em seguida utilizamos a cláusula WHERE para definir os filtros de busca, verificando se o id do tópico é igual em ambas as tabelas e, se o mês da criação igual a 7 (julho) entre as linhas 6 e 8.
Para organizar os resultados utilizamos o GROUP BY para agrupar os resultados pelo id_tipo e, em seguida, na linha 10 ordenamos os dados com o ORDER BY.
Para limitar os resultados da query a três linhas utilizamos na linha 12 o LIMIT, começando do registro 0 e parando no 3.
Exemplo 4
Imagine então um outro cenário, onde temos uma tabela vendas (Tabela 6), e uma tabela onde ficam as parcelas, chamada vendas_parcelas, referentes às vendas feitas (Tabela 7).
| ID | ID_CLIENTE | DATA | VALOR |
| 1 | 1 | 2018-06-07 | 1200.00 |
| 2 | 5 | 2018-05-17 | 800.00 |
| 3 | 3 | 2018-07-17 | 620.00 |
| 4 | 1 | 2018-05-18 | 900.00 |
Tabela 6. Tabela vendas
| ID | ID_VENDA | NUMERO_PARCELA | VALOR_PARCELA | VENCIMENTO | DATA_PAGAMENTO |
| 1 | 1 | 1 | 600.00 | 2018-06-10 | NULL |
| 2 | 1 | 2 | 600.00 | 2018-07-10 | NULL |
| 3 | 2 | 1 | 200.00 | 2018-05-10 | 2018-05-10 |
| 4 | 2 | 2 | 200.00 | 2018-06-10 | 2018-06-10 |
| 5 | 2 | 3 | 200.00 | 2018-07-10 | 2018-07-10 |
| 6 | 2 | 4 | 200.00 | 2018-08-10 | NULL |
| 7 | 3 | 1 | 310.00 | 2018-07-10 | 2018-07-10 |
| 8 | 3 | 2 | 310.00 | 2018-08-10 | 2018-07-10 |
| 9 | 4 | 1 | 300.00 | 2018-05-20 | 2018-07-17 |
| 10 | 4 | 2 | 300.00 | 2018-06-20 | 2018-07-17 |
| 11 | 4 | 3 | 300.00 | 2018-07-20 | 2018-07-17 |
Tabela 7. Tabela vendas_parcelas
Com essa estrutura, suponhamos que desejamos fazer uma busca de todas as vendas que tenham todas as parcelas pagas, ou seja, todas as vendas concluídas. Para isso, podemos utilizar a query que tem com resultado a Tabela 8.
SELECT
V.ID,
V.DATA,
V.VALOR
FROM
VENDAS V
WHERE
ID NOT IN
(
SELECT
P.ID_VENDA
FROM
VENDAS_PARCELAS P
WHERE
P.ID_VENDA = V.ID
AND
P.DATA_PAGAMENTO IS NULL
)
AND MONTH(V.DATA) = 5 | ID | DATA | VALOR |
| 4 | 2018-05-18 | 900.00 |
Tabela 8. Resultado da consulta de todas as vendas já
pagas, sem parcelas em aberto
Na consulta acima, das linhas 1 a 4 selecionamos os campos ID, DATA e VALOR, definindo um apelido para a tabela, como V.
Em seguida, na linha 6, definimos qual a tabela principal de consulta dos dados para VENDAS, e informamos que o seu apelido será V, para facilitar a legibilidade da query.
Na linha 8 definimos a primeira condição da query através do NOT IN, precisamos que o ID da venda não esteja mencionado no resultado de outro select, que é feio na linha posterior.
Na linha 9 fazemos o segundo select da query, buscando na tabela VENDA_PARCELA todas as vendas que tenha a DATA_PAGAMENTO como NULL, ou seja, alguma parcela em aberto e que tenham o ID_VENDA igual ao ID da tabela VENDA, para fazer o vínculo entre as duas tabelas.
Na linha 18 realizamos a segunda condição da query, que é o mês da venda ser igual a maio (5).
Exemplo 5
Pensando em uma abordagem um pouco mais complexa, suponha que nessa mesma estrutura de tabelas, desejamos realizar uma consulta para verificar o pagamento de comissão de nossos vendedores. A comissão só é pega se a primeira parcela da venda for paga, sendo assim só pode vir nessa consulta as vendas que tiverem a primeira parcela dada como paga. Para isso, podemos utilizar a query abaixo:
SELECT
V.ID,
V.VALOR,
P.DATA_PAGAMENTO,
P.NUMERO_PARCELA,
(V.VALOR * 0.10) as VALOR_COMISSAO
FROM VENDAS V, VENDAS_PARCELAS P
WHERE (V.ID = P.ID_VENDA)
AND (P.NUMERO_PARCELA = 1)
AND (P.DATA_PAGAMENTO IS NOT NULL); No trecho de código acima, entre as linhas 1 a 6 selecionamos as colunas que virão no resultado da consulta. Observe que criamos uma coluna chamada VALOR_COMISSAO que será o valor da conta referente a 10% do valor da venda.
Na linha 7 selecionamos as tabelas que serão utilizadas na consulta, VENDAS com o apelido V, e VENDAS_PARCELAS com o apelido P. Em seguida, na linha 8 realizamos uma condicional para garantir que o ID da venda será o mesmo nas duas tabelas.
Nas linhas 9 e 10 é onde definimos que o número da parcela deve ser 1 (primeira parcela) e que a data de pagamento não deve ser nula, ou seja, estar paga.
Confira também
Artigos relacionados
-
Artigo
-
Artigo
-
Artigo
-
Artigo
-
Artigo