NOT IN x NOT EXISTS x LEFT JOIN (pt-BR)

Nota de Migração: Este artigo foi migrado para o Portal Wiki em Português. O conteúdo será atualizado aqui http://social.technet.microsoft.com/wiki/pt-br/contents/articles/12561.not-in-x-not-exists-x-left-join.aspx.

Neste Post irei explicar um pouco sobre NOT IN x NOT EXISTS.
É mais comum o uso do NOT IN quando você quer ver os itens não existentes em outra tabela. Vamos ver as diferenças entre os comandos.

Vamos criar algumas tabelas para teste, criar índices e inserir alguns registros.

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 agora 2 comandos e comparar os resultados.

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)

NOT IN: costs=0,059 - logical reads(Setor)=2
NOT EXISTS: costs=0,059 - logical reads(Setor)=2

Veja que neste cenário os resultados são idênticos.

Vamos alterar a coluna referenciada, mudando ela pra NULL, sem alterar os valores.

ALTER TABLE Setor ALTER COLUMN Gerente int null

Executando os mesmos comandos, temos agora resultados diferentes.

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)

NOT IN: costs=0,571 - logical reads(Setor)=10001
NOT EXISTS: costs=0,059 - logical reads(Setor)=2

Veja que quando se utiliza NOT IN em colunas que permite nulo (mesmo não tendo nenhum valor nulo) ele não utiliza índice.
Resultado: o NOT IN ficou 10 vezes mais lento e ao invés de acessar 2 páginas passou a acessar 10001 páginas.
Com este exemplo podemos perceber que o comando NOT EXISTS sempre será melhor.

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.

Este exemplo também serviu para mostrar que devemos analisar melhor as tabelas ao criar o modelo, pois se você tem um campo que nunca receberá valor nulo, marque ele como NOT NULL.

Vamos inserir um registro com valor nulo:

INSERT INTO Setor(Descricao, Gerente) values('RH',NULL)

Consulte novamente:

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)