Início / dba / Perguntas / 121034
Accepted
Michael Richardson
Asked: 2015-11-14 11:18:12 +0800 CST

Melhor prática entre usar LEFT JOIN ou NOT EXISTS

  • 772

Existe uma prática recomendada entre usar um formato LEFT JOIN ou NOT EXISTS?

Qual é a vantagem de usar um sobre o outro?

Se nenhum, qual deve ser preferido?

SELECT *
FROM tableA A
LEFT JOIN tableB B
     ON A.idx = B.idx
WHERE B.idx IS NULL

SELECT *
FROM tableA A
WHERE NOT EXISTS
(SELECT idx FROM tableB B WHERE B.idx = A.idx)

Estou usando consultas no Access em um banco de dados SQL Server.

sql-server join

4 respostas

  1. Best Answer

    A maior diferença não está no join vs não existe, é (como está escrito), o SELECT *.

    No primeiro exemplo, você obtém todas as colunas de Ae B, enquanto no segundo exemplo, você obtém apenas colunas de A.

    No SQL Server, a segunda variante é um pouco mais rápida em um exemplo muito simples:

    Crie duas tabelas de amostra:

    CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

    Insira 10.000 linhas em cada tabela:

    INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

    Remova cada 5ª linha da segunda tabela:

    DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

    Execute as duas SELECTvariantes de instrução de teste:

    SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

    Planos de execução:

    insira a descrição da imagem aqui

    A segunda variante não precisa realizar a operação de filtro, pois pode usar o operador anti-semi-junção esquerdo.

    • 77

  2. Logicamente, eles são idênticos, mas NOT EXISTSestão mais próximos do AntiSemiJoin que você está solicitando e geralmente são os preferidos. Também destaca melhor que você não pode acessar as colunas em B, porque é usado apenas como filtro (em vez de tê-las disponíveis com valores NULL).

    Muitos anos atrás (SQL Server 6.0 ish), LEFT JOINera mais rápido, mas isso não acontecia há muito tempo. Estes dias, NOT EXISTSé marginalmente mais rápido.

    O maior impacto no Access é que o JOINmétodo precisa concluir a junção antes de filtrá-la, construindo o conjunto associado na memória. Usá NOT EXISTS-lo verifica a linha, mas não aloca espaço para as colunas. Além disso, ele para de procurar quando encontra uma linha. O desempenho varia um pouco mais no Access, mas uma regra geral é que NOT EXISTStende a ser um pouco mais rápido. Eu estaria menos inclinado a dizer que é "melhor prática", pois há mais fatores envolvidos.

    • 34

  3. Uma exceção que notei NOT EXISTSser superior (ainda que marginalmente) LEFT JOIN ... WHERE IS NULLé ao usar servidores vinculados .

    Ao examinar os planos de execução, parece que o NOT EXISTSoperador é executado em um loop aninhado. Por meio do qual é executado por linha (o que suponho que faz sentido).

    Exemplo de plano de execução demonstrando esse comportamento: insira a descrição da imagem aqui

    • 8

  4. Em geral, o motor irá criar um plano de execução baseado essencialmente em:

    1. O número de linhas em A e B
    2. Se existe um Índice em A e/ou B.
    3. O número esperado de linhas de resultados (e linhas intermediárias)
    4. A forma da consulta de entrada (ou seja, sua pergunta)

    Para 4):

    O plano "não existe" encoraja um plano baseado em busca na tabela B. Esta é uma boa escolha quando a tabela A é pequena e a tabela B é grande (e existe um índice em B).

    O plano "antijoin" é uma boa escolha quando a tabela A é muito grande ou a tabela B é muito pequena ou nenhum índice em B e retorna um conjunto de resultados grande.

    No entanto, é apenas um "incentivo", como uma entrada ponderada. Um forte (1),(2),(3) geralmente faz a escolha de (4) discutível.

    ( Ignorando o efeito do seu exemplo retornando colunas diferentes devido ao *. ).

    • 7

relate perguntas