Não existe
NÃO EXISTE:
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE NOT EXISTS (
SELECT 1
FROM Northwind..[Order Details] od
WHERE p.ProductId = od.ProductId)
ou não em:
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
SELECT ProductID
FROM Northwind..[Order Details])
O plano de execução da consulta diz que ambos fazem a mesma coisa. Se for esse o caso, qual é o formulário recomendado?
Isto é baseado na base de dados NorthWind.
[editar]
Encontrei este artigo útil.: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx Acho que vou ficar pelo não existe.10 answers
Eu sempre default em NOT EXISTS
.
Os planos de execução pode ser o mesmo no momento, mas se a coluna é alterado no futuro, para permitir que NULL
s NOT IN
versão precisa fazer mais trabalho (mesmo se não NULL
s estão realmente presentes nos dados) e a semântica de NOT IN
se NULL
s são presentes não são susceptíveis de ser o que você quer de qualquer maneira.
Quando nenhum Products.ProductID
ou [Order Details].ProductID
permitir NULL
o NOT IN
será tratado de forma idêntica ao seguinte: consulta.
SELECT ProductID,
ProductName
FROM Products p
WHERE NOT EXISTS (SELECT *
FROM [Order Details] od
WHERE p.ProductId = od.ProductId)
O Plano EXACTO pode variar, mas para o meu exemplo, os dados são os seguintes.
Um equívoco razoavelmente comum parece ser que as sub-consultas correlacionadas são sempre " más " em comparação com as junções. Eles certamente podem ser quando forçam um plano de loops aninhado (sub consulta avaliada linha por linha), mas este plano inclui um operador lógico anti semi-join. As junções anti-semi não estão restritas a loops aninhados, mas podem usar junções de hash ou merge (como neste exemplo) muito.
/*Not valid syntax but better reflects the plan*/
SELECT p.ProductID,
p.ProductName
FROM Products p
LEFT ANTI SEMI JOIN [Order Details] od
ON p.ProductId = od.ProductId
Se [Order Details].ProductID
for NULL
-able the query then becomes
SELECT ProductID,
ProductName
FROM Products p
WHERE NOT EXISTS (SELECT *
FROM [Order Details] od
WHERE p.ProductId = od.ProductId)
AND NOT EXISTS (SELECT *
FROM [Order Details]
WHERE ProductId IS NULL)
A razão para isso é que a semântica correta se [Order Details]
contém algum NULL
ProductId
s é devolver nenhum resultado. Veja o spool extra anti semi join e contagem de linhas para verificar isso que é adicionado ao plano.
Se Products.ProductID
também é alterado para se tornar NULL
- capaz a consulta então torna-se
SELECT ProductID,
ProductName
FROM Products p
WHERE NOT EXISTS (SELECT *
FROM [Order Details] od
WHERE p.ProductId = od.ProductId)
AND NOT EXISTS (SELECT *
FROM [Order Details]
WHERE ProductId IS NULL)
AND NOT EXISTS (SELECT *
FROM (SELECT TOP 1 *
FROM [Order Details]) S
WHERE p.ProductID IS NULL)
A razão para isso é porque um NULL
Products.ProductId
não deve ser devolvido na results except if the NOT IN
sub query were to return no results at all (i.e. the [Order Details]
table is empty). Nesse caso, deveria. No plano para os meus dados de amostra isso é implementado adicionando outro anti semi join como abaixo.
O efeito disso é mostrado em O post no blog já ligado por Buckley. No exemplo, o número de leituras lógicas aumenta de cerca de 400 para 500.000.
Além disso, o facto de um único {[5] } reduzir a contagem de linhas para zero torna a estimativa da cardinalidade muito difícil. Se o servidor SQL assume que isso irá acontecer, mas de fato não havia linhas NULL
nos dados, o resto do plano de execução pode ser catastroficamente pior, se isso for apenas parte de uma consulta maior, com loops aninhados inapropriados causando a execução repetida de uma sub-árvore cara, por exemplo.
NOT IN
numa coluna capaz de NULL
. Isto ... o artigo mostra outro para uma consulta na base de Dados AdventureWorks2008
.
Para a NOT IN
numa coluna NOT NULL
ou a NOT EXISTS
quer contra uma coluna nulável quer não nulável, dá o seguinte plano.
Quando a coluna muda para NULL
- able o plano NOT IN
agora parece
Sales.SalesOrderDetail.ProductID = <correlated_product_id>
a dois procura por linha externa. O adicional está em WHERE Sales.SalesOrderDetail.ProductID IS NULL
.
Como isto está debaixo de um semi-junção anti-se que se retorna qualquer linha, a segunda procura não irá ocorrer. Contudo, se Sales.SalesOrderDetail
não contiver qualquer NULL
ProductID
s duplicará o número de operações de busca necessárias.
Também esteja ciente de que não IN não é equivalente a não existir quando se trata de null.
Este post explica muito bem
Http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Quando a sub-pesquisa voltar, mesmo que seja um nulo, não irá corresponder a nenhum linhas.
A razão para isso pode ser encontrada olhando para os detalhes do que o Na verdade, não está em operação.
Digamos, para ilustrar que há 4 linhas na tabela chamada t, há uma coluna chamada ID com valores 1..4WHERE SomeValue NOT IN (SELECT AVal FROM t)
É equivalente a
Vamos dizer Ainda que Avalal é nula onde ID = 4. Daí isso != a comparação retorna desconhecida. A tabela lógica da verdade para e Estados esse desconhecido e verdadeiro é Desconhecido, desconhecido e falso é falso. Há nenhum valor que possa ser e seja desconhecido para produzir o resultado verdadeiroWHERE SomeValue != (SELECT AVal FROM t WHERE ID=1) AND SomeValue != (SELECT AVal FROM t WHERE ID=2) AND SomeValue != (SELECT AVal FROM t WHERE ID=3) AND SomeValue != (SELECT AVal FROM t WHERE ID=4)
Por isso, se alguma linha desse subcontingente retornar nula, o inteiro não em o operador irá avaliar para falso ou nulo e nenhum registro será devolvidos
SELECT ProductID, ProductName
FROM Northwind..Products p
outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null
Eu tenho uma tabela que tem cerca de 120 mil registros e precisa selecionar apenas aqueles que não existem (correspondido com uma coluna varchar) em quatro outras tabelas com o número de linhas aprox 1500, 4000, 40000, 200. Todos os quadros envolvidos têm um índice único na coluna em causa Varchar
.
NOT IN
demorou cerca de 10 minutos, demorou 4 segundos.
Tenho uma pergunta recursiva que pode ter alguma secção não lançada que pode ter contribuído para os 10 minutos, mas a outra opção a tomar 4 segundos explica-me, pelo menos para mim que NOT EXISTS
é muito melhor ou pelo menos que IN
e EXISTS
não são exactamente os mesmos e sempre vale a pena verificar antes de avançar com o código.
No seu exemplo específico são os mesmos, porque o Optimizador descobriu que o que está a tentar fazer é o mesmo em ambos os exemplos. Mas é possível que em exemplos não-triviais o otimizador pode não fazer isso, e nesse caso há razões para preferir um a outro na ocasião.
NOT IN
deverá ser preferido se estiver a testar várias linhas no seu selecto exterior. O subquery dentro da declaração {[[0]} pode ser avaliado no início da execução, e o a tabela temporária pode ser assinalada com cada valor na opção externa, em vez de executar de novo a sub-selecção de cada vez, como seria necessário com a instrução NOT EXISTS
.
Se o subconjunto deve estar correlacionado com a selecção exterior, então NOT EXISTS
pode ser preferível, uma vez que o Optimizador pode descobrir uma simplificação que impede a criação de quaisquer tabelas temporárias para desempenhar a mesma função.
Eu estava a usar
SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)
E descobriu que estava a dar resultados errados (por errado quero dizer sem resultados). Como havia um NULL no quadro 2.Col1.
Ao mudar a pesquisa para
SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)
Deu-me os resultados correctos.
Desde então eu comecei a usar não existe em todos os lugares.
Em termos de eficiência, eu descobri que a junção à esquerda é nula mais eficiente (quando uma abundância de linhas são para ser selecionadas que é)
SELECT x.col
FROM big_table x
WHERE x.key IN( SELECT key FROM really_big_table );
Não seria relativamente lento o isn't much to limit size of what the query check to see if they key is in. Seria preferível neste caso.
Mas, dependendo do optimizador do SGBD, isto não pode ser diferente.Como um exemplo de quando existe é melhor
SELECT x.col
FROM big_table x
WHERE EXISTS( SELECT key FROM really_big_table WHERE key = x.key);
AND id = very_limiting_criteria