Inicialmente o Modelo de dados relacional prega a divisão dos dados em diversas entidades (tabelas), mas quando é necessário transformar esses dados em informações muitas vezes é necessário fazer a junção desses elementos, por isso o assunto Junção Tabelas SQL Joins é importante.
Modelo HR
O modelo HR reproduz um exemplo de um sistema de Recursos Humanos, dentro de um banco de dados ORACLE. Entretanto, o modelo tem a função de servir como um exemplo e possibilitar que usuários possam realizar o aprendizado e estudo de recursos relacionados a este banco de dados.
Contudo, este modelo é aplicado na forma de um schema, acessado através do usuário HR, facilitando assim, o acesso do usuário à esta base de dados de exemplo, que é usada em alguns dos cursos oficiais da ORACLE.
Primeiramente é importante entender como o modelo é composto, assim segue a explicação de cada tabela que o representa:
- EMPLOYEES: contém dados dos funcionários.
- DEPARTAMENTS: contém dados dos departamentos aos quais os funcionários trabalham.
- REGIONS: contém as regiões continentais mundiais.
- LOCATIONS: Contém os dados de localização dos escritórios de atuação.
- COUNTRIES: Contém os países.
- JOBS: Contém os cargos dos funcionários.
- JOB_HISTORY: Contém um histórico das trocas de cargos dos funcionários.
Assim, é importante verificar o modelo lógico, com a indicação de como as tabelas estão ligadas por suas chaves estrangeiras (foreign key):
Nas experimentações práticas deste artigo será utilizado o modelo HR.
Junção de Tabelas SQL Joins
O modelo relacional prega a divisão dos dados em diversas entidades, mas para extrair informações entre essas diversas entidades é necessário realizar a junção de tabelas, os Joins.
A linguagem SQL (Structured Query Language – SQL) é a forma como os dados são manipulados dentro dos bancos de dados relacionais.
O Instituto Americano Nacional de Padrões (ANSI) e a Organização Internacional de Padronização (ISO), são os responsáveis pela padronização da linguagem SQL, gerando assim uma linguagem padrão, mas isso não impede que os bancos de dados implementem dialetos proprietários.
Um dos principais motivos tem relação com a criação de funções que ainda não existiam dentro da linguagem padronizada.
O ORACLE é um dos bancos de dados que apresenta seu padrão e implementa também o padrão ANSI para a linguagem SQL.
Assim, para realizar essa junção neste artigo, serão descritos e praticados dentro do padrão ANSI.
Cross Join
Analogamente, é o processo que gera um produto cartesiano entre duas, ou mais tabela.
Ou seja, relaciona cada linha de registro da primeira tabela com a cada uma das linhas da segunda tabela, assim por diante, caso haja mais tabelas.
A aplicação abaixo demonstra um CROSS JOIN padrão ANSI:
SELECT DEPARTMENT_NAME, CITY
FROM HR.DEPARTMENTS
CROSS JOIN HR.LOCATIONS;
Aqui são relacionadas todas as linhas da tabela DEPARTMENTS com todas as linhas da tabela LOCATION, como foram retornadas as colunas DEPATMENT_NAME (nome do departamento) e a CITY (cidades das localizações), tem-se como resultado todos os departamentos com todas as cidades, ou todas as cidades com todos os departamentos.
A aplicação abaixo demonstra um CROSS JOIN sem utilizar JOIN:
SELECT DEPARTMENT_NAME, CITY
FROM HR.DEPARTMENTS, HR.LOCATIONS;
Enquanto que, para comparar a questão da diferença de desempenho, pode-se usar o recurso de plano de execução:
EXPLAIN PLAN FOR
SELECT DEPARTMENT_NAME, CITY
FROM HR.DEPARTMENTS
CROSS JOIN HR.LOCATIONS;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
Assim, pode-se comparar o plano de execução, tanto no método ANSI, com o uso de JOIN, quanto sem o seu uso:
EXPLAIN PLAN FOR
SELECT DEPARTMENT_NAME, CITY
FROM HR.DEPARTMENTS, HR.LOCATIONS;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
Como pode ser verificado os planos de execução são idênticos, demonstrando que não há diferença de desempenho.
Inner Join
Retorna os registros relacionados entre duas ou mais tabelas, apenas os registros relacionados.
Neste caso, uma dica importante é analisar quais tabelas serão necessárias para confeccionar a informação, exemplificando: gerar uma listagem com o primeiro nome do funcionário e o nome do seu cargo.
Olhando para o modelo, verificamos que o primeiro nome do funcionário (FIRST_NAME) está na tabela EMPLOYEES e o nome do cargo (JOB_TITLE) está na tabela JOBS. Agora é preciso identificar a constraint de FOREIGN KEY (FK) que liga as duas tabelas e por quais colunas está ligação é feita.
É uma ligação FK de 1 para n, de JOBS para EMPLOYEES (ou seja, um funcionário pode ter apenas um cargo, mas um cargo pode ser usado em vários funcionários), a coluna que faz a ligação é a JOB_ID, coluna que em JOBS é a chave primárias (primary key – PK) e em EMPLOYEES uma coluna que não permite nulo.
Assim, como não permite nulo sempre existirá valor para a ligação entre as duas tabelas pelo campo JOB_ID, pois a mesma é obrigatória.
Quando essa ligação é obrigatória o ideal é usar a cláusula INNER JOIN, pois ela retorna todas as linhas que estão relacionadas pela FK entre as duas tabelas, como é obrigatório o preenchimento do campo, todas as linhas serão relacionadas.
Padrão ANSI do INNER JOIN:
SELECT FIRST_NAME, JOB_TITLE
FROM HR.EMPLOYEES
INNER JOIN HR.JOBS
ON HR.EMPLOYEES.JOB_ID = HR.JOBS.JOB_ID;
Na construção de uma instrução utilizando INNER JOIN é necessário colocar uma das tabelas no FROM e a outra virá logo após o INNER JOIN, também é importante definir como as duas tabelas são ligadas na cláusula ON, que na verdade conterá a coluna de ligação entre as duas tabelas que devem estar igualadas.
Após a execução foram retornadas 107 linhas de registros, se não definir a cláusula ON tem-se um produto cartesiano, destacando a importância desta cláusula na aplicação dos JOINs.
A coluna JOB_ID existem nas duas tabelas com esse nome, dessa forma tem que identificar de que tabela a coluna pertence para que não haja colunas ambíguas (colunas com nomes iguais).
Quando usar INNER JOIN? sempre que a relação entre as duas tabelas for obrigatória.
Left Join / Left Outer Join
Traz os dados relacionados entre duas tabelas, mas também retorna os dados não relacionados da tabela à esquerda.
Tanto Left, quanto Right Join contém o Inner join, ou seja, na aplicação de ambos sempre teremos o mesmo retorno da aplicação de um Inner Join, mais as linhas da tabela ou a esquerda, ou a direita que não tem relacionamento de ligação.
Portanto, quando usar Left ou Right?
R: quando a coluna da Foreign Key na tabela de ligação não for not null, não for obrigatória. Assim, quando retornar os registros trará na listagem os que tem ligação e os que não tem ligação ou da tabela a esquerda, ou da tabela a direita.
Usando o mesmo relacionamento de exemplo do INNER JOIN e aplicarmos tanto o LEFT JOIN, quanto o RIGHT JOIN teremos o mesmo resultado, a mesma quantidade de linhas será retornada em qualquer uma das cláusula. Por quê? como é obrigatório o preenchimento do JOB_ID em EMPLOYEES, só temos funcionários com cargos definidos, não existirão não relacionados.
Quando usar LEFT JOIN ou RIGTH JOIN?
R: sempre que a relação entre as duas tabelas não for obrigatória. Aí usar LEFT se a tabela relacionada estiver à esquerda, ou RIGHT se a tabela estiver a direita.
A instrução abaixo retorna todos os países (COUNTRIES) que tem ligação com a tabela de localizações (LOCATIONS), que corresponderia ao INNER JOIN.
Mas, como foi aplicado um LEFT JOIN, além dessa linhas, também são retornadas as linhas da tabela a esquerda (por quê a esquerda? pois se colocar o LEFT JOIN na mesma linha do FROM, a esquerda corresponde a tabela HR.COUNTRIES).
Assim, teremos uma listagem que mostra onde tem escritórios e em que países não existem escritórios.
SELECT COUNTRY_NAME, CITY
FROM HR.COUNTRIES
LEFT JOIN HR.LOCATIONS
ON HR.COUNTRIES.COUNTRY_ID = HR.LOCATIONS.COUNTRY_ID;
Quando a linha do LEFT JOIN está em uma nova linha, considere a esquerda a tabela acima, pois se colocar na mesma linha será a tabela a esquerda.
Right Join / Right Outer Join
Traz os dados relacionados entre duas tabelas, mas também retorna os dados não relacionados da tabela à direita.
Pode-se então definir que o RIGHT JOIN é o LEFT JOIN apontando para a tabela a direita, retornando todos os registros relacionados, mais os registros não relacionados da tabela a direita. Ou seja, a tabela logo em seguida ao RIGHT JOIN.
A instrução abaixo, retorna uma listagem dos departamentos (DEPARTMENTS) e a cidade onde estão localizados (LOCATIONS), mais as linhas das cidades onde há escritórios e não está vinculado nenhum departamento a este local.
SELECT DEPARTMENT_NAME, CITY
FROM HR.DEPARTMENTS
RIGHT JOIN HR.LOCATIONS
ON HR.DEPARTMENTS.LOCATION_ID = HR.LOCATIONS.LOCATION_ID;
Full Join / Full Outer Join
Retorna os dados relacionados entre duas tabelas, mas também retorna os dados não relacionados de ambas as tabelas.
Dessa forma, o FULL JOIN pode ser comparado analogamente a aplicação do INNER JOIN + LEFT JOIN + RIGHT JOIN, assim retorna todas as linhas relacionadas, mais as linhas não relacionadas da tabela a esquerda e a tabela a direita.
SELECT DEPARTMENT_NAME, CITY
FROM HR.DEPARTMENTS
FULL JOIN HR.LOCATIONS
ON HR.DEPARTMENTS.LOCATION_ID = HR.LOCATIONS.LOCATION_ID;
A instrução acima retorna todas as linhas relacionadas e não relacionada de ambas as tabelas envolvidas.
Self Join
Faz o produto cartesiano de uma tabela ligada a ela mesma.
Natural Join
Faz o relacionamento das tabelas automaticamente desde que as colunas de ligação tenham o mesmo nome.
Conclusão: Junção Tabelas SQL Joins
É importante diferenciar, Junção interna (inner join) de Junção externa (outer join): junções externas podem ser divididas em: junções esquerdas (left outer joins), junções externas direitas (right outer joins) e junções externas completas (full outer joins).
A palavra-chave OUTER é opcional, no contexto das Junção externa.
Conclui-se que o aprendizado para aplicação de Junção de Tabelas SQL Joins é fundamental para quem quer trabalhar com o modelo de dados relacional, ou seja com banco de dados relacionais.
Por fim, é muito importante se especializar neste assunto, caso deseje trabalhar com banco de dados relacionais.
Clique aqui para acompanhar outros artigos relacionados a banco de dados.