Где-то на форуме был вопрос типа
'как правильно и/или что быстрее:  WHERE, JOIN, и т.д.'
(помнится даже отвечал на него, но теперь оказывается - не совсем правильно)

Недавно передо мной возник этот же вопрос......

Поэкспериментировав, почитав статейки из инет,
хочу поделится своими выводами....

Критика принимается  

ЗАДАЧА:

     хотим получить результат из табл.А, наложив доп.условие на реквизит А,
     который 'живет' в табл.Б
     и при этом на таблицу Б накладываем еще одно некоторое доп.условие ч/з WHERE

		SELECT А.ID
		FROM A
		{ INNER | LEFT,RIGHT OUTER | FULL } JOIN А.РевизитСсылка = Б.ID
		WHERE (.... дополн.фильтр по Б)
 

ВАРИАНТЫ ЗАПРОСОВ (как результат курения планов + чтения статей из интернет):

                       http://stackoverflow.com/questions/2389204/left-inner-join-vs-left-outer-join-wh...
                       http://www.skillz.ru/dev/php/article-Obyasnenie_SQL_obedinenii_JOIN_INNER_OUTER....
                       http://bytes.com/topic/sql-server/answers/82859-sql-syntax-inner-outer-join-vs-w...
                       http://ru.wikipedia.org/wiki/Join_(SQL)

     получил такое:

     INNER:            
           1. сперва сканируется Б (по его фильтру из WHERE) и формируется <Список Б.ID>
           2. из А выбираются только те записи, для которых {А.РевизитСсылка = УжеОтобранныеЗаписиБ.ID}
           т.е. во втором запросе (при сканировании А)
           собственно JOIN-условие <А.РеквА = Б.РеквБ>
           как бы превращается в WHERE-условие <А.РеквА IN (уже отобранные Б.ID)>

     LEFT OUTER:      
           (запрос INNER) в полном объеме, см.выше
           (запрос OUTER А) сканируется А и выбираются те записи, для которых {А.РевизитСсылка = NULL}
           (UNION) и его результат присоединяется к первому сканированию

     RIGHT OUTER:      
           (запрос INNER) в полном объеме, см.выше
           (запрос OUTER Б) сканируется Б и выбираются те записи, что не имеют отношения к А (связь = NULL)
           (UNION) результаты 2х сканирований объединяются (UNION)

     FULL:      
           (запрос INNER) в полном объеме, см.выше
           (запрос OUTER А) сканируется А и выбираются те записи, что не имеют отношения к Б (связь = NULL)
           (запрос OUTER Б) сканируется Б и выбираются те записи, что не имеют отношения к А (связь = NULL)
           (UNION) все три результата объединяются через UNION

РЕЗУЛЬТАТ:

     СКОРОСТЬ (при равных условиях WHERE и условиях связи 'А.РевизитСсылка = Б.ID')

     INNER      - самый быстрый (2 простых последовательных запроса ч/з WHERE: фильтр-2 накладываем на результат фильтра-1)
     OUTER      - 2е место (все запросы INNER + 1 запрос на NULL + всё объединяем ч/з UNION)
     FULL      - 3е место (все запросы INNER + 2 запроса на NULL + всё объединяем ч/з UNION)

     Причем чем меньше величина отношения количества записей

           У = (кол-во отобранных ч/з INNER) / (Всего записей А + Всего записей Б)

     тем существенней разница между скоростью INNER (быстрее) vs OUTER|FULL (медленней)

ВЫВОДЫ:
     
1) INNER - самый быстрый из всех JOIN-ов, без надобности OUTER|FULL - не использовать !
2) естественно WHERE быстрее JOIN, т.к. WHERE это запрос по 1й таблице, а JOIN по 2вум (и более)

P.S. иногда внутреннее соединение пишут так
        (и надо понимать что это запрос к 2м таблицам, а не простой WHERE)

		SELECT А.ID
		FROM A
		WHERE (А.РевизитСсылка = Б.ID)
		AND (.... дополн.фильтр по Б)

	вместо (правильно, хотя первый вариант - не запрещается 8=))

		SELECT А.ID
		FROM A
		[INNER] JOIN А.РевизитСсылка = Б.ID
		WHERE (.... дополн.фильтр по Б)
 

Eprst писал(а) 30. Августа 2013 :: 09:28:


Т.е. правильно ли я понял, что:

INNER - это вот это:

// выбрать все товары, если 'ОсновнойСклад' товра обслуживает менеджер 'ВыбМенеджер'

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Товары AS А
INNER JOIN $Справочник.Клиенты AS Б ON Б.ID = А.ОсновнойСклад
WHERE ($Б.ОсновнойМенеджер = :ВыбМенеджер)

 

CROSS - тот же INNER, но без WHERE (теоретический термин, без конструкции в T-SQL):
ДЛЯ МЕНЯ ОТКРЫТИЕ   ЧТО CROSS - это из T-SQL .....
(здесь сравнение CROSS & INNER : http://msdn.microsoft.com/en-us/library/ms190690(SQL.100).aspx)

// выбрать все товары, у которых 'ОсновнойСклад' назначен (не пустой)

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Товары AS А
CROSS JOIN $Справочник.Клиенты AS Б ON Б.ID = А.ОсновнойСклад
// хотя результат с INNER будет тот же,
// но оптимизатор наверное отработает качественней 8))...
 

Если так, то предположу что стадии оптимизатора такие:

CROSS - только декартово произведение (А*Б)
INNER - две стадии: тот же CROSS + фильтрация WHERE,
       причем последовательность оптимизатора (наверное) такая:

      1) если кол-во записей А больше чем Б то
         сперва WHERE по Б (по меньше таблице)
         а потом CROSS: произведение <результат_Б * А>

      2) если кол-во записей А меньше чем Б
         сначала CROSS: произведение <Б * А> + дополнительное поле для Б
         а потом WHERE <.... дополн.фильтр из РезультатКросс.ПолеДляWhere>      

Думаю, так логично, хотя не проверял...
Это как бы для теории.
Но в любом случае:на работу оптимизатора мы повлиять не можем (текстом запроса).

UNION:

Eprst писал(а) 30. Августа 2013 :: 09:28:


Не совсем понял...

Можно пример - как заменить CROSS|INNER на UNION.
Собственно - не понял вот что:
как заменить "произведение" на "сложение"

А скорость UNION vs X мы померяем, но  - потом.

Eprst писал(а) 30. Августа 2013 :: 10:34:


т.е. так?

INNER(cross)

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Товары AS А
INNER JOIN $Справочник.Клиенты AS Б ON Б.ID = А.ОсновнойСклад
 

что то же самое что и

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Товары AS А,$Справочник.Клиенты AS Б
WHERE Б.ID = А.ОсновнойСклад
 

LEFT + WHERE

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Товары AS А
LEFT JOIN $Справочник.Клиенты AS Б ON Б.ID = А.ОсновнойСклад
WHERE (А.ОсновнойСклад <> NULL)
// или $ПустойИД9 ?  

Похоже что скорость выполнения запроса измениться,
но (думаю) в случае LEFT+WHERE: в худшую сторону (исходя из логики стадий, что постами выше)......
Если товаров больше чем клиентов.....

Eprst писал(а) 30. Августа 2013 :: 11:20:


А, ну да. Запросы дадут одинаковый результат:

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Товары AS А
LEFT JOIN $Справочник.Клиенты AS Б ON Б.ID = А.ОсновнойСклад
WHERE (А.ОсновнойСклад <> NULL)
// или $ПустойИД9 ?
 

SELECT А.ID [Товар $Справочник.Товары]
, Б.ID [Склад $Справочник.Клиенты]
FROM  $Справочник.Клиенты AS Б
LEFT JOIN $Справочник.Товары AS А ON Б.ID = А.ОсновнойСклад
WHERE (Б.ID <> NULL)
// или $ПустойИД9 ? 

Тогда нужно (еще запрос?) сперва посчитать кого больше: Клиентов или Товаров
и выполнить
Запрос WHERE (Б.ID <> NULL) - если товаров больше (=== таблица Б меньше)
Запрос WHERE (А.ОсновнойСклад <> NULL) - если клиентов больше (=== таблица А меньше)

НО: ту же самую работу (подсчет "кого больше")
выполняет оптимизатор на сервере - а это быстрее чем
1) клиент-сервер: запрос "кого больше"
2) клиент-сервер: запрос "выборка результатов"

Поэтому: INNER|CROSS - будет быстрее чем LEFT+WHERE

Хотя не замерял, но логически, думаю - я прав.

kos писал(а) 30. Августа 2013 :: 10:19:


Сорри, туплю: здесь тоже понятно, ведь арифметика простая:

8 = {2} * 4 = {2} + {2} + {2} + {2}

т.е.

SELECT A.ID
FROM A
JOIN B ON B.ID = A.Рекв1
JOIN C ON C.ID = A.Рекв2
JOIN D ON D.ID = A.Рекв3
WHERE (B.Рекв6 = ВыбРекв6)
AND (C.Рекв7 = ВыбРекв7)
AND (D.Рекв8 = ВыбРекв8)
 

это все равно что НЕВЕРНО! см.следующий пост

SELECT A.ID
FROM A
WHERE A.Рекв1 IN (SELECT B.ID FROM B WHERE (B.Рекв6 = ВыбРекв6))

UNION

SELECT A.ID
FROM A
WHERE A.Рекв2 IN (SELECT C.ID FROM C WHERE (C.Рекв7 = ВыбРекв7))

UNION

SELECT A.ID
FROM A
WHERE A.Рекв3 IN (SELECT D.ID FROM D WHERE (D.Рекв8 = ВыбРекв8))
 

Можем замерять скорость. Кто хочет?  
НЕВЕРНО! см.следующий пост

Все это, конечно, замечательно, но
а) о каком скуле идет речь?
б) в любом случае, оптимизатор скуля - вещь настолько загадочная, что далеко не всегда можно предугадать, что будет эффективнее.

Например, на 2005 скуле в простых случаях план запроса с явным Иннер-ом и неявным в виде "Лефт-джойн + условия  по левой короче, по присоединяемой таблице" будет просто одинаковый.
А на 2000 скуле,помниццо, в каких-то случаях оптимизатору на Иннер-ах вообще сносило башню, в результате чего у меня появилась привычка практически всюду использовать неявные Иннеры через лефт+условие.

Цитата:


Я знаю только методу замены фулл-джойна на Юнион для такой стандартной ситуации:

2 регистра остатков (скажем, материалы на складах и в переработке), нужно вывести данные по всем материалам, существующим хотя бы на одном из них.

Решение на фулл джойне:

Select
   IsNull(регОст.Материал, рег.Материал) Материал,
   IsNull(регОст.Количество, 0) + IsNull(рег.Количество) Количество
From
   регОст
   Full Join рег on регОст.Материал = рег.Материал 

Решение на Юнионе:

Select
   регОст.Материал
   регОст.Количество,
   0 Колво
From
   регОст

Union All

Select
   рег.Материал,
   0,
   рег.Количество
From
   рег 

И Групбай сверху