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

Откуда:
Сообщений: 663
Доброго времени суток ALL.
Помогите разобраться. Запрос вида

Select t1.f1, t2.f1 From t1, t2 where t1.f1=t2.f1

и запрос вида
Select t1.f1, t2.f1 From t1 inner join t2 on t1.f1=t2.f1

Выдадут один и тот же результат объясните мне зачем тогда нужен
inner join? Тем более "читабельность" запроса c inner join при усложнении самого запроса тоже усложняется.
26 май 04, 13:01    [701722]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Вербняков Александр
Member

Откуда: г.Таганрог, Ростовская область
Сообщений: 498
Разные версии языка, первая конструкция в более ранних реализациях SQL последняя в более поздних
26 май 04, 13:04    [701739]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Тем более "читабельность" запроса c inner join при усложнении самого запроса тоже усложняется.


Спорное утверждение...
26 май 04, 13:05    [701746]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Alexander Popov
Member

Откуда:
Сообщений: 663
Так вот и хочется понять зачем было городить огород если можно и без inner join работать
26 май 04, 13:05    [701752]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Alexander Popov
Member

Откуда:
Сообщений: 663
2 pkarklin
Возможно спорное если применять Left join или Right Join.
26 май 04, 13:09    [701775]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31776
Мне кажется, с inner Join понятнее, особенно если у вас десяток таблиц джойница. Аккуратный списочек.
select ......
from Table1
  join Table2 on ....
  join Table3 on ....
  join Table4 on ....
  join Table5 on ....
  join Table6 on ....
  join Table7 on ....
  join Table8 on ....
  join Table9 on ....
where <условия на Table1>
26 май 04, 13:10    [701779]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
paparome
Member

Откуда: Москва
Сообщений: 4312
А вы попробуйте штук 10 таблиц поджойнить (да если еще и по нескольким полям джойн идет), а потом все уcловия перетащить во where и заменить inner join на запятые

И посмотрите на читабельность where
26 май 04, 13:13    [701803]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Alexander Popov
Member

Откуда:
Сообщений: 663
Select * From 
Table1, 
Table2, 
Table3, 
Table4, 
Table5, 
Table6, 
Table7, 
Table8
 
where  Table1.f1=Table2.f1
          ....
          ....

Список тоже вполне аккуратный
26 май 04, 13:13    [701806]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Возможно спорное если применять Left join или Right Join.


Ну, почему же!? С помощью JOIN указываем как объединять таблицы, а в WHERE как фильтровать. Все красиво.
26 май 04, 13:14    [701810]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
С.Лютый
Guest
ну так в чем проблема? это же замечательно, когда есть выбор. Используйте любимый (удобный) вариант и все
26 май 04, 13:15    [701818]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Вербняков Александр
Member

Откуда: г.Таганрог, Ростовская область
Сообщений: 498
Кстатит построитель запросов пользуется именно join , это наверное очень весомый аргумент, потому что небольшие запросы можно быстро на нём ваять или строить большие join, а потом накладывать case условия ... и я согласен что намного удобнее когда в WHERE только условия отбора но не объединения
26 май 04, 13:16    [701821]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Alexander Popov
Member

Откуда:
Сообщений: 663
Обычно двух абсолютно одинаково оптимальных решений не бывает всегда чем то лучше одно чем то другое целью топика было выяснить плюсы и минусы.
26 май 04, 13:18    [701830]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37117
автор
Так вот и хочется понять зачем было городить огород если можно и без inner join работать

Вообще-то join'ы - это ANSI стандарт.
26 май 04, 13:21    [701853]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
Alexander Popov
Member

Откуда:
Сообщений: 663
А что разве условие where не ANSI стандарт?
26 май 04, 13:23    [701858]     Ответить | Цитировать Сообщить модератору
 Re: inner Join  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Alexander Popov
Тем более "читабельность" запроса c inner join при усложнении самого запроса тоже усложняется

