Стандарт SQL относительно левого внешнего объединения и где условия
Я получаю различные результаты на основе условия фильтра в запросе на основе того, куда я помещаю условие фильтра. Мои вопросы:
- Существует ли техническое различие между этими запросами?
- Есть ли что-нибудь в стандарте SQL, который объясняет другой recordsets от запросов?
Учитывая упрощенный сценарий:
--Table: Parent Columns: ID, Name, Description
--Table: Child Columns: ID, ParentID, Name, Description
--Query 1
SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description
FROM Parent p
LEFT OUTER JOIN Child c ON (p.ID = c.ParentID)
WHERE c.ID IS NULL OR c.Description = 'FilterCondition'
--Query 2
SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description
FROM Parent p
LEFT OUTER JOIN Child c
ON (p.ID = c.ParentID AND c.Description = 'FilterCondition')
Я предположил, что запросы возвратят те же наборы результатов, и я был удивлен, когда они не сделали. Я использую MS SQL2005 и в фактических запросах, запрашиваю 1, возвратил ~700 строк, и запрос 2 возвратил ~1100 строк, и я не мог обнаружить шаблон, на котором были возвращены строки и какие строки были исключены. Было все еще много строк в запросе 1 с дочерними строками с данными и ПУСТЫМИ данными. Я предпочитаю стиль запроса 2 (и я думаю, что это более оптимально), но я думал, что запросы возвратят те же результаты.
Редактирование/Сводка:
Были некоторые большие ответы, предоставленные здесь. Мне было нелегко выбирать, кому присудить ответ. Я решил пойти с MDMA, так как это был первый ответ и один из самых ясных. На основе предоставленных ответов вот моя сводка:
Возможные результаты:
- A: Родитель без детей
- B: Родители с детьми
- |-> B1: Родители с детьми, где никакой ребенок не соответствует фильтру
- \-> B2: Родители с детьми, где 1 или более соответствуют фильтру
Результаты запроса:
- Запросите 1 возврат (A, B2)
- Запросите 2 возврата (A, B1, B2)
Запросите 2 всегда возвраты родитель из-за левого соединения. В запросе 1, выполняется оператор Where после левого соединения, таким образом, родители с детьми, где ни один из детей не соответствует фильтру, исключены (случай B1).
Примечание: только родительская информация возвращается в случае, если B1, и в случае, если B2 только родительская/дочерняя информация, соответствующая фильтру, возвращается.
HLGEM предоставил хорошую ссылку:
http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN
7 ответов
Первый запрос вернет случаи, когда у родителя нет потомков или когда некоторые из потомков соответствуют условию фильтра. В частности, случаи, когда у родителя есть один дочерний элемент, но он не соответствует условию фильтра, будут опущены.
Второй запрос вернет строку для всех родителей. Если по условию фильтра совпадений нет, для всех столбцов c будет возвращено NULL. Вот почему вы получаете больше строк в запросе 2 - родители с дочерними элементами, которые не соответствуют условию фильтрации, выводятся с NULL дочерними значениями, тогда как в первом запросе они отфильтровываются.
Да, разница огромная. Когда вы помещаете фильтры в предложение ON на LEFT JOIN, фильтр применяется до , результаты присоединяются к внешней таблице. Когда вы применяете фильтр в предложении WHERE, это происходит после применения LEFT JOIN.
Короче говоря, первый запрос будет исключать строки, в которых есть дочерние строки, но описание дочернего элемента не соответствует условию фильтрации, тогда как второй запрос всегда будет возвращать строку для родительского элемента.
Помещение условия в предложение where преобразует его во внутреннее соединение (если вы не используете что-то, где id имеет значение null, что дает вам записи, которых нет в таблице) См. Это для более полного объяснения:
http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN
Первый запрос возвращает меньше строк, потому что он возвращает только те строки, которые либо не имеют дочерних элементов, либо имеют дочерние элементы, соответствующие условию фильтра.
Предложение WHERE исключает остальные (те, у которых есть дочерние элементы, но не соответствующие условию фильтра).
Второй запрос показывает все три условия выше.
родители, у которых есть только дочерние элементы с description! = 'FilterCondition' , не появятся в запросе 1, потому что предложение WHERE оценивается после объединения строк.
Для этого набора записей:
parent
id
1
child
id parent filter
1 1 OtherCondition
2 1 OtherCondition
первый запрос вернет 0 записей, а второй - 1 запись:
WITH parent (id) AS
(
SELECT 1
),
child (id, parent, condition) AS
(
SELECT 1, 1, 'OtherCondition'
UNION ALL
SELECT 2, 1, 'OtherCondition'
)
SELECT *
FROM parent
LEFT JOIN
child
ON child.parent = parent.id
/* The children are found, so no fake NULL records returned */
1 1 1 OtherCondition
1 2 1 OtherCondition
Теперь добавляем предложение WHERE : предложение
WITH parent (id) AS
(
SELECT 1
),
child (id, parent, condition) AS
(
SELECT 1, 1, 'OtherCondition'
UNION ALL
SELECT 2, 1, 'OtherCondition'
)
SELECT *
FROM parent
LEFT JOIN
child
ON child.parent = parent.id
WHERE child.id IS NULL OR child.condition = 'FilterCondition'
WHERE фильтрует записи, возвращенные на предыдущем шаге, и ни одна запись не соответствует условию.
В то время как этот:
WITH parent (id) AS
(
SELECT 1
),
child (id, parent, condition) AS
(
SELECT 1, 1, 'OtherCondition'
UNION ALL
SELECT 2, 1, 'OtherCondition'
)
SELECT *
FROM parent
LEFT JOIN
child
ON child.parent = parent.id
AND child.condition = 'FilterCondition'
1 NULL NULL NULL
возвращает единственную фальшивую запись.
Я заметил несколько отличий, которые могут повлиять на результаты. В первом запросе у вас есть LEFT OUTER JOIN Child c ON (p.ID = c.ParentID)
, а затем во втором запросе у вас есть LEFT OUTER JOIN Child c
ON (p.ID = c.ParentID AND c.Description = 'FilterCondition') , и это заставляет второй запрос возвращать всех родителей с детьми, удовлетворяющими вашему условию, тогда как первое условие также вернет родителей без детей. Также обратите внимание на приоритет условий соединения и условия where.