No post anterior [http://www.ecode10.com/artigo/1482/NOT+IN+X+NOT+EXISTS.aspx] vimos um pouco sobre NOT IN e NOT EXISTS, vamos dar continuidade acrescentando o uso do LEFT JOIN. Vamos utilizar a mesma base de testes da Parte 1.

CREATE TABLE Funcionario (FuncID int identity, Nome varchar(50))

ALTER TABLE Funcionario ADD CONSTRAINT PK_Funcionario PRIMARY KEY CLUSTERED(FuncID)

CREATE TABLE Setor (SetorID int identity, Descricao varchar(50), Gerente int not null) ALTER TABLE Setor ADD CONSTRAINT PK_Setor PRIMARY KEY CLUSTERED(SetorID) ALTER TABLE Setor ADD CONSTRAINT FK_Setor_Funcionario FOREIGN KEY(Gerente) REFERENCES Funcionario(FuncID) CREATE NONCLUSTERED INDEX IX_Setor_Gerente ON Setor(Gerente) GO

insert into Funcionario(Nome) values(NEWID()) GO 5000

insert into Setor(Descricao, Gerente) values('Financeiro',7)
insert into Setor(Descricao, Gerente) values('Contabil',6)
insert into Setor(Descricao, Gerente) values('Marketing',5)
insert into Setor(Descricao, Gerente) values('Patrimonio',2)
insert into Setor(Descricao, Gerente) values('Auditoria',3)
insert into Setor(Descricao, Gerente) values('Juridico',4)
insert into Setor(Descricao, Gerente) values('Pessoal',1)

Vamos executar estes 3 comandos e perceber que os resultados são idênticos.

Select FuncID from Funcionario where FuncID not in (select Gerente from Setor) select FuncID from Funcionario where not exists (select Gerente from Setor where Setor.Gerente=Funcionario.FuncID) select Funcionario.FuncID from Funcionario left join Setor ON Funcionario.FuncID=Setor.Gerente where Setor.Gerente is null

Agora vamos mudar a coluna, permitindo valores nulos e acrescentando registro nulo.

ALTER TABLE Setor ALTER COLUMN Gerente int null INSERT INTO Setor(Descricao, Gerente) values('RH',NULL)

Vamos entender algumas características destes comandos

O EXISTS retorna sempre TRUE ou FALSE. 1) TRUE assim que ele encontrar uma combinação entre as tabelas. 2) FALSE se não encontrar nenhuma combinação.

O IN retorna TRUE, FALSE e NULL. 1) TRUE é retornado quando o valor não-null em questão é encontrado na lista. 2) FALSE é retornado quando o valor não-null não é encontrado na lista e a lista não tem valores não-null. 3) NULL é retornado quando o valor é null, ou o valor não-null não é encontrado na lista e a lista contém pelo menos um valor nulo.

Tanto o NOT IN quanto o NOT EXISTS tem uma característica em comum, o Anti Semi Join. Com este recurso assim que o mecanismo encontra uma combinação ele ignora as outras combinações em ambas tabelas.

Execute estes 3 comandos e perceba as diferenças.

Select FuncID from Funcionario where FuncID not in (select Gerente from Setor) select FuncID from Funcionario where not exists (select Gerente from Setor where Setor.Gerente=Funcionario.FuncID)

Veja que o NOT IN não retorna nenhum registro caso a coluna referenciada tenha algum valor nulo.

Para evitar dor de cabeça com campos nulos, use sempre o NOT EXISTS.

Muitas pessoas usam o LEFT JOIN para fazer estes tipo de pesquisa, apesar de retornar os mesmo valores do NOT EXISTS, ele tem a desvantagem de percorrer toda a tabela, pois no LEFT JOIN não tem o Anti Semi Join.

select FuncID from Funcionario where not exists (select Gerente from Setor where Setor.Gerente=Funcionario.FuncID) select Funcionario.FuncID from Funcionario left join Setor ON Funcionario.FuncID=Setor.Gerente where Setor.Gerente is null

Resumindo, sempre que quiser saber os valores que estão na tabelaA e não estão na tabelaB, use o NOT EXISTS.