[Sql] В чем разница между «INNER JOIN» и «OUTER JOIN»?


Answers

Также вы можете рассмотреть следующую схему для разных типов соединений;

Источник: Visual-Representation-of-SQL-Joins подробно описанный CL Moffatt

Question

Также как LEFT JOIN , RIGHT JOIN И FULL JOIN подходят?




The precise algorithm for INNER JOIN , LEFT/RIGHT OUTER JOIN are like as following:

  1. Take each row from the first table: a
  2. Consider all rows from second table beside it: (a, b[i])
  3. Evaluate the ON clause against each pair: ON(a, b[i]) = true/false?
    • When the condition evaluates to true , return it.
    • For (left/right) Outer Joins : if reach end of second table without any match, return a ( virtual ) pair using Null for all columns of second table: (a, Null) . This is to ensure all rows of first table exists in final results.

Note: the condition specified in ON clause could be anything, it is not required to use Primary Keys and you don't need to always refer to Columns from both tables! Например:

  • ... ON T1.title = T2.title AND T1.version < T2.version ( => see this post as a sample usage: Select only rows with max value on a column )
  • ... ON T1.y IS NULL
  • ... ON 1 = 0 (just as sample)

You can see also this answer in current page for more samples.




The difference is in the way tables are joined if there are no common records.

JOIN is same as INNER JOIN and means to only show records common to both tables. Whether the records are common is determined by the fields in join clause.

Например:

SELECT * 
FROM t1
JOIN t2 on t1.ID = t2.ID

It means show only records where the same ID value exists in both tables.

LEFT JOIN is same as LEFT OUTER JOIN and means to show all records from left table (ie the one that precedes in SQL statement) regardless of the existence of matching records in the right table.

RIGHT JOIN is same as RIGHT OUTER JOIN and means opposite of LEFT JOIN , ie shows all records from the second (right) table and only matching records from first (left) table.




INNER JOIN requires there is at least a match in comparing the two tables. For example, table A and table B which implies A ٨ B (A intersection B).

LEFT OUTER JOIN and LEFT JOIN are the same. It gives all the records matching in both tables and all possibilities of the left table.

Similarly, RIGHT OUTER JOIN and RIGHT JOIN are the same. It gives all the records matching in both tables and all possibilities of the right table.

FULL JOIN is the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN without duplication.




You use INNER JOIN to return all rows from both tables where there is a match. ie In the resulting table all the rows and columns will have values.

In OUTER JOIN the resulting table may have empty columns. Outer join may be either LEFT or RIGHT .

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.




  • Inner join - An inner join using either of the equivalent queries gives the intersection of the two tables , ie the two rows they have in common.

  • Left outer join - A left outer join will give all rows in A, plus any common rows in B.

  • Full outer join - A full outer join will give you the union of A and B, ie All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versay




Внутренние соединения требуют, чтобы в объединенной таблице существовала запись с соответствующим идентификатором.

Внешние соединения возвращают записи для левой стороны, даже если ничего не существует для правой стороны.

Например, у вас есть таблица Заказы и OrderDetails. Они связаны «OrderID».

заказы

  • Номер заказа
  • Имя Клиента

Информация для заказа

  • OrderDetailID
  • Номер заказа
  • Наименование товара
  • Кол-во
  • Цена

Запрос

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

будет возвращать только ордеры, которые также имеют что-то в таблице OrderDetails.

Если вы измените его на ВНЕШНЮЮ ВЛЕВО

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

то он будет возвращать записи из таблицы Orders, даже если у них нет записей OrderDetails.

Вы можете использовать это, чтобы найти Заказы, у которых нет каких-либо OrderDetails, указывающих на возможный сиротский порядок, добавив предложение where, например WHERE OrderDetails.OrderID IS NULL .




Диаграммы Венна на самом деле не делают этого для меня.

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

Нет никакой альтернативы пониманию логической обработки, и это довольно просто понять.

  1. Представьте себе крест.
  2. Оцените предложение on по всем строкам с шага 1, сохраняя те, где предикат оценивает значение true
  3. (Только для внешних соединений) добавьте обратно в любые внешние строки, которые были потеряны на шаге 2.

(NB: На практике оптимизатор запросов может найти более эффективные способы выполнения запроса, чем чисто логическое описание выше, но конечный результат должен быть одинаковым)

Я начну с анимированной версии полного внешнего соединения . Дальнейшее объяснение следует.

объяснение

Исходные таблицы

Сначала начните с CROSS JOIN (Carteian Product). Это не имеет предложения ON и просто возвращает каждую перестановку строк из двух таблиц.

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

Внутренние и внешние соединения имеют предикат предложения ON.

  • Внутреннее соединение. Оцените условие в предложении «ON» для всех строк в результате перекрестного соединения. Если true, верните объединенную строку. В противном случае отбросьте его.
  • Left Outer Join. То же, что и внутреннее соединение, то для любых строк в левой таблице, которые не соответствуют чему-либо, выводят их с NULL-значениями для столбцов правой таблицы.
  • Правое внешнее соединение. То же, что и внутреннее соединение, то для любых строк в правой таблице, которые не соответствуют чему-либо, выводят их с значениями NULL для столбцов левой таблицы.
  • Полное внешнее соединение. То же, что и внутреннее соединение, тогда сохраняйте левые несопоставимые строки, как в левом внешнем соединении, так и в правых не совпадающих строках в соответствии с правым внешним соединением.

Некоторые примеры

SELECT A.Colour, B.Colour FROM IN INER JOIN B ON A.Colour = B.Colour

Вышеприведенное классическое объединение equi.

Анимированная версия

SELECT A.Colour, B.Colour FROM IN INER JOIN B ON A.Colour NOT IN («Зеленый», «Синий»)

Внутреннее условие соединения необязательно должно быть условием равенства, и ему не нужно ссылаться на столбцы из (или даже любой) таблиц. Оценка A.Colour NOT IN ('Green','Blue') в каждой строке перекрестного соединения возвращается.

SELECT A.Colour, B.Colour FROM IN INER JOIN B ON 1 = 1

Условие соединения оценивается как true для всех строк в результате перекрестного соединения, так что это то же самое, что и кросс-соединение. Я не буду повторять изображение 16 строк.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

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

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

Это просто ограничивает предыдущий результат только возвратом строк, где B.Colour IS NULL . В этом конкретном случае это будут строки, которые были сохранены, поскольку они не совпадали в правой таблице, и запрос возвращает единственную красную строку, не сопоставленную в таблице B Это известно как анти-соединение.

Важно выбрать столбец для теста IS NULL который либо не имеет значения NULL, либо для которого условие соединения гарантирует, что любые значения NULL будут исключены для правильной работы этого шаблона и избежания просто возврата строк, которые имеют Значение NULL для этого столбца в дополнение к несогласованным строкам.

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

Правые внешние соединения действуют аналогично левым внешним соединениям, за исключением того, что они сохраняют несоответствующие строки из правой таблицы, а null расширяют левые столбцы.

SELECT A.Colour, B.Colour FROM FULL OUTER JOIN B ON A.Colour = B.Colour

Полное внешнее объединение объединяет поведение левого и правого объединений и сохраняет несоответствующие строки как из левой, так и из правой таблиц.

SELECT A.Colour, B.Colour FROM FULL OUTER JOIN B ON 1 = 0

Никакие строки в перекрестном соединении не соответствуют предикату 1=0 . Все строки с обеих сторон сохраняются с использованием обычных правил внешнего соединения с NULL в столбцах из таблицы с другой стороны.

SELECT COALESCE (A.Colour, B.Colour) AS Color ОТ ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ B ON 1 = 0

При незначительной поправке к предыдущему запросу можно смоделировать UNION ALL из двух таблиц.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

Обратите внимание, что WHERE (если присутствует) логически запускается после соединения. Одной из распространенных ошибок является выполнение левого внешнего соединения, а затем включение предложения WHERE с условием в правой таблице, которое заканчивается исключением несоответствующих строк. Вышеприведенное завершает выполнение внешнего соединения ...

... И затем выполняется предложение «Где». NULL= 'Green' не оценивает значение true, поэтому строка, сохраненная внешним соединением, заканчивается отбрасыванием (вместе с синим), эффективно преобразуя соединение обратно во внутреннее.

Если бы намерение состояло в том, чтобы включать только строки из B, где Цвет Зеленый, а все строки из A, независимо от правильного синтаксиса,

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

SQL Fiddle

См. Эти примеры в реальном времени на SQLFiddle.com .




In SQL, A join is used to compare and combine — literally join — and return specific rows of data from two or more tables in a database. An inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables.

Внутреннее соединение

An inner join focuses on the commonality between two tables. When using an inner join , there must be at least some matching data between two (or more) tables that are being compared. An inner join searches tables for matching or overlapping data. Upon finding it, the inner join combines and returns the information into one new table.

Example of Inner Join

Let's consider a common scenario of two tables: product prices and quantities. The common information in the two tables is product name, so that is the logical column to join the tables on. There are some products that are common in the two tables; others are unique to one of the tables and don't have a match in the other table.

An inner join on Products returns information about only those products that are common in both tables.

Outer Join

An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table.

There are three types of outer joins:

  • Left Outer Join (or Left Join)
  • Right Outer Join (or Right Join)
  • Full Outer Join (or Full Join) Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not.

Left Outer Join

A left outer join will return all the data in Table 1 and all the shared data (so, the inner part of the Venn diagram example), but only corresponding data from Table 2, which is the right join.

Left Join Example

In my example database, there are two products — oranges and tomatoes — on the 'left' (Prices table) that do not have a corresponding entry on the 'right' (Quantities table). In a left join , these rows are included in the result set with a NULL in the Quantity column. The other rows in the result are the same as the inner join.

Right Outer Join

A right outer join returns Table 2's data and all the shared data, but only corresponding data from Table 1, which is the left join.

Right Join Example

Similar to the left join example, the output of a right outer join includes all rows of the inner join and two rows — broccoli and squash — from the 'right' (Quantities table) that do not have matching entries on the left.

Full Outer Join

A full outer join , or full join , which is not supported by the popular MySQL database management system, combines and returns all data from two or more tables, regardless of whether there is shared information. Think of a full join as simply duplicating all the specified information, but in one table, rather than multiple tables. Where matching data is missing, nulls will be produced.




Inner Join An inner join focuses on the commonality between two tables. When using an inner join, there must be at least some matching data between two (or more) tables that are being compared. An inner join searches tables for matching or overlapping data. Upon finding it, the inner join combines and returns the information into one new table.

Outer Join An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table.

There are three types of outer joins:

Left Outer Join (or Left Join) Right Outer Join (or Right Join) Full Outer Join (or Full Join) Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not.




Inner join.

A join is combining the rows from two tables. An inner join attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match. If a row from the first table in the join matches two rows in the second table, then two rows will be returned in the results. If there's a row in the first table that doesn't match a row in the second, it's not returned; likewise, if there's a row in the second table that doesn't match a row in the first, it's not returned.

Outer Join.

A left join attempts to find match up the rows from the first table to rows in the second table. If it can't find a match, it will return the columns from the first table and leave the columns from the second table blank (null).




Внутреннее соединение

Извлеките только согласованные строки, то есть A intersect B

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Левая внешняя связь

Выберите все записи из первой таблицы и любые записи во второй таблице, которые соответствуют связанным ключам.

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Полная внешняя связь

Выберите все записи из второй таблицы и любые записи в первой таблице, которые соответствуют соединенным ключам.

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Рекомендации




  • INNER JOIN most typical join for two or more tables. It returns data match on both table ON primarykey and forignkey relation.
  • OUTER JOIN is same as INNER JOIN , but it also include NULL data on ResultSet.
    • LEFT JOIN = INNER JOIN + Unmatched data of left table with Null match on right table.
    • RIGHT JOIN = INNER JOIN + Unmatched data of right table with Null match on left table.
    • FULL JOIN = INNER JOIN + Unmatched data on both right and left tables with null matches.
  • Self join is not a keyword in SQL, when a table references data in itself knows as self join. Using INNER JOIN and OUTER JOIN we can write self join queries.

Например:

SELECT * 
FROM   tablea a 
       INNER JOIN tableb b 
               ON a.primary_key = b.foreign_key 
       INNER JOIN tablec c 
               ON b.primary_key = c.foreign_key 



Соединения используются для объединения данных из двух таблиц, в результате чего создается новая временная таблица. Соединения выполняются на основе чего-то, называемого предикатом, который определяет условие для использования для соединения. Разница между внутренним соединением и внешним соединением заключается в том, что внутреннее соединение будет возвращать только строки, которые фактически соответствуют на основе предиката соединения. Рассмотрим таблицу Employee and Location:

Внутреннее соединение: - Внутреннее соединение создает новую таблицу результатов путем объединения значений столбцов двух таблиц ( Employee and Location ) на основе предиката соединения. Запрос сравнивает каждую строку Employee с каждой строкой местоположения, чтобы найти все пары строк, которые удовлетворяют предикату соединения. Когда предикат соединения выполняется путем сопоставления значений, отличных от NULL, значения столбцов для каждой согласованной пары строк Employee и Location объединяются в строку результатов. Вот как выглядит SQL для внутреннего соединения:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Теперь вот что получило бы результат выполнения SQL:

Outer Join: - Внешнее соединение не требует, чтобы каждая запись в двух соединенных таблицах имела соответствующую запись. Объединенная таблица сохраняет каждую запись, даже если не существует другой подходящей записи. Внешние соединения подразделяются дальше на левые внешние соединения и правые внешние соединения, в зависимости от того, какие строки таблицы сохраняются (слева или справа).

Left Outer Join: - Результат левого внешнего соединения (или просто левого соединения) для таблиц Employee and Location всегда содержит все записи «левой» таблицы ( Employee ), даже если условие соединения не находит подходящей записи в «правая» таблица ( местоположение ). Вот как выглядит SQL для левого внешнего соединения, используя приведенные выше таблицы:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Теперь вот что получило результат выполнения этого SQL:

Right Outer Join: - Правое внешнее соединение (или правое соединение) близко напоминает левое внешнее соединение, за исключением обработки обращенных столов. Каждая строка из «правой» таблицы ( Location ) появится хотя бы один раз в объединенной таблице. Если ни одна соответствующая строка из «левой» таблицы ( Employee ) не существует, NULL будет отображаться в столбцах Employee для тех записей, которые не совпадают в Location . Это выглядит так:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

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

Полное Внешнее Объединение: - Полное Внешнее Присоединение или Полное Присоединение - сохранить информацию о немаркировке, включив нечетные строки в результаты соединения, используйте полное внешнее соединение. Он включает все строки из обеих таблиц, независимо от того, имеет ли другая таблица соответствующее значение.

Источник изображения




Both inner and outer joins are used to combine rows from two or more tables into a single result. This is done using a join condition. The join condition specifies how columns from each table are matched to one another. In most cases the aim is to find equal values between tables, and include those matches.

The most common case for this is when you're matching the foreign key of one table to the primary key of another, such as when using and ID to lookup a value.

Though both inner and outer joins include rows from both tables when the match condition is successful, they differ in how they handle a false match condition.

Inner joins don't include non-matching rows; whereas, outer joins do include them. Let's dig a little deeper into the mechanics of each

Inner Join Mechanics

An inner join is used to return results by combining rows from two or more tables.

In its simplest case, where there is no join condition, an inner join would combine all rows from one table with those from another. If the first table contained three rows, and the second, four, then the final result would contain twelve (3 x 4 = 12) !

The purpose of the join condition is to limit which rows are combined. In most cases we limit rows to those matching a column. If a person has more than one phone number, then more than one match is made. From this you can see we may get more rows returned than we have for each person.

Tables to Join Conversely, if a person has no phone number, then there won't be an entry in PersonPhone, and no match made. That particular person won't be included in the results, as only those with matches are included. Let's try an example. Suppose the HR Manager wants to create a phone directory. They want the person's first name, last name, title, and phone numbers. What query could you use to create this? Here is one that would do the trick:

SELECT   P.FirstName,
         P.LastName,
         P.Title,
         PH.PhoneNumber
FROM     Person.Person AS P
         INNER JOIN
         Person.PersonPhone AS PH
         ON P.BusinessEntityID = PH.BusinessEntityID
         AND PH.PhoneNumberTypeID = 3
ORDER BY P.LastName

The INNER JOIN specifies which tables to join and the match condition for doing so. The condition PH.Phone NumberTyeID = 3 limits the query to work numbers. If you run the above, you get the following results:

Inner Join Results Keep in mind the inner join only returns row where the match condition is true. In this example, rows where the BusinessEntityID's don't match aren't included. This could be an issue if a person doesn't have a phone number as those employees wouldn't be on the list. If you wish to include these employees you can use an Outer join.

Outer Join Mechanics

An outer join is used to return results by combining rows from two or more tables. But unlike an inner join, the outer join will return every row from one specified table, even if the join condition fails. Take the phone directory example above. If the HR manager wanted to list every employee regardless of whether they had a work phone number, then using an outer join would make it so.

SELECT   P.FirstName,
         P.LastName,
         P.Title,
         PH.PhoneNumber
FROM     Person.Person AS P
         LEFT OUTER JOIN
         Person.PersonPhone AS PH
         ON P.BusinessEntityID = PH.BusinessEntityID
         AND PH.PhoneNumberTypeID = 3
ORDER BY P.LastName

You can learn more about left and right outer joins in this article, for now just understand that when a LEFT OUTER JOIN is used, all rows for the table in the FROM clause are included in the result, even if a match isn't found with the other table. When a match isn't found, then a NULL is place in the column. You can see this in action below:

Outer Join Results Notice in the example the PhoneNumber for Catherine Abel is NULL. This is because Catherine's work number isn't listed, and no match was found during the join. If this would have been an inner join, then this row wouldn't have been included in the results.