Левое внешнее соединение не сохраняет левую таблицу

У меня есть этот запрос:

SELECT Q_ID,
   Q_DESC,
   COUNT(Q_ID)
FROM   #tmp_rep
   LEFT OUTER JOIN po_Questions po
        ON  Q_ID = Certificate AND FUNCS = 1
        AND LEN(LTRIM(RTRIM(po.UserName))) > 0
        AND LEN(LTRIM(RTRIM(po.UserNumber))) > 0
GROUP BY
   Q_ID,
   Q_DESC
ORDER BY
   Q_ID

таблица #tmp_rep имеет 2 столбца (Q_ID,Q_Desc) и 4 строки. Таблица po_Questions имеет 10, которые используют 3 кода Q_ID в столбцах строк сертификата. Если я запускаю этот запрос, все в порядке, и для Q-ID=4 я получаю 0 для подсчета, но если я написал этот запрос следующим образом:

SELECT Q_ID,
   Q_DESC,
   COUNT(Q_ID)
FROM   #tmp_rep
   LEFT OUTER JOIN po_Questions po
        ON  Q_ID = Certificate 
WHERE FUNCS = 1
      AND LEN(LTRIM(RTRIM(po.UserName))) > 0
        AND LEN(LTRIM(RTRIM(po.UserNumber))) > 0

GROUP BY
   Q_ID,
   Q_DESC
ORDER BY
   Q_ID

тогда я получаю только 3 строки в результате, и Q_ID=4 не принадлежит результату. Почему SQL Server имеет такое поведение?

Спасибо

Источник

2 ответа

Решение

Для несоответствующих строк po.UserName будет NULL так LEN(LTRIM(RTRIM(po.UserName))) является NULL

NULL > 0 оценивает UNKNOWN не TRUE поэтому, когда предикат находится в WHERE вы превращаете свое внешнее соединение обратно во внутреннее. Аналогично для FUNCS как указывает SQLMenace.

Возможно, вы захотите скачать постер Ицик Бен Ган "Логическая обработка запросов".

Концептуально происходит следующее (однако это не следует путать с тем, как это физически реализовано!)

Для вашего первого запроса:

  • Декартово произведение на #tmp_rep, po_Questions
  • Тогда ON Filter применяется, который эффективно делает INNER JOIN на Q_ID = Certificate но также исключает любые po_Questions строки, которые не соответствуют вашему предикату.
  • Тогда несоответствие Outer Ряды из #tmp_rep добавлены обратно. Они будут иметь NULL для всех столбцов из po_Questions
  • Здесь нет WHERE пункт, так что это конечный результат.

Для вашего второго запроса:

  • Декартово произведение на #tmp_rep, po_Questions
  • Тогда ON Filter применяется, который эффективно делает INNER JOIN на Q_ID = Certificate,
  • Тогда несоответствие Outer Ряды из #tmp_rep добавлены обратно. Они будут иметь NULL для всех столбцов из po_Questions
  • Тогда WHERE пункт оценивается. Это определенно удалит все строки из предыдущего шага и, возможно, также дополнительные строки.
Источник

По сути, вы создали внутреннее соединение, изменение

WHERE FUNCS = 1

в

AND FUNCS = 1

Левое внешнее соединение не может быть указано в предложении WHERE, иначе оно будет отфильтровано

другая проблема - функция LEN для NULL, она не будет больше 0

Источник
Другие вопросы по тегам