DEV Community: Matheus Camilo

SQL - Joins (Tutorial Prático)

Matheus Camilo — Fri, 08 Dec 2023 01:57:01 +0000

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.

SQL - Joins

Matheus Camilo — Tue, 14 Nov 2023 01:19:02 +0000

1. Introdução

Em bancos de dados, a cláusula JOIN em SQL é um recurso fundamental que permite a combinação de informações provenientes de diferentes tabelas. Essa operação é essencial para unir colunas com base em valores comuns, proporcionando a criação de conjuntos de dados mais abrangentes e significativos.

O SQL padrão ANSI define cinco tipos principais de JOIN - INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN e CROSS JOIN - cada um desempenhando um papel específico na obtenção de dados conforme as necessidades da consulta. Em um cenário onde os dados são distribuídos em várias tabelas lógicas, o uso eficiente de JOINS é crucial para garantir a integridade e a abrangência das análises realizadas em bancos de dados relacionais. Essa capacidade de unir dados de maneira coesa e eficiente é fundamental para explorar toda a potencialidade dos sistemas de gerenciamento de banco de dados.

2. Desenvolvimento

1. Inner Join

1.1 Definição

O INNER JOIN (ou só apenas join) é uma cláusula utilizada para unir registros que possuem correspondentes em ambas tabelas. Caso formos representar utilizando conceitos de conjutos, o resultado desta junção é a intersecção entre a Tabela A e B. Gera como resultado uma linha da Tabela A para cada registro correspondente da tabela B.

1.2 Exemplo

SELECT * FROM tabela_a
INNER JOIN tabela_b 
    ON tabela_a.id = tabela_b.tabela_a_id;

2. Left Join

2.1 Definição

O LEFT JOIN é uma cláusula utilizada para unir registros de tabelas, trazendo todos os registros da tabela A e os seus correspondentes da tabela B. Em um registro da tabela A que não haja correspondente da tabela B, os campos da tabela B unidos a este registro aparecem vazios.

2.2 Exemplo

SELECT * FROM tabela_a
LEFT JOIN tabela_b 
    ON tabela_a.id = tabela_b.tabela_a_id;

3. Right Join

3.1 Definição

O RIGHT JOIN é uma cláusula utilizada para unir registros de tabelas, trazendo todos os registros da tabela B e os seus correspondentes da tabela A. Em um registro da tabela B que não haja correspondente da tabela A, os campos da tabela A deste registro aparecem vazios. Ele é praticamente o inverso do LEFT JOIN

3.2 Exemplo

SELECT * FROM tabela_a
RIGHT JOIN tabela_b 
    ON tabela_a.id = tabela_b.tabela_a_id;

4. Full Join

4.1 Definição

O FULL JOIN é uma cláusula utilizada para unir registros de tabelas, tanto os correspondentes quanto os que não possuem correspondência alguma de ambas as tabelas. O resultado dessa junção é o resultado do LEFT JOIN + RIGHT JOIN.

4.2 Exemplo

SELECT * FROM tabela_a
FULL JOIN tabela_b 
    ON tabela_a.id = tabela_b.tabela_a_id;

5. Cross Join

5.1 Definição

O CROSS JOIN é uma cláusula utilizada para gerar o produto escalar entre as tabelas, ou seja, juntando cada registro da tabela A com cada registro da tabela B. Esta operção tem como resultado todas as probabilidades de junção de registros entre as duas tabelas. Por fim, a quantidade de linhas resultante é dada pela quantidade de linhas da tabela A vezes a quantidade de linhas da tabela B.

5.2 Exemplo

SELECT * FROM tabela_a
CROSS JOIN tabela_b;

Vantagens e Desvantagens de se utilizar os JOINs

Vantagens

Funciona muito bem com tabelas normalizadas
Simplificar consultas complexas entre várias tabelas relacionadas
Melhora no Desempenho caso utilizado de forma correta no cenário adequado

Desvantagens

É mais fácil consultar as informações necessárias em apenas em uma tabela em vez de obter dados de terceiras
Não é recomendado para tabelas desnormalizadas
Quanto mais tabelas envolvidas, maior é o impacto em performance da consulta

Exemplos de aplicações no mercado:

3. Conclusão

O JOIN no SQL é uma cláusula importantíssima para manipular dados, fazer a relação entre tabelas e permitir filtragem para entendimento e controle. Assim, é uma forma de gerenciar a complexidade de bancos de dados grandes e gerar insights precisos sobre eles.

Os diversos tipos de JOINS fornecem uma maneira poderosa de combinar dados de várias tabelas. O INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN e CROSS JOIN são úteis em diferentes situações, permitindo manipulação e análise eficaz de dados complexos. Dominar esses tipos de JOINS amplia suas habilidades em SQL, tornando suas consultas mais eficientes e precisas.

Referências

Imagens dos joins: https://blog.betrybe.com/sql/sql-join/
Imagens das aplicações no mercado: https://www.linkedin.com/pulse/entendendo-e-utilizando-os-tipos-de-joins-em-sql-um-guia-sousa/?originalSubdomain=pt