Лучшая практика использования LEFT JOIN или NOT EXISTS

Существует ли наилучшая практика между использованием формата LEFT JOIN или NOT EXISTS?

В чем преимущество использования одного над другим?

Если нет, что должно быть предпочтительным?

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)

Я использую запросы в Access для базы данных SQL Server.

41 голос | спросил Michael Richardson 13 52015vEurope/Moscow11bEurope/MoscowFri, 13 Nov 2015 22:18:12 +0300 2015, 22:18:12

4 ответа


34

Самая большая разница не в соединении vs не существует, она (как написано), SELECT *.

В первом примере вы получаете все столбцы из как A, так и B, тогда как во втором примере вы получаете только столбцы из A.

В SQL Server второй вариант немного быстрее в очень простом надуманном примере:

Создайте две таблицы образцов:

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

Вставьте 10 000 строк в каждую таблицу:

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

Удалите каждую пятую строку из второй таблицы:

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;

Выполните два тестовых варианта SELECT:

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);

Планы выполнения:

введите изображение здесь »> </a> </p>

<p> Второй вариант не должен выполнять операцию фильтра, так как он может использовать левый оператор анти-полусоединения. </p></body></html>

ответил Max Vernon 13 52015vEurope/Moscow11bEurope/MoscowFri, 13 Nov 2015 22:37:59 +0300 2015, 22:37:59
16

Логически они идентичны, но NOT EXISTS ближе к AntiSemiJoin, о котором вы просите, и обычно предпочитается. Он также подчеркивает, что вы не можете получить доступ к столбцам в B, потому что он используется только как фильтр (в отличие от наличия у них значений NULL).

Много лет назад (SQL Server 6.0 ish), LEFT JOIN был быстрее, но этого не было в течение очень долгого времени. В наши дни NOT EXISTS работает немного быстрее.


Самое большое влияние в Access заключается в том, что метод JOIN должен завершить соединение до его фильтрации, построив объединенный набор в памяти. Используя NOT EXISTS, он проверяет строку, но не выделяет пространство для столбцов. Кроме того, он перестает смотреть, как только находит строку. Производительность изменяется немного больше в Access, но общее правило состоит в том, что NOT EXISTS имеет тенденцию быть немного быстрее. Я был бы менее склонен говорить, что это «лучшая практика», так как есть больше факторов.

ответил Rob Farley 14 62015vEurope/Moscow11bEurope/MoscowSat, 14 Nov 2015 03:27:13 +0300 2015, 03:27:13
5

В общем, движок создаст план выполнения, основанный главным образом на:

  1. Число строк в A и B
  2. Есть ли указатель на A и /или B.
  3. Ожидаемое количество строк результатов (и промежуточных строк)
  4. Форма входного запроса (т. е. ваш вопрос)

Для (4):

План «не существует» поощряет план, основанный на поиске, на таблице B. Это хороший выбор, когда таблица A мала, а таблица B большая (и индекс существует на B).

План «antijoin» является хорошим выбором, если таблица A очень большая или таблица B очень мала или не имеет индекса на B и возвращает большой результирующий набор.

Однако это просто «поощрение», как взвешенный вход. Сильные (1), (2), (3) часто делают выбор для (4) спорным.

(Игнорирование эффекта вашего примера, возвращающего разные столбцы из-за *, адресуемого ответом @MaxVernon.).

ответил crokusek 14 62015vEurope/Moscow11bEurope/MoscowSat, 14 Nov 2015 04:46:42 +0300 2015, 04:46:42
3

Исключением я заметил, что NOT EXISTS является превосходным (хотя и незначительно) до LEFT JOIN ... WHERE IS NULL при использовании Linked Servers .

Изучая планы выполнения, выясняется, что оператор NOT EXISTS выполняется в режиме вложенного цикла. При этом он выполняется на основе каждой строки (что, я полагаю, имеет смысл).

Пример плана выполнения, демонстрирующий это поведение: введите описание изображения здесь

ответил pimbrouwers 27 J000000Thursday17 2017, 18:56:05

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132