Pular para o conteúdo principal

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


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á.

Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN…