Treinamento para Consultas com SQL Server – Parte 2 – JOINS (introdução)

em

Conceitos básicos

Um banco de dados normalmente possui várias tabelas para garantir a integridade dos dados e evitar a redundância. O modelo formal de organização dos dados de forma a evitar que porções da mesma informação estejam repetidas em vários lugares chama-se modelo relacional.
Neste modelo através de várias tabelas que possuem relacionamentos entre si é assegurado que uma informação esteja em apenas um lugar e que as operações de atualização sobre estes dados sejam atômicas, ou seja, executadas apenas em um lugar e propagadas para todos os demais locais onde a informação é necessária de forma automática, sem a necessidade de intervenção do código da aplicação.
No SQL Server usando a ferramenta SQL Server Management Studio (SSMS) você consegue rapidamente verificar quais os relacionamentos de uma tabela através da janela Pesquisador de Objetos (ou Objects Explorer se estiver em inglês). Para isso basta selecionar uma tabela e clicar com o botão direito do mouse escolhendo o comando “Exibir dependências”. A figura a seguir ilustra esta funcionalidade.
Este comando exibe uma janela que mostra tanto as tabelas das quais a que foi selecionada depende, ou seja, das quais importa chaves estrangeiras, mas também, as tabelas que importam sua chave primária e são dependentes desta.
Logo, a partir disto fica mais fácil tanto saber quais as tabelas devem ser consultadas para obter os dados das tabelas filhas.
Os relacionamentos são tratados nas consultas de seleção através da cláusula JOIN. Vamos tratar de três tipos básicos de utilização desta cláusula que são os mais comuns:
  1. INNER JOIN
  2. LEFT OUTER JOIN
  3. CROSS JOIN
  4. SELF JOIN

Usando INNER JOIN

Existe muita documentação sobre o assunto, a referência do banco de dados pode ser consultada aqui. Basicamente, o que interessa mesmo é que você deve usar INNER JOIN quando quiser trazer apenas as linhas de uma tabela que também estejam em outra. Caso na tabela na qual se fez a junção não existam registros relacionados com a tabela de origem, nenhum registro será listado.
Este é o tipo mais comum de utilização embora, não seja interessante em casos onde se deseja listar os registros da primeira, independentemente de existirem registros em uma tabela filha.
A sintaxe básica é a seguinte:

SELECT
FROM TabelaOriginal
JOIN TabelaRelacionada1 ON TabelaRelacionada1.ChaveEstrangeira = 
   TabelaOriginal.ChavePrimária
JOIN TabelaRelacionada2 ON TabelaRelacionada2.ChaveEstrangeira = 
   TabelaOriginal.ChavePrimária
   (ou TabelaRelacionada1.ChavePrimária)
...
JOIN TabelaRelacionada ON TabelaRelacionada.ChaveEstrangeira =
     TabelaOriginal.ChavePrimária 
     (ou TabelaRelacionada=1]>.ChavePrimária


 A sintaxe será melhor compreendida após analisar os exemplos seguintes.

Lembretes:

  • As cláusulas JOIN devem vir sempre na sequência da cláusula FROM.
  • Recomenda-se que as tabelas relacionadas sejam colocadas na sequência, ou seja, logo abaixo da tabela de onde irá importar a chave estrangeira.
  • Os operadores para fazer o relacionamento são os mesmos da cláusula WHERE.
  • Embora no exemplo a tabela de junção esteja do lado esquerdo da cláusula, a ordem não é importante para a execução, embora, facilite a compreensão de quem for fazer a leitura

Exemplos

Considere duas tabelas do banco AdventureWorks
  1. PERSON.PERSON – Dados de pessoas que no banco podem ser empresas, funcionários, etc.
  2. HUMANRESOURCES.EMPLOOYEE – contém dados referentes ao registro da pessoa como empregado da empresa.
As duas tem os campos conforme demonstrado no diagrama da próxima figura, o objetivo é trazer os campos:
  1. Tabela PERSON.PERSON: FirstName e LastName unindo e transformando em uma coluna nomeada como CompleteName
  2. Tabela HUMANRESOURCES.EMPLOYEE:
  3. Campo JobTitle (Título do cargo)
  4. BirthDate (Data de nascimento)
Somente devem ser exibidos nomes de pessoas que estejam na tabela HUMANRESOURCES.EMPLOYEE. É neste ponto que diferenciamos quando usar o INNER JOIN ou LEFT, pois, queremos omitir nome das pessoas que não sejam empregados. A coluna que serve como chave de ligação entre as duas tabelas é a coluna BusinessEntityID que é a chave primária para ambas.
A consulta para realizar esta operação é a seguinte:

select pp.FirstName + ' ' + pp.LastName as CompleteName
   ,HRE.JobTitle
   ,HRE.BirthDate
from person.person as pp
join HumanResources.Employee HRE on
   hre.BusinessEntityID = pp.BusinessEntityID

Esta consulta traz os seguintes resultados, exibidos parcialmente abaixo:

Observe que foi omitido a cláusula INNER pois fica implícita. Logo, se não for informado, o interpretador de consultas do SQL Server interpreta como sendo desta forma.
Na instrução SELECT que os campos FirstName e LastName foram concatenados usando o sinal “+”.  A concatenação desta forma só é válida para tipos de colunas texto (varchar, nvarchar, char). Para colunas de tipos diferentes, é necessário converter para texto primeiro. As funções de conversão serão tratadas em tópico próprio. Não é possível também, concatenar colunas de tipos diferentes.
Após a concatenação o conteúdo das colunas FirstName e LastName gerou uma coluna que foi nomeada como CompleteName. Para criar aliases de colunas e tabelas usa-se a instrução “as” que também pode ser omitida.
Foram usados aliases (apelidos) também para as tabelas, PERSON.PERSON ficou com pp e HUMANRESOURCES.EMPLOYEE com HRE. Esta prática não causa nenhum impacto no desempenho e facilita muito a compreensão da instrução deixando o código mais compacto.
Avançando no uso de INNER JOIN, É possível usar as suas cláusulas para filtrar os resultados da tabela PERSON.PERSON para listar apenas registros que estejam na tabela HUMANRESOURCES.EMPLOYEE.
Isto deixa a consulta um pouco diferente, mas, o seu funcionamento não foi alterado. Veja o exemplo abaixo:

select pp.FirstName + ' ' + pp.LastName as CompleteName
from person.person as pp
join HumanResources.Employee HRE on hre.BusinessEntityID = 
   pp.BusinessEntityID 

O resultado da consulta será semelhante ao da anterior listando apenas o nome completo.  Analise a instrução e observe que nenhuma coluna da tabela HUMANRESOURCES.EMPLOYEE foi incluída no resultado, embora, o filtro tenha sido aplicado e somente foram listadas pessoas que possuem registros nesta tabela. Este tipo de filtro não é muito comum, mas, diminuiu o tamanho da consulta e facilita a sua manutenção.
É possível ainda usar a cláusula JOIN para fazer outros tipos de filtros que seriam normalmente feitos na cláusula WHERE. Por exemplo, para listar apenas os nomes dos empregados que tem o cargo “Application Specialist” a consulta precisa verificar na tabela HUMANRESOURCES.EMPLOYEE e fazer o filtro. Usando este filtro, a instrução fica desta forma:

select pp.FirstName + ' ' + pp.LastName as CompleteName
   ,HRE.JobTitle
   ,HRE.BirthDate
from person.person as pp
join HumanResources.Employee HRE on hre.BusinessEntityID = 
   pp.BusinessEntityID
   and hre.JobTitle = 'Application Specialist'


No destaque está a condição que foi acrescentada à cláusula JOIN. 

Considerações adicionais

Os exemplos até aqui usaram apenas duas tabelas, mas, nada impede que mais tabelas sejam acrescentadas.
Vamos adicionar algumas tabelas para esta consulta e obter mais dados. O diagrama da figura seguinte,  lista as tabelas para realizar a consulta a seguir que deve obter os seguintes campos:
  • Nome completo
  • Descrição do cargo
  • Data de nascimento
  • Endereço de e-mail
  • Endereços residenciais
para os funcionários que possuam o cargo “Application Specialist”.
Os dados destas tabelas são os seguintes:

Tabela Descrição
HUMANRESOURCES.EMPLOYEE Registra os empregados no banco de dados.
PERSON.EMAILADDRESS Endereços de e-mail de uma pessoa. Uma pessoa poderá ter mais de um endereço.
PERSON.PERSON Armazena dados gerais das pessoas do banco de dados.
PERSON.ADDRESS Cadastro de endereços. Um endereço pode ser usado em outras tabelas.
PERSON.BUSINESSENTITYADDRESS Relaciona uma pessoa com um endereço.

Desta forma, a consulta a ser usada é como descrita abaixo:

select pp.FirstName + ' ' + pp.LastName as CompleteName
   ,HRE.JobTitle
   ,HRE.BirthDate
   ,EA.EmailAddress
   ,A.AddressLine1
   ,A.AddressLine2
   ,A.City
from person.person as pp
join HumanResources.Employee HRE on hre.BusinessEntityID =
   pp.BusinessEntityID and hre.JobTitle = 'Application Specialist'
join Person.BusinessEntityAddress BEA on BEA.BusinessEntityID = HRE.BusinessEntityID
join Person.Address A on A.AddressID = BEA.AddressID
join Person.EmailAddress EA on EA.BusinessEntityID = HRE.BusinessEntityID;

Nenhuma novidade na consulta, apenas foram acrescentadas tabelas fazendo os devidos relacionamentos necessários. Ao trabalhar com JOINS, isto acaba tornando-se comum. O tipo INNER JOIN é um dos mais comuns e além de ser usado para junções de dados em um banco de dados normalizado, como pode ser percebido pelo exemplo, serve também para realizar filtros.

Alerta sobre desempenho

INNER JOINS dependem bastante das configurações dos índices do banco de dados feitas de forma correta. Tecnicamente, para cada registro da tabela origem da junção, a tabela do JOIN é percorrida completamente.
A consulta traz os seguintes resultados:

No próximo post será explicado o uso dos demais JOINS. Até lá.