Olá mundo!
Estamos aqui com a segunda parte do nosso artigo sobre banco de dados, mais especificamente sobre os JOINs. No artigo anterior falamos sobre a sua sintaxe, aplicações, vantagens e desvantagens.
Caso não tenha acompanhado o referido artigo, aqui está o link: SQL - Joins
Mas alguns podem pensar: "Certo, aprendi a utilizar os JOINs. Mas onde e como posso usá-los em exemplos mais próximos da realidade?"
Existem diversar aplicabilidades destes no mercado de trabalho de cada um dos JOINs, mas trouxemos 5 estudos de caso, para que possamos juntos ver aplicação de cada um.
"Chega de conversa, agora é hora de por a mão na massa!"
1. INNER JOIN
Vamos criar um estudo de caso fictício para demonstrar o uso do INNER JOIN em um cenário prático.
1.1. Estudo de Caso:
Sistema de Gerenciamento de Vendas
Suponha que você está desenvolvendo um sistema de gerenciamento de vendas para uma loja online que vende produtos eletrônicos. Você tem duas tabelas principais em seu banco de dados: clientes e pedidos.
Tabela "clientes":
CREATE TABLE clientes (
cliente_id INT PRIMARY KEY,
nome_cliente VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO clientes VALUES
(1, 'Ana Silva', 'ana@email.com'),
(2, 'Carlos Oliveira', 'carlos@email.com'),
(3, 'Mariana Santos', 'mariana@email.com');
A tabela "clientes" contém informações sobre os clientes, incluindo um identificador único (cliente_id), o nome do cliente (nome_cliente), e o endereço de e-mail (email).
Tabela "pedidos":
CREATE TABLE pedidos (
pedido_id INT PRIMARY KEY,
cliente_id INT,
produto VARCHAR(50),
quantidade INT,
total DECIMAL(10, 2)
);
INSERT INTO pedidos VALUES
(101, 1, 'Smartphone', 2, 1200.00),
(102, 2, 'Notebook', 1, 1500.00),
(103, 1, 'Fones de Ouvido', 1, 50.00),
(104, 3, 'Tablet', 3, 900.00);
A tabela "pedidos" contém informações sobre os pedidos feitos pelos clientes, incluindo um identificador único (pedido_id), o identificador do cliente que fez o pedido (cliente_id), o produto adquirido (produto), a quantidade adquirida (quantidade), e o total do pedido (total).
1.2. Consulta com INNER JOIN para obter detalhes de pedidos e clientes:
Agora, suponha que você queira obter uma lista de todos os pedidos, juntamente com os detalhes do cliente que fez cada pedido. Você pode usar INNER JOIN para combinar as informações das tabelas "pedidos" e "clientes" com base no identificador do cliente (cliente_id).
SELECT
pedidos.pedido_id,
clientes.nome_cliente,
pedidos.produto,
pedidos.quantidade,
pedidos.total
FROM
pedidos
INNER JOIN
clientes ON pedidos.cliente_id = clientes.cliente_id;
Esta consulta retorna uma lista que inclui o ID do pedido, o nome do cliente, o produto adquirido, a quantidade e o total do pedido. O INNER JOIN garante que apenas os pedidos associados a clientes existentes na tabela "clientes" sejam incluídos no resultado.
Esse é um exemplo simples de como o INNER JOIN pode ser usado para combinar informações de diferentes tabelas com base em chaves relacionadas, proporcionando uma visão abrangente dos dados em um sistema de gerenciamento de vendas.
2. LEFT JOIN
2.1. Estudo de Caso: Sistema de Gerenciamento de Funcionários e Departamentos
Suponha que você esteja desenvolvendo um sistema de gerenciamento de recursos humanos para uma empresa. Você tem duas tabelas principais em seu banco de dados: funcionários e departamentos.
Tabela "funcionarios":
CREATE TABLE funcionarios (
funcionario_id INT PRIMARY KEY,
nome_funcionario VARCHAR(50),
cargo VARCHAR(50),
departamento_id INT
);
INSERT INTO funcionarios VALUES
(1, 'João Silva', 'Desenvolvedor', 1),
(2, 'Ana Oliveira', 'Designer', 2),
(3, 'Carlos Santos', 'Analista de Negócios', NULL),
(4, 'Mariana Lima', 'Desenvolvedor', 1);
A tabela "funcionarios" contém informações sobre os funcionários, incluindo um identificador único (funcionario_id), o nome do funcionário (nome_funcionario), o cargo (cargo), e o identificador do departamento em que trabalham (departamento_id). Note que alguns funcionários podem não ter um departamento atribuído.
Tabela "departamentos":
CREATE TABLE departamentos (
departamento_id INT PRIMARY KEY,
nome_departamento VARCHAR(50)
);
INSERT INTO departamentos VALUES
(1, 'TI'),
(2, 'Design'),
(3, 'Recursos Humanos');
A tabela "departamentos" contém informações sobre os departamentos da empresa, incluindo um identificador único (departamento_id) e o nome do departamento (nome_departamento).
2.2. Consulta com LEFT JOIN para obter lista de funcionários com ou sem departamento:
Agora, suponha que você queira obter uma lista de todos os funcionários, incluindo aqueles que não têm um departamento atribuído. Você pode usar LEFT JOIN para garantir que todos os funcionários sejam incluídos na lista, mesmo que não haja uma correspondência no departamento.
SELECT
funcionarios.funcionario_id,
funcionarios.nome_funcionario,
funcionarios.cargo,
departamentos.nome_departamento
FROM
funcionarios
LEFT JOIN
departamentos ON funcionarios.departamento_id = departamentos.departamento_id;
Esta consulta retorna uma lista que inclui o ID do funcionário, o nome do funcionário, o cargo e o nome do departamento em que trabalham. Se um funcionário não tiver um departamento atribuído (por exemplo, Carlos Santos), o resultado mostrará o nome do departamento como NULL.
O LEFT JOIN é útil quando você deseja incluir todos os registros da tabela à esquerda (tabela "funcionarios" neste caso), mesmo que não haja correspondência na tabela à direita (tabela "departamentos" neste caso). Isso é especialmente útil quando você deseja incluir informações mesmo quando alguns dados relacionados estão ausentes.
3. RIGHT JOIN
3.1. Estudo de Caso: Sistema de Gerenciamento Hospitalar
Suponha que você esteja desenvolvendo um sistema de gerenciamento hospitalar. Você tem duas tabelas principais em seu banco de dados: pacientes e consultas.
Tabela "pacientes":
CREATE TABLE pacientes (
paciente_id INT PRIMARY KEY,
nome_paciente VARCHAR(50),
idade INT,
medico_id INT
);
INSERT INTO pacientes VALUES
(1, 'Maria Silva', 35, 101),
(2, 'José Oliveira', 45, 102),
(3, 'Ana Santos', 28, NULL),
(4, 'Carlos Lima', 50, 101);
A tabela "pacientes" contém informações sobre os pacientes, incluindo um identificador único (paciente_id), o nome do paciente (nome_paciente), a idade (idade), e o identificador do médico responsável (medico_id). Note que alguns pacientes podem não ter um médico atribuído.
Tabela "consultas":
CREATE TABLE consultas (
consulta_id INT PRIMARY KEY,
medico_id INT,
data_consulta DATE,
descricao VARCHAR(200)
);
INSERT INTO consultas VALUES
(201, 101, '2023-01-15', 'Consulta de rotina'),
(202, 102, '2023-02-05', 'Avaliação cardíaca'),
(203, 101, '2023-03-20', 'Exames de sangue'),
(204, 103, '2023-04-10', 'Consulta de acompanhamento');
A tabela "consultas" contém informações sobre as consultas realizadas, incluindo um identificador único (consulta_id), o identificador do médico que realizou a consulta (medico_id), a data da consulta (data_consulta), e uma descrição da consulta (descricao).
3.2. Consulta com RIGHT JOIN para obter lista de consultas com ou sem pacientes:
Agora, suponha que você queira obter uma lista de todas as consultas, incluindo aquelas que não têm um paciente associado. Você pode usar RIGHT JOIN para garantir que todas as consultas sejam incluídas na lista, mesmo que não haja correspondência na tabela "pacientes".
SELECT
consultas.consulta_id,
pacientes.nome_paciente,
pacientes.idade,
consultas.data_consulta,
consultas.descricao
FROM
consultas
RIGHT JOIN
pacientes ON consultas.medico_id = pacientes.medico_id;
Esta consulta retorna uma lista que inclui o ID da consulta, o nome do paciente, a idade do paciente, a data da consulta e a descrição da consulta. Se uma consulta não tiver um paciente associado (por exemplo, consulta realizada por um médico que não está na tabela "pacientes"), o resultado mostrará o nome do paciente como NULL.
O RIGHT JOIN é útil quando você deseja incluir todos os registros da tabela à direita (tabela "pacientes" neste caso), mesmo que não haja correspondência na tabela à esquerda (tabela "consultas" neste caso). Isso é especialmente útil quando você deseja incluir informações mesmo quando alguns dados relacionados estão ausentes.
4. Full Join
4.1. Estudo de Caso: Empresa de Serviços de Streaming
Inicialmente, em uma empresa de serviços de streaming, várias pessoas assinavam vários planos sem precisar se cadastrar no site da empresa para selecionar a plataforma de streaming, apenas com seu cpf e senha.
Uma pessoa pode assinar vários planos de uma só vez com seus dados.
Todavia, um tempo se passou, e as políticas de assinatura daquela empresa mudaram, agora fornecendo a possibilidade de cadastro aos novos assinantes em sua base de dados, obtendo assim mais benefícios nas plataformas.
Com base nos dados, referente á usuários cadastrados e planos escolhidos, a empresa deseja obter informações sobre os planos assinados por usuários cadastrados ou não no sistema, assim como clientes cadastrados que cancelaram algum plano. E assim com esses dados, planejar uma melhor abordagem de marketing.
4.2. Informações técnicas
Os dados dos clientes está na tabela cliente e os dados dos planos assinados está na tabela plano.
A tabela cliente tem os campos:
- Nome
- CPF (Que é a chave primária da tabela)
- Data de Criação
A tabela plano tem os campos:
- Nome
- Valor
- Data de Assinatura
- CPF Cliente(Que é a chave estrangeira da tabela
cliente)
4.3. Resolução
Nessa situação, a melhor abordagem a ser utilizada utilizando os recursos de SQL, é utilizar a cláusula de junção FULL JOIN. Para assim obter todos os dados de todas as tabelas, tanto os relacionados como os que não estão.
SELECT * from cliente
FULL JOIN plano
ON cliente.cpf = plano.cpf_cliente;
Aplicando no caso de uso, será possível obter:
- Os cpf de clientes que não possuem cadastro por meio dos seus plano
- Os clientes que cancelaram seus planos
- Os cliente cadastrados com seus planos assinados
5. Cross Join
5.1. Estudo de Caso
Um cientista, em seu laboratório, precisa realizar vários experimentos com vários químicos registrados em suas base de dados.
Ele precisa realizar a mistura de todos os químicos da tabela com os químicos com os mesmos desta tabela. Para isso, ele necessita de uma lista com a combinação de todos os químicos.
Portanto, se na base dados ele possue 3 registros, então ele terá ao final uma lista com 9 registros.
5.2. Informações técnicas
Os dados dos químicos estão na tabela quimico.
A tabela quimico tem os campos:
- Nome
- Identificação do químico (Que é a chave primária)
5.3. Resolução
Nessa situação, a melhor abordagem a ser utilizada utilizando os recursos de SQL, é utilizar a cláusula de junção CROSS JOIN. Essa cláusula é raramente utilizada, por sua aplicação para geração do produto cartesiano para cenários específicos.
SELECT * from quimico
CROSS JOIN quimico;
Aplicando no caso de uso, será possível obter:
- A lista com cada quimico com seu "acompanhante" na mistura, considerando a ordem também como fator importante
Conclusão
Antes de criar uma consulta utilizando as cláusulas JOIN, é necessário ter uma compreensão da modelagem do banco de dados para entender como as tabelas se relacionam e identificar suas dependências. Essa compreensão é essencial para escolher o tipo adequado de JOIN em uma consulta, alinhando-se aos dados desejados.
Ao longo destes exemplos citados acima, entendemos a importância de compreender os relacionamentos entre tabelas para a escolha eficaz do JOIN. Além disso, destacamos a notável versatilidade do conjunto de cláusulas SQL JOIN e suas variações. Essa versatilidade oferece a capacidade de criar consultas complexas, combinando dados de maneiras diversas, sem a necessidade de um código extenso.
Top comments (1)
Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...