Внешний запрос SQL NOT IN Внутренний запрос, ссылающийся на внешний запрос

Question

У меня есть небольшая загадка T-SQL, которая, кажется, работает, но мне было интересно, может ли кто-нибудь попытаться дать мне представление о том, что здесь происходит. Рассмотрите следующий сценарий:

SELECT *
FROM TableA a
WHERE a.CustomerID NOT IN (SELECT b.CustomerID FROM TableB b WHERE a.CustomerID = b.CustomerID AND a.WorkOrder = b.WorkOrder)
AND a.[Date] > DATEADD(DD,-3,GETDATE())

Я довольно озадачен тем, как компилятор не работает с этим скриптом. Как он может выбрать, где NOT IN в подзапросе, ссылающемся на внешний запрос? Получить содержимое TableA, где CustomerID NOT IN, CustomerID из TableB и т. Д.... Но когда он находит совпадающий CustomerID в подзапросе, NOT IN запускается и предотвращает показ записи во внешнем запросе select. Я предполагаю, что это где компилятор останавливается. Но тогда, поскольку этот конкретный CustomerID не выбран, он не может включиться во внутренний запрос, поэтому внутренний запрос не выбирает этот CustomerID, и тогда внешний запрос может выбрать эту запись? Да? Нет? Падая в кроличью нору? Есть ли лучший способ написать это?

Был бы признателен, если бы кто-то мог рассказать о том, что здесь происходит, или сослаться на что-то, что могло бы объяснить. Я не мог найти никого, кто бы объяснял этот процесс, возможно, не используя правильные условия поиска.

Спасибо!

Answer 1

Это называется " коррелированный подзапрос", и "подзапрос может быть оценен один раз для каждой строки, обработанной внешним запросом".

Таким образом, здесь для каждой строки таблицы A подзапрос ищет совпадающие данные из таблицы B и определяет, выполнено ли условие NOT IN. Затем перейдите к следующей строке в Таблице A, чтобы повторить этот цикл, пока все соответствующие строки Таблицы A не будут оценены.

Альтернативным подходом может быть "исключение соединения слева", когда вы объединяете две таблицы, но затем игнорируете строки, в которых существует соединение.

SELECT
      *
FROM TableA a
LEFT JOIN TableB b ON a.CustomerID = b.CustomerID
                  AND a.WorkOrder = b.WorkOrder
WHERE b.CustomerID IS NULL
AND a.[Date] > DATEADD(DD, -3, GETDATE())
;

или другой вариант "полу-соединения" с использованием NOT EXISTS:

SELECT
      *
FROM TableA a
WHERE NOT EXISTS (
      SELECT NULL
      FROM TableB b
      WHERE a.CustomerID = b.CustomerID
      AND a.WorkOrder = b.WorkOrder
      )
AND a.[Date] > DATEADD(DD, -3, GETDATE())
;

Обратите внимание, что подзапрос, используемый для |NOT| EXISTS не должен возвращать какие-либо значения через предложение select. Некоторые предпочитают использовать "select 1" или "select *" при использовании EXISTS, но на самом деле это не имеет значения, какой из них используется. Использование "select NULL" - мое предпочтение.

Вы можете узнать больше об этих альтернативах, проверив планы выполнения, см., Например, http://sqlfiddle.com/.

Исходный запрос: Альтернатива "Левое исключение соединения": Альтернатива "Не существует":