inner join vs outer join
Вътрешно присъединяване срещу външно присъединяване: Пригответе се да изследвате точните разлики между вътрешното и външното присъединяване
Преди да проучим разликите между Inner Join Vs Outer Join, нека първо видим какво е SQL JOIN?
Клауза за присъединяване се използва за комбиниране на записи или за манипулиране на записите от две или повече таблици чрез условие за присъединяване. Условието за присъединяване показва как колоните от всяка таблица са съпоставени една срещу друга.
Присъединяването се основава на свързана колона между тези таблици. Най-често срещаният пример е свързването между две таблици през колоната на първичния ключ и колоната с външен ключ.
Да предположим, че имаме таблица, която съдържа Заплата на служителите, а има и друга таблица, която съдържа подробности за служителите.
В този случай ще има обща колона като идентификатор на служител, която ще се присъедини към тези две таблици. Тази колона с идентификационен номер на служител би била основният ключ на таблиците с подробности за служителите и външния ключ в таблицата на заплатите на служителите.
Много е важно да има общ ключ между двете единици. Можете да мислите за таблица като за обект, а за ключа като за обща връзка между двете таблици, която се използва за операция за присъединяване.
DVD извличане и записване софтуер безплатно
По принцип има два вида присъединяване в SQL, т.е. Вътрешно присъединяване и Външно присъединяване . Външното присъединяване допълнително се подразделя на три типа, т.е. Ляво външно присъединяване, дясно външно присъединяване и пълно външно присъединяване.
В тази статия ще видим разликата между Вътрешно присъединяване и Външно присъединяване подробно. Ще запазим кръстосаните и неравностойните присъединявания извън обхвата на тази статия.
Какво ще научите:
- Какво е Inner Join?
- Какво е Външно присъединяване?
- Разлика между Вътрешно и Външно присъединяване
- производителност
- MS Access Вътрешно и външно присъединяване
- Ляво присъединяване срещу ляво външно присъединяване
- Ляво външно присъединяване срещу дясно външно присъединяване
- Разлика между Вътрешно присъединяване и Външно присъединяване в табличен формат
- Вътрешно и външно присъединяване срещу съюз
- Заключение
- Препоръчително четене
Какво е Inner Join?
Вътрешно присъединяване връща само редовете, които имат съвпадащи стойности в двете таблици (тук обмисляме обединяването да се извърши между двете таблици).
Какво е Външно присъединяване?
Външното присъединяване включва съответстващите редове, както и някои от несъвпадащите редове между двете таблици. Външното присъединяване се различава основно от Вътрешното присъединяване по това, как се справя с условието за фалшиво съвпадение.
Има 3 вида външно присъединяване:
- Ляво външно присъединяване : Връща всички редове от таблицата НАЛЯВО и съответстващи записи между двете таблици.
- Правилно външно присъединяване : Връща всички редове от таблицата НАДЯСНО и съответстващи записи между двете таблици.
- Пълно външно присъединяване : Той комбинира резултата от лявото външно присъединяване и дясното външно присъединяване.
Разлика между Вътрешно и Външно присъединяване
[изображение източник ]
Както е показано в горната диаграма, има две обекти, т.е.таблица 1 и таблица 2 и двете таблици споделят някои общи данни.
Вътрешно присъединяване ще върне общата зона между тези таблици (зелената сенчеста зона в диаграмата по-горе), т.е. всички записи, които са общи между таблица 1 и таблица 2.
Ляво външно присъединяване ще върне всички редове от таблица 1 и само тези редове от таблица 2, които също са общи за таблица 1. Правото външно присъединяване ще направи точно обратното. Той ще даде всички записи от таблица 2 и само съответните съответстващи записи от таблица 1.
Освен това, Пълно външно присъединяване ще ни даде всички записи от таблица 1 и таблица 2.
Нека започнем с пример, за да направим това по-ясно.
Да предположим, че имаме две маси: EmpDetails и EmpSalary .
Таблица EmpDetails:
EmployeeID | Име на служителя |
7 | Лили |
един | Джон |
две | Саманта |
3 | Нито един |
4 | Копринено |
5 | Рам |
6 | Арпит |
8 | Сита |
9 | Фара |
10 | Джери |
Таблица на EmpSalary:
EmployeeID | Име на служителя | Заплата на служител |
---|---|---|
единадесет | Роуз | 90000 |
един | Джон | 50 000 |
две | Саманта | 120000 |
3 | Нито един | 75000 |
4 | Копринено | 25000 |
5 | Рам | 150000 |
6 | Арпит | 80000 |
12 | Сакши | 45000 |
13 | Джак | 250000 |
Нека направим Вътрешно присъединяване на тези две маси и да наблюдаваме резултата:
Запитване:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails INNER JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
Резултат:
EmployeeID | Име на служителя | Заплата на служител |
---|---|---|
7 | Лили | НУЛА |
един | Джон | 50 000 |
две | Саманта | 120000 |
3 | Нито един | 75000 |
4 | Копринено | 25000 |
5 | Рам | 150000 |
6 | Арпит | 80000 |
В горния набор от резултати можете да видите, че Inner Join е върнал първите 6 записа, които са присъствали както в EmpDetails, така и в EmpSalary със съответстващ ключ, т.е. EmployeeID. Следователно, ако A и B са две обекти, Inner Join ще върне набора от резултати, който ще бъде равен на „Записи в A и B“, въз основа на съответстващия ключ.
Нека сега видим какво ще направи лявото външно присъединяване.
Запитване:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails LEFT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
Резултат:
EmployeeID | Име на служителя | Заплата на служител |
---|---|---|
един | Джон | 50 000 |
две | Саманта | 120000 |
3 | Нито един | 75000 |
4 | Копринено | 25000 |
5 | Рам | 150000 |
6 | Арпит | 80000 |
8 | Сита | НУЛА |
9 | Фара | НУЛА |
10 | Джери | НУЛА |
В горния набор от резултати можете да видите, че лявото външно присъединяване е върнало всичките 10 записа от таблицата НАЛЯВО, т.е. таблицата EmpDetails и тъй като първите 6 записа са съвпадащи, е върнало заплатата на служителя за тези съвпадащи записи.
Тъй като останалите записи нямат съвпадащ ключ в таблицата НАДЯСНО, т.е. таблица EmpSalary, тя е върнала NULL, съответстваща на тези. Тъй като Лили, Сита, Фара и Джери нямат съответстващ идентификатор на служител в таблицата EmpSalary, тяхната заплата се показва като NULL в набора от резултати.
Така че, ако A и B са две обекти, тогава лявото външно съединение ще върне набора от резултати, който ще бъде равен на „Записи в A NOT B“, въз основа на съответстващия ключ.
Сега нека наблюдаваме какво прави правилното външно присъединяване.
Запитване:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails RIGHT join EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
Резултат:
EmployeeID | Име на служителя | Заплата на служител |
---|---|---|
НУЛА | НУЛА | 90000 |
един | Джон | 50 000 |
две | Саманта | 120000 |
3 | Нито един | 75000 |
4 | Копринено | 25000 |
5 | Рам | 150000 |
6 | Арпит | 80000 |
НУЛА | НУЛА | 250000 |
НУЛА | НУЛА | 250000 |
В горния набор от резултати можете да видите, че дясното външно присъединяване е направило точно обратното на лявото съединение. Върна всички заплати от правилната таблица, т.е. таблица EmpSalary.
Но тъй като Роуз, Сакши и Джак нямат съвпадащ идентификатор на служител в лявата таблица, т.е. таблица EmpDetails, имаме техния идентификатор на служител и име на служител като NULL от лявата таблица.
Така че, ако A и B са две обекти, тогава дясното външно съединение ще върне набора от резултати, който ще бъде равен на ‘Записи в B NOT A’, въз основа на съответстващия ключ.
Нека също да видим какъв ще бъде наборът от резултати, ако правим операция за избор на всички колони в двете таблици.
Запитване:
SELECT * FROM EmpDetails RIGHT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
Резултат:
EmployeeID | Име на служителя | EmployeeID | Име на служителя | Заплата на служител |
---|---|---|---|---|
НУЛА | НУЛА | единадесет | Роуз | 90000 |
един | Джон | един | Джон | 50 000 |
две | Саманта | две | Саманта | 120000 |
3 | Нито един | 3 | Нито един | 75000 |
4 | Копринено | 4 | Копринено | 25000 |
5 | Рам | 5 | Рам | 150000 |
6 | Арпит | 6 | Арпит | 80000 |
НУЛА | НУЛА | 12 | Сакши | 250000 |
НУЛА | НУЛА | 13 | Джак | 250000 |
Сега нека преминем към Пълното присъединяване.
Пълно външно присъединяване се извършва, когато искаме всички данни от двете таблици, независимо дали има съвпадение или не. Следователно, ако искам всички служители, дори ако не намеря съвпадащ ключ, ще изпълня заявка, както е показано по-долу.
Запитване:
SELECT * FROM EmpDetails FULL JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
Резултат:
EmployeeID | Име на служителя | EmployeeID | Име на служителя | Заплата на служител |
---|---|---|---|---|
7 | Лили | НУЛА | НУЛА | НУЛА |
един | Джон | един | Джон | 50 000 |
две | Саманта | две | Саманта | 120000 |
3 | Нито един | 3 | Нито един | 75000 |
4 | Копринено | 4 | Копринено | 25000 |
5 | Рам | 5 | Рам | 150000 |
6 | Арпит | 6 | Арпит | 80000 |
8 | Сита | НУЛА | НУЛА | НУЛА |
9 | Фара | НУЛА | НУЛА | НУЛА |
10 | Джери | НУЛА | НУЛА | НУЛА |
НУЛА | НУЛА | единадесет | Роуз | 90000 |
НУЛА | НУЛА | 12 | Сакши | 250000 |
НУЛА | НУЛА | 13 | Джак | 250000 |
Можете да видите в горния набор от резултати, че тъй като първите шест записа съвпадат и в двете таблици, имаме всички данни без никакви NULL. Следващите четири записа съществуват в лявата таблица, но не и в дясната, поради което съответните данни в дясната таблица са NULL.
Последните три записа съществуват в дясната таблица, а не в лявата, следователно имаме NULL в съответните данни от лявата таблица. Така че, ако A и B са две обекти, пълното външно присъединяване ще върне набора от резултати, който ще бъде равен на „Записи в A И B“, независимо от съответстващия ключ.
Теоретично това е комбинация от ляво присъединяване и дясно присъединяване.
производителност
Нека сравним Вътрешно присъединяване с Ляво външно присъединяване в SQL сървъра. Говорейки за скоростта на работа, лявото външно JOIN очевидно не е по-бързо от вътрешното съединение.
как да се предаде масив като параметър в java
Според дефиницията, външно съединение, било то ляво или дясно, трябва да изпълни цялата работа на вътрешно съединение, заедно с допълнителната работа, удължаваща резултатите. Очаква се външно съединение да върне по-голям брой записи, което допълнително увеличава общото му време за изпълнение само поради по-големия набор от резултати.
По този начин външното съединение е по-бавно от вътрешното съединение.
Освен това може да има някои специфични ситуации, при които лявото съединение ще бъде по-бързо от вътрешното, но не можем да продължим да ги заместваме помежду си, тъй като лявото външно съединение не е функционално еквивалентно на вътрешно съединение.
Нека обсъдим случай, при който лявото присъединяване може да е по-бързо от вътрешното присъединяване. Ако таблиците, участващи в операцията за присъединяване, са твърде малки, кажете, че имат по-малко от 10 записа и таблиците не разполагат с достатъчно индекси, за да покрият заявката, в този случай лявото присъединяване обикновено е по-бързо от вътрешното присъединяване.
Нека създадем двете таблици по-долу и направим ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ и ЛЯВО ВЪНШНО ПРИСЪЕДИНЯВАНЕ между тях като пример:
CREATE TABLE #Table1 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Table1 (ID, Name) VALUES (1, 'A') INSERT #Table1 (ID, Name) VALUES (2, 'B') INSERT #Table1 (ID, Name) VALUES (3, 'C') INSERT #Table1 (ID, Name) VALUES (4, 'D') INSERT #Table1 (ID, Name) VALUES (5, 'E') CREATE TABLE #Table2 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Table2 (ID, Name) VALUES (1, 'A') INSERT #Table2 (ID, Name) VALUES (2, 'B') INSERT #Table2 (ID, Name) VALUES (3, 'C') INSERT #Table2 (ID, Name) VALUES (4, 'D') INSERT #Table2 (ID, Name) VALUES (5, 'E') SELECT * FROM #Table1 t1 INNER JOIN #Table2 t2 ON t2.Name = t1.Name
документ за самоличност | Име | документ за самоличност | Име | |
---|---|---|---|---|
По-долу е визуализацията на вътрешно съединение: | По-долу е визуализацията на външно съединение | |||
един | един | ДА СЕ | един | ДА СЕ |
две | две | Б. | две | Б. |
3 | 3 | ° С | 3 | ° С |
4 | 4 | д | 4 | д |
5 | 5 | Е | 5 | Е |
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55);
документ за самоличност | Име | документ за самоличност | Име | |
---|---|---|---|---|
един | един | ДА СЕ | един | ДА СЕ |
две | две | Б. | две | Б. |
3 | 3 | ° С | 3 | ° С |
4 | 4 | д | 4 | д |
5 | 5 | Е | 5 | Е |
Както можете да видите по-горе, и двете заявки са върнали един и същ набор от резултати. В този случай, ако прегледате плана за изпълнение на двете заявки, тогава ще откриете, че вътрешното присъединяване е струвало повече от външното съединение. Това е така, защото за вътрешно присъединяване SQL сървърът има хеш съвпадение, докато той влага цикли за лявото съединение.
Хеш съвпадението обикновено е по-бързо от вложените цикли. Но в този случай, тъй като броят на редовете е толкова малък и няма индекс, който да се използва (тъй като правим присъединяване към колона с име), хеш операцията се оказа най-скъпата вътрешна заявка за присъединяване.
Ако обаче промените съответстващия ключ в заявката за присъединяване от Name на ID и ако има голям брой редове в таблицата, тогава ще откриете, че вътрешното присъединяване ще бъде по-бързо от лявото външно съединение.
MS Access Вътрешно и външно присъединяване
Когато използвате множество източници на данни в заявка на MS Access, тогава прилагате JOINs, за да контролирате записите, които искате да видите, в зависимост от това как източниците на данни са свързани помежду си.
Във вътрешно съединение само свързаните от двете таблици се комбинират в един набор от резултати. Това е присъединяване по подразбиране в Access и най-често използваното. Ако приложите присъединяване, но не посочите изрично какъв тип присъединяване е, тогава Access приема, че това е вътрешно присъединяване.
Във външните обединения всички свързани данни от двете таблици се комбинират правилно, плюс всички останали редове от една таблица. При пълни външни съединения, когато е възможно, всички данни се комбинират.
Ляво присъединяване срещу ляво външно присъединяване
В SQL сървъра ключовата дума external не е задължителна, когато прилагате ляво външно присъединяване. По този начин няма значение, ако напишете „LEFT OUTER JOIN“ или „LEFT JOIN“, тъй като и двете ще ви дадат един и същ резултат.
A LEFT JOIN B е еквивалентен синтаксис на A LEFT OUTER JOIN B.
По-долу е списъкът на еквивалентните синтаксиси в SQL сървъра:
[изображение източник ]
Ляво външно присъединяване срещу дясно външно присъединяване
Вече видяхме тази разлика в тази статия. Можете да се обърнете към заявките за ляво външно присъединяване и дясното външно присъединяване, за да видите разликата.
Основната разлика между лявото и дясното присъединяване се състои в включването на несъвпадащи редове. Лявото външно съединение включва несравнимите редове от таблицата, която е вляво на клаузата за присъединяване, докато дясното външно съединение включва несъответстващите редове от таблицата, която е вдясно от клаузата за присъединяване.
Хората питат кое е по-добре да се използва, т.е. ляво или дясно присъединяване? По принцип те са от същия тип операции, с изключение на обърнатите аргументи. Следователно, когато питате кое съединение да използвате, вие всъщност питате дали да напишете а. Въпросът е само на предпочитание.
По принцип хората предпочитат да използват Left join в своята SQL заявка. Бих препоръчал да останете последователни в начина, по който пишете заявката, за да избегнете объркване при тълкуването на заявката.
Досега сме виждали всичко за Вътрешното присъединяване и всички видове Външни присъединявания. Нека обобщим бързо разликата между Вътрешно присъединяване и Външно присъединяване.
Разлика между Вътрешно присъединяване и Външно присъединяване в табличен формат
Вътрешно присъединяване | Външно присъединяване |
---|---|
Връща само редовете, които имат съвпадащи стойности в двете таблици. | Включва съответстващите редове, както и някои от несъвпадащите редове между двете таблици. |
В случай, че в таблиците има голям брой редове и има индекс за използване, INNER JOIN обикновено е по-бърз от OUTER JOIN. | Като цяло, OUTER JOIN е по-бавен от INNER JOIN, тъй като трябва да върне повече записи в сравнение с INNER JOIN. Въпреки това, може да има някои специфични сценарии, при които OUTER JOIN е по-бърз. |
Когато съвпадение не бъде намерено, то не връща нищо. | Когато съвпадение не е намерено, във върнатата стойност на колоната се поставя NULL. |
Използвайте INNER JOIN, когато искате да търсите подробна информация за която и да е конкретна колона. | Използвайте OUTER JOIN, когато искате да покажете списъка с цялата информация в двете таблици. |
INNER JOIN действа като филтър. И на двете таблици трябва да има съвпадение, за да може вътрешно съединение да връща данни. | Те действат като добавяне на данни. |
Съществува неявна нотация на присъединяване за вътрешно присъединяване, което включва таблици, които трябва да бъдат обединени по запетая, в клаузата FROM. Пример: SELECT * FROM product, category WHERE product.CategoryID = category.CategoryID; | Няма неявна нотация на присъединяване за външно присъединяване. |
Вътрешно и външно присъединяване срещу съюз
Понякога бъркаме Join and Union и това е и един от най-често задаваните въпроси в SQL интервюта . Вече видяхме разликата между вътрешно съединение и външно съединение. Сега, нека видим как СЪЮЗА се различава от СЪЮЗА.
UNION поставя ред заявки един след друг, докато join създава декартов продукт и го подгрупира. По този начин UNION и JOIN са напълно различни операции.
Нека пуснем по-долу две заявки в MySQL и да видим резултата от тях.
Заявка за СЪЮЗ:
SELECT 28 AS bah UNION SELECT 35 AS bah;
Резултат:
Бах | |
---|---|
един | 28 |
две | 35 |
ПРИСЪЕДИНЕТЕ ЗАПИТВАНЕ:
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55);
Резултат:
foo | Бар | |
---|---|---|
един | 38 | 35 |
Операцията UNION поставя резултата от две или повече заявки в един набор от резултати. Този набор от резултати съдържа всички записи, които се връщат чрез всички запитвания, включени в UNION. По този начин, по същество, UNION комбинира двете набори от резултати заедно.
Операцията за присъединяване извлича данни от две или повече таблици въз основа на логическите връзки между тези таблици, т.е. въз основа на условието за присъединяване. В заявката за присъединяване данните от една таблица се използват за избор на записи от друга таблица. Тя ви позволява да свързвате подобни данни, които присъстват в различни таблици.
За да го разберете много просто, можете да кажете, че UNION комбинира редове от две таблици, докато join обединява колони от две или повече таблици. По този начин и двете се използват за комбиниране на данните от n таблици, но разликата е в начина на комбиниране на данните.
По-долу са изобразените изображения на UNION и JOIN.
Горното е изобразително представяне на операция за присъединяване, изобразяващо, че всеки запис в набора от резултати съдържа колони от двете таблици, т.е.
Присъединяването обикновено е резултат от денормализация (противоположно на нормализирането) и използва външния ключ на една таблица, за да търси стойностите на колоните, като използва първичен ключ в друга таблица.
Горното е изобразително представяне на UNION Operation, показващо, че всеки запис в резултата е ред от някоя от двете таблици. По този начин резултатът от UNION комбинира редовете от таблица А и таблица Б.
Допълнително четене = >> MySQL UNION обясни с примери
Заключение
В тази статия видяхме основните разлики между Вътрешно присъединяване и Външно присъединяване в SQL . Видяхме и класификацията на външно присъединяване, т.е. ляво присъединяване, дясно присъединяване и пълно присъединяване. Видяхме как всеки от тези типове съединения работи и как те се различават един от друг.
Направихме и сравнение на производителността между тези типове съединения. Също така обсъдихме как присъединяването е различно от обединението.
Прочетете също = >> Типове присъединяване към MySQL
Надявам се тази статия да ви е помогнала да изчистите съмненията си относно разликите между различните видове присъединяване. Сигурни сме, че това наистина ще ви накара да решите кой тип присъединяване да изберете въз основа на желания набор от резултати.
Препоръчително четене
- Точна разлика между проверката и проверката с примери
- Модем срещу рутера: Знаете точната разлика
- Разлика между SQL Vs MySQL Vs SQL Server (с примери)
- Урок за Python DateTime с примери
- LAN срещу WAN срещу MAN: Точна разлика между видовете мрежи
- Изрежете командата в Unix с примери
- Синтаксис на командата Unix Cat, Опции с примери
- Използване на курсора в MongoDB с примери