T-SQL: INTERSECT e EXCEPT

Quarta... terminei a consultoria mais cedo (as 16h), tenho duas horas até começar a próxima turma do curso Microsoft 2793 (Reporting Service) as 18h... Tempo suficiente para escrever mais um post e fazer um pequeno lanche (a turma da noite detona o coffee break)!

Uma situação muito comum para quem trabalha com banco de dados é comparar o conteúdo de duas tabelas, retornando as linhas em comum ou as linhas que existem em uma tabela e não existem na outra (minus da álgebra relacional). Até o SQL Server 2000 a solução para estes dois problemas era utilizar JOIN ou SUBQUERY, veja no exemplo abaixo:

-- Criando duas tabelas
create table Teste1 (Coluna1 varchar(20))
create table Teste2 (Coluna1 varchar(20))
go

insert Teste1 values('Rio de Janeiro')
insert Teste1 values('Sao Paulo')
insert Teste1 values('Salvador')
insert Teste1 values('Sao Luiz')

insert Teste2 values('Rio de Janeiro')
insert Teste2 values('Sao Paulo')
insert Teste2 values('Salvador')
insert Teste2 values('Brasilia')
go

-- Obtendo linhas em comum
select Coluna1 from Teste1 where exists
(select * from Teste2 where Teste2.Coluna1 = Teste1.Coluna1)

-- Obtendo as linhas que existem na tabela Teste1 e
-- não existem na tabela Teste2
select Coluna1 from Teste1 where not exists
(select * from Teste2 where Teste2.Coluna1 = Teste1.Coluna1)

No SQL Server 2005 ficou muito mais fácil utilizando os operadores INTERSECT e EXCEPT, veja abaixo:

-- Obtendo linhas em comum
select Coluna1 from Teste1
INTERSECT
select Coluna1 from Teste2

-- Obtendo as linhas que existem na tabela Teste1 e
-- não existem na tabela Teste2
select Coluna1 from Teste1
EXCEPT
select Coluna1 from Teste2

Até o próximo post.
Landry.

Novos tipos de dados do SQL 2008: HIERARCHYID (Parte 2)

Depois de um logo período sem publicar nada no Blog (fiquei enrolado no trabalho...), finalmente vou finalizar o post sobre o tipo de dados hierárquico. A primeira parte está no endereço: http://sqlserver-brasil.blogspot.com/2008/05/novos-tipos-de-dados-do-sql-2008_14.html.

No último post mostrei como implementar hierarquia no SQL Server 2005, veremos agora as novidades do SQL Server 2008. Vou utilizar a mesma hierarquia do post anterior, segue abaixo a figura:


O novo tipo de dados hierarchyid representa uma hierarquia, estando disponível uma série de métodos para sua manipulação: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write. Vou utilizar alguns destes no exemplo abaixo.

Primeiro vamos criar a tabela abaixo contendo o novo tipo de dados hierarchyid:

CREATE TABLE FuncionarioHierarchies(
FuncionarioID int not null primary key,
Nome varchar(10) not null,
Cargo varchar(12) not null,
Hierarquia hierarchyid null)

Para incluir o ROOT da hierarquia (Presidente Jose) vamos utilizar o método GetRoot, que retorno o ROOT da hierarquia:

INSERT FuncionarioHierarchies VALUES
(1,'Jose','Presidente',hierarchyid::GetRoot())

SELECT * FROM FuncionarioHierarchies
where Hierarquia = hierarchyid::GetRoot()

Reparem que para consultar o ROOT, basta utilizar o mesmo método da inclusão!

Vamos incluir agora os Diretores Maria e Pedro, utilizando o método GetDescendant:

-- Inclui Maria
DECLARE @Pai hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
INSERT FuncionarioHierarchies VALUES
(2,'Maria','Diretor',@Pai.GetDescendant(NULL,NULL))

-- Inclui Pedro
DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES
(3,'Pedro','Diretor',@Pai.GetDescendant(@PrimeiroFilho,NULL))

A tabela abaixo mostra como utilizar os dois parâmetros do método pai.GetDescendant (filho1,filho2):



Para completar nosso organograma utilize o script abaixo:

DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = Hierarquia FROM FuncionarioHierarchies
where FuncionarioID = 2
INSERT FuncionarioHierarchies VALUES
(4,'Ana','Gerente',@Pai.GetDescendant(NULL,NULL))
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES
(5,'Lucia','Gerente',@Pai.GetDescendant(@PrimeiroFilho,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 3
INSERT FuncionarioHierarchies VALUES
(6,'Ronaldo','Gerente',@Pai.GetDescendant(NULL,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 5
INSERT FuncionarioHierarchies VALUES
(7,'Marcio','Coordenador',@Pai.GetDescendant(NULL,NULL))
go

Agora vamos retornar a hierarquia utilizando o SELECT abaixo com o método GetAncestor:

SELECT FuncionarioID,Nome,Cargo,Hierarquia.GetLevel() as Nivel,
(Select FuncionarioID FROM FuncionarioHierarchies
Where Hierarquia = e.Hierarquia.GetAncestor(1)) As Chefe
FROM FuncionarioHierarchies e

Até o próximo post,
Landry.