Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как работает объединение FULL JOIN, с условиями  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
MS SQL 2005
DECLARE @tab1 TABLE 
([id] int IDENTITY(1,1), prd INT)

INSERT into @tab1 (prd) values(12)
INSERT into @tab1 (prd) values(12)
INSERT into @tab1 (prd) values(12)
INSERT into @tab1 (prd) values(13)
INSERT into @tab1 (prd) values(13)

DECLARE @tab2 TABLE 
([id] int IDENTITY(1,1), prd INT, gr INT)

INSERT into @tab2 (prd, gr) values(11, 1)
INSERT into @tab2 (prd, gr) values(12, 1)
INSERT into @tab2 (prd, gr) values(13, 1)
INSERT into @tab2 (prd, gr) values(10, 2)

--Нужно получить (для gr=1)
--11
--12
--12
--12
--13
--13

-- Так не работает т.к. попадает в выборку значение 10.
SELECT t2.prd
FROM @tab1 t1 FULL JOIN @tab2 t2 ON gr=1 and t1.prd = t2.prd

-- Только так
SELECT t2.prd
FROM @tab1 t1 FULL JOIN (SELECT * FROM @tab2 WHERE gr=1) t2 ON t1.prd = t2.prd
Почему?
10 окт 11, 11:47    [11411406]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
Glory
Member

Откуда:
Сообщений: 104751
trew
Почему?

Наверное потому, что условия в ON gr=1 проверяется для результат соединения ?
10 окт 11, 11:50    [11411446]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SELECT t2.prd
FROM @tab1 t1 FULL JOIN @tab2 t2 ON /*t2.gr=1 and*/ t1.prd = t2.prd
WHERE t2.gr=1;
10 окт 11, 11:56    [11411521]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
Dafur
Member

Откуда:
Сообщений: 28
Именно потому что FULL JOIN. В результат попадают ВСЕ записи, даже если условие объединения не выполняется.
10 окт 11, 11:57    [11411529]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iljy
Member

Откуда:
Сообщений: 8711
iap
SELECT t2.prd
FROM @tab1 t1 FULL JOIN @tab2 t2 ON /*t2.gr=1 and*/ t1.prd = t2.prd
WHERE t2.gr=1;

Так неправильно, будут отсеяны все данные из t1, не имеющие пары в t2, фактически FULL JOIN превращается в RIGHT.
10 окт 11, 12:02    [11411590]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Glory,

Для меня это открытие. У меня в коде (вообще, для разных таблиц) часто встречается конструкция

FROM @tab1 t1 INNER JOIN @tab2 t2 ON t1.prd = t2.prd AND gr=1 
-- или 
FROM @tab1 t1 LEFT JOIN @tab2 t2 ON t1.prd = t2.prd AND gr=1 

куда мне эти константы ( gr=1 ) девать, в условие WHERE (как показал iap)
или так заворачивать их

FROM @tab1 t1 INNER JOIN (SELECT * FROM @tab2 WHERE gr=1) t2 ON t1.prd = t2.prd

Без плана выполнения, из жизненного опыта можно дать совет?
10 окт 11, 12:04    [11411606]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
Glory
Member

Откуда:
Сообщений: 104751
trew
куда мне эти константы ( gr=1 ) девать, в условие WHERE (как показал iap)

Это зависит от того, что вы хотите получить
Нужно просто знать порядок обработки сервером разных частей запроса
10 окт 11, 12:05    [11411615]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iljy
Member

Откуда:
Сообщений: 8711
trew,

вы понимаете, как работают разные виды соединений? Из жизненного опыта: для лучшего понимания надо смотреть план.
10 окт 11, 12:06    [11411616]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
aleks2
Guest
iljy
trew,

вы понимаете, как работают разные виды соединений? Из жизненного опыта: для лучшего понимания надо смотреть план.


Даже если вы разберете двигатель внутреннего сгорания до винтика - вы не поймете как он работает.

Соединений то всего 4 типа INNER, LEFT/RIGHT, FULL и CROSS. Тривиальнее тока азбука.
10 окт 11, 12:22    [11411757]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2,

ага, но понять, в какой момент какие условия применяются, в зависимости от вида соединения, проще всего глядя на план.
10 окт 11, 12:33    [11411867]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
--Так писать
FROM @tab1 t1 INNER JOIN @tab2 t2 ON t1.prd = t2.prd AND gr=1 
--или так
FROM @tab1 t1 INNER JOIN (SELECT * FROM @tab2 WHERE gr=1) t2 ON t1.prd = t2.prd

Glory
Это зависит от того, что вы хотите получить
Нужно просто знать порядок обработки сервером разных частей запроса

у меня далее, ко второй таблице привязывается ещё несколько таблиц через LEFT JOIN.
уже определился. Буду в профайлере смотреть количество Reads, чтобы понять эквивалентны они или нет.
10 окт 11, 12:46    [11411983]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
Glory
Member

Откуда:
Сообщений: 104751
trew
--Так писать
FROM @tab1 t1 INNER JOIN @tab2 t2 ON t1.prd = t2.prd AND gr=1 
--или так
FROM @tab1 t1 INNER JOIN (SELECT * FROM @tab2 WHERE gr=1) t2 ON t1.prd = t2.prd

Без разницы
Можно даже так
FROM @tab1 t1 INNER JOIN @tab2 t2 ON t1.prd = t2.prd
WHERE gr=1
Главное знать, что

When SQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. Although the physical execution of various joins uses many different optimizations, the logical sequence is:

- The join conditions in the FROM clause are applied.
- The join conditions and search conditions from the WHERE clause are applied.
- The search conditions from the HAVING clause are applied.
10 окт 11, 12:51    [11412040]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
pochemu_eto_iap_ne_prav?
Guest
iljy
iap
SELECT t2.prd
FROM @tab1 t1 FULL JOIN @tab2 t2 ON /*t2.gr=1 and*/ t1.prd = t2.prd
WHERE t2.gr=1;

Так неправильно, будут отсеяны все данные из t1, не имеющие пары в t2, фактически FULL JOIN превращается в RIGHT.

а разве автор и не заказал правое соединение своим "t2.gr=1"?
у него в левой таблице вообще нет gr,
а условие на равенство именно по gr.
значит по-любому данные из левой таблицы,
не имеющие пар в t2, будут отсеяны
10 окт 11, 13:27    [11412402]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iljy
Member

Откуда:
Сообщений: 8711
pochemu_eto_iap_ne_prav?
iljy
пропущено...

Так неправильно, будут отсеяны все данные из t1, не имеющие пары в t2, фактически FULL JOIN превращается в RIGHT.

а разве автор и не заказал правое соединение своим "t2.gr=1"?
у него в левой таблице вообще нет gr,
а условие на равенство именно по gr.
значит по-любому данные из левой таблицы,
не имеющие пар в t2, будут отсеяны

Нет, не заказал. Посмотрите на вариант ТС, отмеченный как --Только так.


trew
у меня далее, ко второй таблице привязывается ещё несколько таблиц через LEFT JOIN.
уже определился. Буду в профайлере смотреть количество Reads, чтобы понять эквивалентны они или нет.

Вы понимаете разницу между INNER, LEFT и FULL JOIN? Вы с логикой своих соединений разберитесь для начала.
10 окт 11, 13:39    [11412524]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
iljy,

У меня FULL JOIN используется для выборки во временную таблицу.
Да, правильно говорит Glory -нечего смешивать два вопроса в одной теме. Учту.
10 окт 11, 13:43    [11412573]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
spasibo_doshlo
Guest
и то правда. да, пардон, не дочитамши до конца первый пост понаписала.
(smile: ashamed)
10 окт 11, 13:46    [11412606]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iljy
Member

Откуда:
Сообщений: 8711
trew
iljy,

У меня FULL JOIN используется для выборки во временную таблицу.
Да, правильно говорит Glory -нечего смешивать два вопроса в одной теме. Учту.

Какая разница где он используется? Первичен вопрос "а что вы хотите получить?" В стартовом посте проблема не в IO, а в том, что запросы разные логически, в частности у них разный алгоритм проверки условий. Соответственно сначала надо понять, что с чем вы соединять собираетесь, а уже потом оптимизировать.
10 окт 11, 13:55    [11412684]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iljy
iap
SELECT t2.prd
FROM @tab1 t1 FULL JOIN @tab2 t2 ON /*t2.gr=1 and*/ t1.prd = t2.prd
WHERE t2.gr=1;

Так неправильно, будут отсеяны все данные из t1, не имеющие пары в t2, фактически FULL JOIN превращается в RIGHT.
Скорее всего неправильно.
Но желаемый результат получается, однако.
Кто точно знает, что надо-то было?
10 окт 11, 14:07    [11412773]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
У меня в первом посте написано: Нужно получить...

А далее, обнаружив такие нюансы, возник вопрос по оптимизации (совершенно) другого кода

FROM @tab1 t1 INNER JOIN @tab2 t2 ON t1.prd = t2.prd AND gr=1 
LEFT JOIN @tab3 ...
LEFT JOIN @tab4 ... 
LEFT JOIN @tab5 ... 
и хотел разобраться, раз FULL JOIN так работает, когда будет осуществлена проверка для этого кода.
Сразу на этапе INNER JOIN (gr=1 ) или после присоединения всех таблиц т.е. в самом конце для результата (gr=1 ) ?
10 окт 11, 14:19    [11412879]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
trew,

https://www.sql.ru/forum/actualthread.aspx?tid=812892&pg=1&mid=9915805#9915805
10 окт 11, 14:23    [11412915]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
iap,

Следующие действия демонстрируют порядок обработки инструкции SELECT.

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE или WITH ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP


а FULL JOIN в первом посте -это исключение из правил?
SELECT t2.prd
FROM @tab1 t1 FULL JOIN @tab2 t2 ON gr=1 and t1.prd = t2.prd
10 окт 11, 14:30    [11412979]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
trew
и хотел разобраться, раз FULL JOIN так работает, когда будет осуществлена проверка для этого кода.
Это очень просто.

1. Выполняется INNER JOIN. То есть, соединяются каждая строка левой таблицы с каждой строкой правой таблицы,
для которых условие в ON возвращает TRUE. (Замечу, что возможны три результата - FALSE,TRUE и UNKNOWN)
2. К результату добавляются все записи левой таблицы, которых не оказалось в результате п.1.
Значения всех полей правой таблицы заменяются на NULL.
3. К результату добавляются все записи правой таблицы, которых не оказалось в результате п.1.
Значения всех полей левой таблицы заменяются на NULL.
4. Если есть WHERE, из результата выбрасываются все записи, для которых условие WHERE не даёт TRUE.

UNKNOWN легко получить, например, сравнив NULL с чем-нибудь, или вычисляя выражение,
в котором один из операндов равен NULL.
10 окт 11, 14:33    [11413007]     Ответить | Цитировать Сообщить модератору
 Re: Как работает объединение FULL JOIN, с условиями  [new]
pochemu_eto_iap_ne_prav?
Guest
trew
У меня в первом посте написано: Нужно получить...


хи-хи, ну так тогда iap был прав, ТС заказал правое соединение.
это уже глядя на его последний full join из первого поста следует больше
10 окт 11, 14:38    [11413040]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить