LEFT JOIN vs RIGHT JOIN

Някой ще ме просветли ли разликата м/у ляв и десен join каква е. Едното по бързо ли е от другото, кое при кое се ползва примерно и т.н.
Аре без RTFM....Чел съм туй онуй и затова питам за да стане всичко ясно.
Благодаря предварително.

Разликата между

a LEFT JOIN b
и
b RIGHT JOIN a

е точно каквато е разликата между
a > b
и
b < a


Едното е огледален образ на другото. Затова е най-добре да си харесаш само единия и да не ползваш и двата. Не за друго а за да не става объркване

salle.online.bg/sig

Да разбирам ли че няма разлика в времето за изпълнение на заявката?
Защото при 2 еднакви заявки и условия, времето за изпълнение на right е много по малко(по-бързо е ) от тази с left.
На какво може да се дължи това?

Не дефинирам какво значи много по-бързо, защото зависи от сложността на база и т.н. -> но е видима разликата във времето.

я ги пусни тея 2 заявки да видим колко са еднакви?

Примерно нещо такова. Забавих защото тези твърдения са от познат и трябваше да изчакам да ми даде заявките.

select t1.*, t2.field12 from table1 t1 left join table2 t2 on(t1.id = t2.parent_id) where t1.sum > 0 and t2.date is not null

select t1.*, t2.field12 from table1 t1 right join table2 t2 on(t1.id = t2.parent_id) where t1.sum > 0 and t2.date is not null

Да не би все пак да трябва да прочетеш още веднъж каква е разликата между двата вида OUTER JOIN?

salle.online.bg/sig

Като казваш:

table1 Някакъв join table2,

има значение коя таблица е отляво и коя отдясно в израза.. оттук идва и проблемът ти;)

ми не са две еднакви заявки, ама хич. :)
left join e същото като right join, но само ако се разменят местата на таблиците, иначе не е.
може да ти стане по-ясно а може и да те обърка допълнително, но пробвай да сложиш по едно EXPLAIN пред всяка от заявките и разгледай резултата. има там една колонка rows, най-грубо казано, колкото по-големи са числата там, толкова по-бавна е заявката.

п.с. плз не ме почвайте сега за другите причини да е бавно едно или друго, знам че броя редове не е единствения индикатор за проблеми и че е относителен и че трябва да се гледа в комплект с всичко останало и т.н. не знам дали пича е готов за подобна лекция просто, но барем да почне да го пуска чат-пат :)

"пича" е готов и ги ползва тези неща :)
Както казах един познат ми спореше на тези тема. И на мен не ми изглеждаха еднакви...питах го изрично няма ли да сменим имената на таблицата защото така няма много смисъл....той каза че си е така. И за да му покажа реших да постна темата.
Благодаря за всички отговори....

би трябвало да са еднакво бързи. Ако не са (и има фрапираща разлика, зависима от броя на записите в двете таблици), е глупаво (като реализация от СБД) - защото всеки LJ може да се сведе до RJ, както и обратно.
Практически - никога не ми е хрумвало да ги сравнявам като бързодействие при различни СБД.

Сега погледнах какво сте си писали по-горе. Ми те не са ви еднакви заявките, бе колега...
Както правилно са ви посочили a>b не е същото като a<b
a>b съответства на b<а
Ама си мислех, че това е очевидно и не е нужно да се обяснява.... А явно не е било така.

When the mountain is high
Just look up to the sky

има ли разлика между left_join i select s1,s2,s3 from t1,t2,t3
koe e po dobre da polzvam

mysql> explain select userid,payment_code from users,payments where vip = 1 and userid = user_id and payment_type = 'vip';
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | payments | ALL | user_id | NULL | NULL | NULL | 2287 | Using where |
| 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 3 | bgfree.payments.user_id | 1 | Using where |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------------+
2 rows in set (0.01 sec)

mysql> explain select userid,payment_code from users left join payments on (userid = user_id) where vip = 1 and payment_type = 'vip';
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | payments | ALL | user_id | NULL | NULL | NULL | 2287 | Using where |
| 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 3 | bgfree.payments.user_id | 1 | Using where |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql>

При left join би трябвало филтрирането на редовете от отделните таблици да е преди обединяването на данните от тях.

При cross-join и select ... from t1,t2,t3 обединяването на данните е пълна комбинация от всички редове от всяка от таблиците, след което се извършва филтриране според where клаузите.

Възможно е някои СБД да оптимизират cross-join заявките до left и right join заявки, но аз лично ще ти препоръчам да си указваш изрично типа на комбиниране на записите от таблиците, по следните причини:
Неоптимизиран (вътрешно от СБД) cross-join в сравнение с left outer/right outer join:
1. ползва повече памет за временни резултати от изпълнение на заявката
2. извършва повече сравнения, тъй като филтрирането се извършва след, а не преди комбинирането на редовете от таблиците.
3. много по-трудно се проследява програмна логика (left outer join автоматично означава релация тип "едно-към-много", респективно right outer join е "много-към-едно"), докато при cross-join-а тези връзки не са явни и трябва да се разглежда where клаузата под лупа.

Представи си cross-join от 9 таблици - 1 главна, 2 подчинени и 6 lookup (т.е. съдържащи еквивалети на кодове/идентификатори с имена/символни низове) как ще изглежда и представи си колко по-ясно ще изглежда същата заявка с 8 left outer join-а.
Вярно че заявката с left outer join ще е по-дълга като текст, но ще е много по-лесно да се ориентираш в нея за какво става въпрос, отколкото в cross-join-а.
Не дай боже да се наложи да правиш и union на два, три и повече резултата от такива заявки.

Освен това при left outer join също можеш да имаш where, но там вече ще сложиш условията за филтриране на резултата от join-а.


Сега се сетих, че ако нямаш еквивалентен ред за даден идентификатор от главната таблица в подчинената/lookup таблицата, то при cross-join ще загубиш този ред, тъй като няма да имаш редове с идентификатор null за подчинената таблица.
Т.е. ако потребителя си е изтрил по погрешка всички редове в подчинената, или еквивалентния ред в lookup таблицата, то този ред от главната таблица, няма да бъде изведен при cross-join с последващ where филтър.

Примерно имаш общи данни за фактура с даден номер, и нямаш нито една позиция все още по тази фактура (т.е. потребителя още не е попълнил редовете с данни за фактурата).
В този случай, ако поиска справка за фактурите заедно с позициите по тях, няма да можеш да покажеш тази фактура и да кажеш че по нея няма позиции ако ползваш cross-join.

Друг пример - имаш таблица която ти дава съответствия по пощенски код и населено място. Ако ползваш пощенския код за идентификатор на населеното място (в главната таблица) и в случай че за дадения пощенски код нямаш съответен в таблицата със съответствия, то при cross-join ще загубиш този ред от главната таблица, докато при left outer join ще имаш null, като име на населеното място.

Накратко казано cross join с where t1.id = t2.id е еквивалентен на inner join, като резултат, но като изпълнение е много по-бавен.

За да реализираш еквивалент на left outer join с cross join-и, трябва да ползваш union и подзаявка с not exists() като условие за нея.
т.е. Обедини (union) всички записи за които t1.id = t2.id с тези записи от t1, за които няма ред в t2, с id = t1.id
За да мине union-а трябва да зададеш изрично null As t2.column1, null As t2.column2 за всяка от колоните от t2, които ти участват в заявката.
Надявам се след това последното да ти е станала поне малко ясна разликата между двете.

Не знам за него ама и на мен ми стана ясно :)
По принцип 99% от селектите ми са макс 2 таблици. Ама може би е добра практика да се ползва left joint. По добър стил, по добра четимост (ускоряването едва ли ще бъде забележимо в моите случаи).

Bеer? Mоre?
Плацебото

Не знам дали осъзнаваш, че току що наговори куп неверни неща.

SQL като език е декларативен. Със SELECT описваш вида на крайния резултат, а не стъпките по които той да се получи. Повечето db engine-и след парсване на заявката я оптимизират с цел да генерират план (стъпка по стъпка) за изпълнение и оптимален достъп до данните. Точно тук всички твои хипотези как CROSS JOIN се изпълнявал първо, после се филтрирало отиват в канала по простата причина, че в SQL няма начин да укажеш последователността на стъпките за изпълнение на заявката.

Обикновено db engine-а отрязва от изпълнение всички глупости, които програмиста натворява (например JOIN с таблица, колони на която не присъстват в SELECT-а), "изтласква" всички предикати "нагоре" максимално близко до base таблицити и преглежда от кои индекси има файда (щото от повечето творения на родните DBA няма такава).

От тази гледна точка INNER JOIN е еквивалентен на CROSS JOIN със същия предикат в WHERE клаузата. Просто второто за мен е sql обфускация :-))

cheers,
</wqw>


[линк]

Така, отвори дебелите книги за кой да е СБД (MS SQL, Oracle, Postgre SQL) и ще видиш, че условията в JOIN имат приоритет пред условията в WHERE.
Т.е. JOIN-а се извършва винаги преди да се проверят WHERE условията.
Пределно ми е ясно че MS SQL Server (а вероятно и други СБД) си прави execution plan преди да почне да обработва дадена SQL заявка.

Да, също така знам, че ANSI SQL е декларативен, но ако ще се заяждаме има и SQL разширения които малко или много започват да се различават от дефиницията за декларативен език.

Прочети ми мнението още веднъж и виж, че съм казал, че някои СБД могат да оптимизират Cross Join заявки, и това което съм написал е "по принцип", т.е. разбирай на теория, според това което пише по разни книги за SQL.

Поздрави


П.П. Ще ти препоръчам да прочетеш
SQL Optimization And ON Clause vs WHERE Clause
където е правен практически тест, и наистина няма съществена разлика в бързодействието и е казано
the SQL Server does a wonderful job of optimizing the statements prior to execution


И един друг пример, в които се вижда разликата между условията след where и след ON клаузите:
Additional Criteria in the JOIN Clause
The WHERE clause acts on the results after the JOIN has been completed

Ако ти се занимава пробвай да видиш какво е бързодействието при реализация на рекурсивен join (таблица самa към себе си) с INNER JOIN и с CROSS JOIN.


В Интернете много психов,
Каждый пятый - это псих.
Говори со мною тихо,
Может я один из них

Какво значи "JOIN-а се извършва винаги преди да се проверят WHERE условията."? Стъпките на execution plan-а не са 1-към-1 със самата заявка. Ако си представим заявката като дърво, на което листа са обектите от базата (обикновено таблици или индекси), предикатите от WHERE клаузите (да, няколко) и ON клаузите се "избутват" по-близо до листата, като трансформациите с които това се прави гарантират еквивалентност на резултата.

Обърни внимание на еквивалентност. Естествено че предикат от ON клаузата на OUTER JOIN не може да слезе в WHERE клаузата -- просто двете заявки няма да са еквивалентни. Но ето тези две еквивалентни заявки

SELECT      *

FROM        authors a1

JOIN        authors a2

ON          a1.au_id = a2.au_id



SELECT      *

FROM        authors a1

CROSS JOIN  authors a2

WHERE       a1.au_id = a2.au_id

... генерират абсолютно еднакъв резултат и още повече -- абсолютно еднакъв execution plan.

Прочетох втори път мнението ти и това което си написал именно "на теория" не е вярно. На практика обаче е възможно е да има скодоумни db engine-и, които действат по описания от тебе начин :-))

А в примера от Additional Criteria in the JOIN Clause е ясно че заявките не са еквивалентни -- това с предикат в ON на LEFT JOIN няма нищо общо със същия предикат в WHERE клаузата. Къде остава да си говорим за performance въобще!

Виж това за "рекурсивния" JOIN изби рибата :-)) Хората се затрудняват да ползват alias-и явно. Но и там няма проблем за engine-а да оптимизира (виж горните "рекурсивни" заявки). Btw, за рекурсивност можем да говорим само в CTE от ANSI SQL'99 (или MSSQL2005).

Относно така споменатите SQL разширения, които престават да са декларативни. Толкова по-зле за тях! Колкото по-декларативен е езикът, толкова по-добре engine-а ще оптимизира execution плана. Това е идеята -- vendor-ите да се "състезават" по съвършени engine-и, а не да ме дърпат във vendor lock с proprietary extension-и за обхождане на йерархии (oracle) или парсване на XML (MS).

cheers,
</wqw>


[линк]

Така... за да спрем по-нататъшен безсмислен спор:
1. Условие в ON клауза винаги има приоритет (изпълнява се първо) пред условие в WHERE клауза.
2. Execution планът обикновено е в резултат на оптимизиране на заявката, преди нейното реално изпълнение.

П.П. Това за рекурсивния join ти го дадох като идея за тестване на performance, защото на мен лично ми е по-просто да тествам с една таблица.

Ако с нещо от написаното в 1 или 2 не си съгласен - казвай. Хората са казали, че в спора се ражда истината.

В Интернете много психов,
Каждый пятый - это псих.
Говори со мною тихо,
Может я один из них.

Конструктивно, но... невярно. Какво значи приоритет? Ето с тези заявки ми обясни:

SELECT      *

FROM        authors a1

JOIN        authors a2

ON          a1.au_id = a2.au_id

WHERE       a1.au_lname = 'Green'



SELECT      *

FROM        authors a1

JOIN        authors a2

ON          a1.au_lname = 'Green'

WHERE       a1.au_id = a2.au_id

Щото аз знам какво е приоритет на AND пред OR или на скобите, но на ON пред WHERE не ми е ясно.

Смяташ ли, че предиката a1.au_lname = 'Green' в WHERE клаузата не се изпълнява преди ON клаузата?

Щото тука при мене execution plan-а на тия заявки е еквивалентен и съответно и резултатът е еквивалентен.

cheers,
</wqw>


[линк]

MS SQL Server ги оптимизира до една и съща.

Смислово и по предназначение ON и WHERE са различни.

[линк]

Прочети тази статия пак - там е обяснено. Спирам да ти пиша по тази тема.

В Интернете много психов,
Каждый пятый - это псих.
Говори со мною тихо,
Может я один из них.

Добре. Но ако пак някъде ще говориш за приоритети добре е да се запознаеш с [линк] и да престанеш да четеш trade press :-))

cheers,
</wqw>


[линк]