Позвольте не согласиться.

  • Джойны используются для того, чтобы показать условия связывания таблиц, участвующих в запросе. Это часть FROM.

    Фильтры для данных пишутся в части WHERE.

    Это два разных понятия, разумно разнесенные в разные части запроса. Это как раз повышает читабельность.

  • Использование *= и =* - нестандартный синтаксис. В старых версиях MS написал свои "звездочки", теперь он от них отказывается (см. BOL) в пользу ANSI стандарта.

  • Джойнов больше, чем три типа - inner, left outer, right outer. Еще есть full outer и cross. Гораздо удобнее всегда связывать таблицы одним и тем же способом - джойном -, чем помнить, что для такого-то типа джойнов надо писать со звездочкой, для такого-то - с полным именем джойна. К тому же нельзя смешивать старый и новый синтаксис в одном запросе, поэтому иногда без джойнов обойтись невозможно.
    -------------------------------
    А вообще - пока звездочки не отменили, пишите, пишите... Никто же вам не запрещает.
  • 26 май 04, 13:25    [701868]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    Alexander Popov
    Member

    Откуда:
    Сообщений: 663
    Спасибо всем большое. Было очень интересно выслушать ваши мнения.
    Лично для меня точки над i расставлены.
    26 май 04, 13:28    [701885]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37117
    автор
    А вообще - пока звездочки не отменили, пишите, пишите... Никто же вам не запрещает.

    И будьте готовы все переписать, когда отменят.
    26 май 04, 13:28    [701886]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    SandalTree
    Member

    Откуда: Перехлёсток восьми батог
    Сообщений: 28146
    Вот по ходу дела столкнулся с невозможностью (или с моим непониманием) построить запрос на JOIN.

    select * 
    INTO #u
    from (
    Select 1 rid,'Вася' name
    Union
    Select 2 rid,'Фёдор' name
    Union
    Select 3 rid,'Оля' name
    Union
    Select 4 rid,'Наташа' name
    ) u
    
    select identity(int, 1,1) as id, * 
    INTO #t
    from (
    Select 0 fl, 1 uid, 3 sale  
    Union
    Select 0 fl, 1 uid, 2 sale  
    Union
    Select 0 fl, 2 uid, 4 sale
    Union
    Select 1 fl, 2 uid, 1 sale
    Union
    Select 1 fl, 2 uid, 5 sale
    Union
    Select 1 fl, 3 uid, 5 sale
    Union
    Select 1 fl, 3 uid, 6 sale
    ) t

    Вот такой код работает (правда ругается):
    select #u.name, count(#t.id) Sales
    from #u, #t
    where #u.rid *= #t.uid
    	and fl = 0
    	and #u.rid = 3
    GROUP BY #u.name

    Тот-же код но с Join нет:
    select #u.name, count(#t.id) Sales
    from #u
    	left JOIN #t on #u.rid = #t.uid
    where fl = 0 and 
    	#u.rid = 3
    GROUP BY #u.name

    Или я чего не понимаю?
    28 май 04, 00:04    [706470]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    ChA
    Member

    Откуда: Москва
    Сообщений: 11201
    Где-то в недрах BOL описано раздичие между использованием "*" и
    OUTER JOIN-ов
    В запросе
    SandalTree

    select #u.name, count(#t.id) Sales
    from #u
    left JOIN #t on #u.rid = #t.uid
    where fl = 0 and
    #u.rid = 3
    GROUP BY #u.name

    условие fl = 0, где fl есть #t.fl, превращает запрос в INNER JOIN.
    Не уверен, что автор именно этого и добивался :)
    28 май 04, 02:59    [706567]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    gvorl
    Member

    Откуда:
    Сообщений: 1
    А вот так:
    select #u.name, count(#t.id) Sales
    from #u
    left JOIN #t on #u.rid = #t.uid and fl = 0 
    where #u.rid = 3
    GROUP BY #u.name
    
    28 май 04, 06:59    [706616]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    GreenSunrise
    Member

    Откуда:
    Сообщений: 12310
    Для понимания разницы в результатах надо посмотреть на планы выполнения.

    Давайте рассмотрим запрос, убрав из него группировку, чтобы увидеть, какие строки вообще возвращаются в первом и втором варианте.

    Первый вариант (*=)
    select *
    from #u, #t
    where #u.rid *= #t.uid
    	and #t.fl = 0
    	and #u.rid = 3
    
    rid         name   id          fl          uid         sale        
    ----------- ------ ----------- ----------- ----------- ----------- 
    
    3 Оля NULL NULL NULL NULL

    Второй вариант (left join)
    select *
    from #u
    	left JOIN #t on #u.rid = #t.uid
    where
    	#t.fl = 0
    	and #u.rid = 3
    
    rid         name   id          fl          uid         sale        
    ----------- ------ ----------- ----------- ----------- ----------- 
    

    Теперь рассмотрим планы выполнения запросов:
    Первый:
      |--Nested Loops(Left Outer Join, WHERE:([#t].[uid]=[#u].[rid]))
    
    |--Table Scan(OBJECT:([tempdb].[dbo].[#u____000000000015]), WHERE:([#u].[rid]=3))
    |--Table Scan(OBJECT:([tempdb].[dbo].[#t____000000000015]), WHERE:([#t].[fl]=0))

    Второй:
      |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([#t].[uid])=([#u].[rid]), RESIDUAL:([#t].[uid]=[#u].[rid]))
    
    |--Sort(ORDER BY:([#t].[uid] ASC))
    | |--Table Scan(OBJECT:([tempdb].[dbo].[#t____000000000015]), WHERE:([#t].[fl]=0))
    |--Table Scan(OBJECT:([tempdb].[dbo].[#u____000000000015]), WHERE:([#u].[rid]=3))

    В первом случае сначала была выборка из таблицы #t с условием [#t].[fl]=0), потом выборка таблицы #u с условием [#u].[rid]=3, потом Left Outer Join между ними. Потому "Оля" и попала в конечный результат.

    Во втором сравнение #u.rid и #t.uid происходило в Merge Join операторе, причем выбран Inner Join, как показано в плане выполнения. Потому и ни одной строки в конечный результат не попало.

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

    Посмотрим на выборку без фильтрации:
    select *
    from #u, #t
    where #u.rid *= #t.uid
    
    select *
    from #u
    	left join #t on #u.rid = #t.uid
    
    Результаты ОДИНАКОВЫЕ:
    
    rid         name   id          fl          uid         sale        
    ----------- ------ ----------- ----------- ----------- ----------- 
    
    1 Вася 1 0 1 2 1 Вася 2 0 1 3 2 Фёдор 3 0 2 4 2 Фёдор 4 1 2 1 2 Фёдор 5 1 2 5 3 Оля 6 1 3 5 3 Оля 7 1 3 6 4 Наташа NULL NULL NULL NULL

    То есть сам по себе left join работает ОДИНАКОВО СО СТАРЫМ И НОВЫМ СИНТАКСИСОМ.

    Дальнейшие же действия зависят от того, что ВЫ понимаете под правильной фильтрацией #t.fl = 0 and #u.rid = 3.

    Если выборку из вышеприведенного результата по данному условию (вернется ноль строк), то ставим #t.fl = 0 and #u.rid = 3 во WHERE.
    Если джойн и фильтрацию вы хотите совместить, то вносите дополнительное условие в JOIN, как вам и предложил gvorl:
    select *
    from #u
    	left JOIN #t on #u.rid = #t.uid and #t.fl = 0
    where
    	#u.rid = 3
    

    Посмотрите на эти два запроса и их результаты:
    select *
    from #u
    	left JOIN #t on #u.rid = #t.uid
    where
    	#t.fl = 0
    
    rid         name   id          fl          uid         sale        
    ----------- ------ ----------- ----------- ----------- ----------- 
    
    1 Вася 1 0 1 2 1 Вася 2 0 1 3 2 Фёдор 3 0 2 4
    select *
    from #u
    	left JOIN #t on #u.rid = #t.uid and #t.fl = 0
    
    rid         name   id          fl          uid         sale        
    ----------- ------ ----------- ----------- ----------- ----------- 
    
    1 Вася 1 0 1 2 1 Вася 2 0 1 3 2 Фёдор 3 0 2 4 3 Оля NULL NULL NULL NULL 4 Наташа NULL NULL NULL NULL

    Отсюда и разница в поведении.
    Прошу прощения, что объяснение такое многословное, но без примеров было не обойтись.
    28 май 04, 14:44    [708145]     Ответить | Цитировать Сообщить модератору
     Re: inner Join  [new]
    SandalTree
    Member

    Откуда: Перехлёсток восьми батог
    Сообщений: 28146
    2 gvorl & GreenSunrise

    Огромное спасибо.
    28 май 04, 17:49    [708841]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить