“内连接”和“外连接”的区别是什么?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

左连接、右连接和全连接是如何组合的?

32 个解决方案

#1

Assuming you're joining on columns with no duplicates, which is a very common case:

假设你加入的列没有重复，这是一个很常见的情况:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

  A和B的内部连接给出了相交B的结果，也就是维恩图的内部部分。

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

  A和B的外部连接给出一个联合B的结果，即维恩图联盟的外部部分。

Examples

例子

Suppose you have two tables, with a single column each, and data as follows:

假设您有两个表，每个表只有一个列，数据如下:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

注意(1,2)是A唯一的，(3,4)是常见的，(5,6)是B唯一的。

Inner join

内连接

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

使用等价查询的内部连接给出两个表的交集，即它们共有的两行。

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

左外连接

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

左外连接将给出A中的所有行，加上B中的所有公共行。

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

右外连接

A right outer join will give all rows in B, plus any common rows in A.

右外连接将给出B中的所有行，以及A中的任何公共行。

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

全外连接

A full outer join will give you the union of A and B, i.e. 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 versa.

一个完整的外部连接将给你A和B的联合，即A中的所有行和B中的所有行。

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

#2

Also you can consider the following schema for different join types;

您还可以为不同的连接类型考虑以下模式;

Source: Visual-Representation-of-SQL-Joins explained in detail by C.L. Moffatt

资料来源:C.L. Moffatt详细解释了sql连接的可视化表示

#3

I recommend Jeff's blog article. The best description I've ever seen, plus there is a visualization, e.g.:

我推荐杰夫的博客文章。这是我所见过的最好的描述，另外还有一个形象化的说法，例如:

Inner Join:

内连接:

Full Outer Join:

全外连接:

#4

The Venn diagrams don't really do it for me.

维恩图并不能真正帮助我。

They don't show any distinction between a cross join and an inner join, for example, or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate.

例如，它们不显示交叉连接和内部连接之间的任何区别，或者更一般地显示不同类型的连接谓词之间的任何区别，或者提供一个框架来推理它们将如何操作。

There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway.

理解逻辑处理是无可替代的，无论如何，理解逻辑处理都是相对简单的。

1. Imagine a cross join.
2. 想象一个交叉连接。
3. Evaluate the on clause against all rows from step 1 keeping those where the predicate evaluates to true
4. 根据步骤1中的所有行计算on子句，使谓词计算为true
5. (For outer joins only) add back in any outer rows that were lost in step 2.
6. (仅适用于外部连接)在步骤2中丢失的任何外部行中添加回。

(NB: In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same)

(NB:在实践中，查询优化器可能会找到比上面纯逻辑描述更有效的执行查询的方法，但最终结果必须相同)

I'll start off with an animated version of a full outer join. Further explanation follows.

我将从一个完整的外部连接的动画版本开始。进一步的解释。

---

Explanation

Source Tables

源表

First start with a CROSS JOIN (AKA Cartesian Product). This does not have an ON clause and simply returns every permutation of rows from the two tables.

首先从交叉连接开始(即笛卡尔积)。它没有ON子句，只返回两个表中每一行的排列。

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

选择一个。颜色,B。颜色从一个交叉连接B。

Inner and Outer joins have an "ON" clause predicate.

内部和外部连接都有一个“ON”子句谓词。

• Inner Join. Evaluate the condition in the "ON" clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.
• 内连接。对交叉连接结果中的所有行计算“ON”子句中的条件。如果为真，返回已加入的行。否则丢弃它。
• Left Outer Join. Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns.
• 左外连接。与内连接一样，对于左侧表中的任何行，它们都不匹配任何输出，这些值与右表列的空值相匹配。
• Right Outer Join. Same as inner join then for any rows in the right table that did not match anything output these with NULL values for the left table columns.
• 右外连接。与内部连接相同的是，对于没有匹配任何内容的右表中的任何行，输出这些值为左表列的空值。
• Full Outer Join. Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join.
• 全外连接。与内连接相同，然后保留左外连接中不匹配的行和右外连接中不匹配的行。

Some examples

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

选择一个。颜色,B。颜色从内部连接B在A上。颜色= B.Colour

The above is the classic equi join.

以上就是经典的equi加入。

Animated Version

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating A.Colour NOT IN ('Green','Blue') on each row of the cross join returns.

内部连接条件不一定是相等条件，它也不需要同时引用表中的两列(甚至任何一列)。评估一个。在交叉连接的每一行上不要用“绿色”、“蓝色”表示颜色。

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1

选择一个。颜色,B。颜色从内部连接B 1 =1

The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won't repeat the picture of the 16 rows again.

对于交叉连接结果中的所有行，join条件的计算结果都为true，因此这与交叉连接是一样的。我不会再重复这16行。

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

Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with NULL values for the right hand columns.

外部连接在逻辑上是相同的方式评估内部连接除了,如果一行从左表(左加入)不加入任何行从右表是保存在结果与NULL值的右边列。

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

This simply restricts the previous result to only return the rows where B.Colour IS NULL. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B. This is known as an anti semi join.

这仅仅限制了前面的结果只返回B所在的行。颜色是NULL。在这个特定的例子中，这些行将被保留，因为它们在右边的表中没有匹配项，查询返回表b中不匹配的单个红色行，这被称为反半连接。

It is important to select a column for the IS NULL test that is either not nullable or for which the join condition ensures that any NULL values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULL value for that column in addition to the un matched rows.

是很重要的选择一列是不可以为空或空测试的联接条件确保任何NULL值将被排除在外为了这个模式正常工作,避免只带回行,正好有一个空值的列除了联合国匹配行。

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

Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.

右外连接的作用与左外连接类似，只是它们保留了来自右表的不匹配行，并且null扩展了左列。

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

Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.

完整的外部连接结合左和右连接的行为，并保留来自左和右表的非匹配行。

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

No rows in the cross join match the 1=0 predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.

交叉连接中没有行与1=0谓词匹配。来自两边的所有行都使用正常的外部连接规则保存，从另一端的表中的列中使用NULL。

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

With a minor amend to the preceding query one could simulate a UNION ALL of the two tables.

通过对前面的查询进行小修改，可以模拟两个表的联合。

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

Note that the WHERE clause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join...

注意，WHERE子句(如果存在)在连接之后逻辑地运行。一个常见的错误是执行一个左外连接，然后在右边的表中包含一个WHERE子句，它的条件是排除不匹配的行。以上结束执行外部连接……

... And then the "Where" clause runs. NULL= 'Green' does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.

…然后是Where子句。NULL= 'Green'不会计算为true，因此外部连接保留的行最终会被丢弃(与蓝色连接一起)，从而有效地将连接转换回内部。

If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be

如果目的是只包含来自B的行，其中颜色为绿色，所有来自A的行，而不考虑正确的语法

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

SQL Fiddle

See these examples run live at SQLFiddle.com.

请参见SQLFiddle.com实时运行的这些示例。

#5

The following was taken from the article "MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN" by Graham Ellis on his blog Horse's Mouth.

下面摘自Graham Ellis在他的博客Horse's Mouth上发表的文章“MySQL -左连接和右连接，内连接和外连接”。

In a database such as MySQL, data is divided into a number of tables which are then connected (Joined) together by JOIN in SELECT commands to read records from multiple tables. Read this example to see how it works.

在像MySQL这样的数据库中，数据被分成若干个表，然后通过选择命令连接(连接)，从多个表中读取记录。阅读这个例子，看看它是如何工作的。

First, some sample data:

首先,一些示例数据:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

REGULAR JOIN

定期参加

If we do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then we get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:

如果我们做一个常规的连接(没有内部、外部、左或右的关键字)，那么我们将在两个表中以适当的方式获得所有匹配的记录，并且不报告不匹配的两个传入表中的记录:

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

LEFT JOIN

左连接

If we do a LEFT JOIN, we get all records that match in the same way and IN ADDITION we get an extra record for each unmatched record in the left table of the join - thus ensuring (in this example) that every PERSON gets a mention:

如果我们做一个左连接，我们将以相同的方式获得所有匹配的记录，此外，我们将获得连接的左表中每个不匹配记录的额外记录——从而确保(在本例中)每个人都被提及:

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

RIGHT JOIN

正确的连接

If we do a RIGHT JOIN, we get all the records that match and IN ADDITION an extra record for each unmatched record in the right table of the join - in my example, that means that each property gets a mention even if we don't have seller details:

如果我们做一个右连接，我们会得到所有匹配的记录，并且在连接的右表中为每个不匹配的记录添加一个额外的记录——在我的示例中，这意味着即使我们没有卖方详细信息，每个属性也会被提及:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatibility and doesn't add an extra capabilities.

内部连接执行完整连接，就像第一个示例一样，在最后两个示例中，单词LEFT或RIGHT后面可以添加单词OUTER——它提供了ODBC兼容性，不添加额外的功能。

#6

Inner Join

Retrieve the matched rows only, that is, A intersect B.

只检索匹配的行，即A与B相交。

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

---

Left Outer Join

Select all records from the first table, and any records in the second table that match the joined keys.

从第一个表中选择所有记录，以及第二个表中与已连接键匹配的任何记录。

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

---

Full Outer Join

Select all records from the second table, and any records in the first table that match the joined keys.

从第二个表中选择所有记录，以及第一个表中与已连接键匹配的任何记录。

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

---

References

• Inner and outer joins SQL examples and the Join block

  内部和外部SQL连接示例和连接块

• SQL: JOINS

  SQL:连接

#7

Joins are used to combine the data from two tables, with the result being a new, temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join. The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate. Lets consider Employee and Location table:

连接用于合并来自两个表的数据，结果是一个新的临时表。连接是基于谓词执行的，谓词指定了执行连接所需的条件。内部连接和外部连接的不同之处在于，内部连接只返回根据连接谓词实际匹配的行。让我们考虑员工和位置表:

Inner Join:- Inner join creates a new result table by combining column values of two tables (Employee and Location) based upon the join-predicate. The query compares each row of Employee with each row of Location to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employee and Location are combined into a result row. Here’s what the SQL for an inner join will look like:

内连接:-内连接通过基于连接谓词组合两个表(Employee和Location)的列值创建一个新的结果表。查询将Employee的每一行与每一行的位置进行比较，以找到满足join-谓词的所有行对。当通过匹配非空值来满足join-谓词时，每对匹配的Employee和Location的列值被合并到一个结果行中。下面是内部连接的SQL语句:

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

Now, here is what the result of running that SQL would look like:

下面是运行SQL的结果:

Outer Join:- An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins and right outer joins, depending on which table's rows are retained (left or right).

外部连接:-一个外部连接不需要两个联接表中的每个记录都有一个匹配的记录。加入的表保留每个记录，即使不存在其他匹配记录。外部连接进一步细分为左外连接和右外连接，这取决于保留的表的行(左或右)。

Left Outer Join:- The result of a left outer join (or simply left join) for tables Employee and Location always contains all records of the "left" table (Employee), even if the join-condition does not find any matching record in the "right" table (Location). Here is what the SQL for a left outer join would look like, using the tables above:

左外连接:-表Employee和Location的左外连接(或简单的左连接)的结果总是包含“左”表(Employee)的所有记录，即使连接条件在“右”表(位置)中没有找到任何匹配的记录。下面是左外连接的SQL语句，使用上面的表:

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

Now, here is what the result of running this SQL would look like:

下面是运行这个SQL的结果:

Right Outer Join:- A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (Location) will appear in the joined table at least once. If no matching row from the "left" table (Employee) exists, NULL will appear in columns from Employee for those records that have no match in Location. This is what the SQL looks like:

右外连接:—右外连接(或右连接)与左外连接非常相似，但表的处理是相反的。来自“右”表(位置)的每一行将至少出现在已联接表中一次。如果“左”表(Employee)中不存在匹配行，那么Employee中的列将出现NULL，用于那些在位置上不匹配的记录。SQL是这样的:

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

Using the tables above, we can show what the result set of a right outer join would look like:

使用上面的表，我们可以显示右外连接的结果集是什么样子的:

Full Outer Joins:- Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.

全外连接:全外连接或全外连接是通过在连接的结果中包含不匹配的行来保留不匹配的信息，使用全外连接。它包括来自两个表的所有行，不管另一个表是否具有匹配的值。

Image Source

图片来源

MySQL 8.0 Reference Manual - Join Syntax

MySQL 8.0参考手册-连接语法

#8

In simple words:

用简单的话说:

An inner join retrieve the matched rows only.

内联接只检索匹配的行。

Whereas an outer join retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

而外部加入检索匹配的行从一个表和其他表中所有行....结果取决于您使用的是哪一个:

• Left: Matched rows in the right table and all rows in the left table

  左:右表中的匹配行和左表中的所有行

• Right: Matched rows in the left table and all rows in the right table or

  右:左表中的匹配行和右表中的所有行

• Full: All rows in all tables. It doesn't matter if there is a match or not

  满:所有表中的所有行。是否匹配并不重要

#9

A inner join only shows rows if there is a matching record on the other (right) side of the join.

只有在连接的另一侧(右边)有匹配记录时，内部连接才显示行。

A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.

(左)外部连接显示左侧的每个记录的行，即使连接的另一侧(右)没有匹配的行。如果没有匹配的行，另一侧(右边)的列将显示null。

#10

Inner joins require that a record with a related ID exist in the joined table.

内部连接要求具有相关ID的记录存在于已连接的表中。

Outer joins will return records for the left side even if nothing exists for the right side.

外部连接将返回左侧的记录，即使右侧不存在任何记录。

For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".

例如，您有一个订单和一个OrderDetails表。它们由一个“OrderID”关联。

Orders

订单

• OrderID
• OrderID
• CustomerName
• CustomerName

OrderDetails

OrderDetails

• OrderDetailID
• OrderDetailID
• OrderID
• OrderID
• ProductName
• ProductName
• Qty
• 数量
• Price
• 价格

The request

请求

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

will only return Orders that also have something in the OrderDetails table.

将只返回在OrderDetails表中也具有某些内容的订单。

If you change it to OUTER LEFT JOIN

如果你把它改成左外连接

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

then it will return records from the Orders table even if they have no OrderDetails records.

然后它将从Orders表返回记录，即使它们没有OrderDetails记录。

You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails.OrderID IS NULL.

您可以通过添加一个where子句(如where OrderDetails)来查找没有任何OrderDetails指示可能的孤立订单的订单。OrderID是NULL。

#11

In simple words :

用简单的话说:

Inner join -> Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table.

内连接->只从父表和子表中获取公共记录，其中父表的主键与子表中的外键匹配。

Left join ->

左连接- >

pseudo code

伪代码

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Right join : Exactly opposite of left join . Put name of table in LEFT JOIN at right side in Right join , you get same output as LEFT JOIN.

右连接:与左连接完全相反。将表名放在左JOIN中，在右JOIN中，您会得到与左JOIN相同的输出。

Outer join : Show all records in Both tables No matter what. If records in Left table are not matching to right table based on Primary , Forieign key , use NULL value as result of join .

外部连接:显示两个表中的所有记录。如果根据主键、Forieign键，左表中的记录与右表不匹配，则使用NULL值作为连接的结果。

Example :

例子:

Lets assume now for 2 tables

现在假设有两个表

1.employees , 2.phone_numbers_employees

1。2.员工,phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Here , employees table is Master table , phone_numbers_employees is child table(it contains emp_id as foreign key which connects employee.id so its child table.)

这里，employees表是主表，phone_numbers_employees表是子表(它包含emp_id作为外键，连接employee。id是它的子表。

Inner joins

内部连接

Take the records of 2 tables ONLY IF Primary key of employees table(its id) matches Foreign key of Child table phone_numbers_employees(emp_id).

只有当employee表的主键(它的id)与子表phone_numbers_employees(emp_id)的外键匹配时，才对两个表进行记录。

So query would be :

所以查询是:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Here take only matching rows on primary key = foreign key as explained above.Here non matching rows on primary key = foreign key are skipped as result of join.

这里只取主键=外键上的匹配行，如上所述。在这里，主键上的非匹配行=外键由于连接而被跳过。

Left joins :

左连接:

Left join retains all rows of the left table, regardless of whether there is a row that matches on the right table.

左连接保留左表的所有行，而不管右表上是否有匹配的行。

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Outer joins :

外连接:

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramatically it looks like :

它的图表看起来是:

#12

You use INNER JOIN to return all rows from both tables where there is a match. i.e. 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.

LEFT OUTER JOIN返回来自第一个表的所有行，即使第二个表中没有匹配项。

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

RIGHT OUTER JOIN返回来自第二个表的所有行，即使第一个表中没有匹配项。

#13

This is a good diagrammatic explanation for all kind of joins

对于所有类型的连接，这是一个很好的图表解释

source: http://ssiddique.info/understanding-sql-joins-in-easy-way.html

来源:http://ssiddique.info/understanding-sql-joins-in-easy-way.html

#14

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).

内部连接要求在比较两个表时至少有一个匹配项。例如,表和表B这意味着٨B(A路口)。

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.

全连接是左外连接和右外连接的组合，没有重复。

#15

The answer is in the meaning of each one, so in the results.

答案是每一个的意义，结果也是如此。

Note :
In SQLite there is no RIGHT OUTER JOIN or FULL OUTER JOIN.
And also in MySQL there is no FULL OUTER JOIN.

注意:在SQLite中没有正确的外部连接或完整的外部连接。MySQL中也没有完整的外部连接。

My answer is based on above Note.

我的回答是基于上面的说明。

When you have two tables like these:

当你有两张这样的桌子:

--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

---

CROSS JOIN / OUTER JOIN :
You can have all of those tables data with CROSS JOIN or just with , like this:

交叉连接/外部连接:您可以使用交叉连接或仅使用如下方式拥有所有这些表数据:

SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

---

INNER JOIN :
When you want to add a filter to above results based on a relation like table1.id = table2.id you can use INNER JOIN:

内连接:当您希望根据表1之类的关系向上面的结果添加过滤器时。id =表二。你可以使用内连接:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

---

LEFT [OUTER] JOIN :
When you want to have all rows of one of tables in the above result -with same relation- you can use LEFT JOIN:
(For RIGHT JOIN just change place of tables)

左(外)连接:当您想要在上述结果中有一个表的所有行时，您可以使用左连接:(对于右连接仅更改表的位置)

SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

---

FULL OUTER JOIN :
When you also want to have all rows of the other table in your results you can use FULL OUTER JOIN:

完整外连接:当您还希望在结果中包含其他表的所有行时，您可以使用完整外连接:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Well, as your need you choose each one that covers your need ;).

嗯，当你需要的时候，你可以选择每一个满足你需要的;

#16

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. For example:

  JOIN与内部连接相同，也意味着只显示两个表共有的记录。记录是否通用取决于join子句中的字段。例如:

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

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

  表示只显示两个表中存在相同ID值的记录。

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

  左连接与左外连接相同，意味着显示来自左表的所有记录(即SQL语句前面的记录)，而不管右表中是否存在匹配记录。

• RIGHT JOIN is same as RIGHT OUTER JOIN and means opposite of LEFT JOIN, i.e. shows all records from the second (right) table and only matching records from first (left) table.
• 右连接与右外连接相同，意思是左连接的相反，即显示来自第二个(右)表的所有记录，只显示来自第一个(左)表的匹配记录。

Source: What's the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

源:左、右、内、外、连接的区别是什么?

#17

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).

左连接尝试查找将第一个表中的行与第二个表中的行匹配。如果找不到匹配项，它将返回第一个表中的列，并将第二个表中的列留空(null)。

#18

I don't see much details about performance and optimizer in the other answers.

在其他答案中，我看不到关于性能和优化器的详细信息。

Sometimes it is good to know that only INNER JOIN is associative which means the optimizer has the most option to play with it. It can reorder the join order to make it faster keeping the same result. The optimizer can use the most join modes.

有时最好知道只有内部连接是关联的，这意味着优化器有最多的选择来使用它。它可以重新排序联接顺序，以使其保持相同的结果更快。优化器可以使用最多的连接模式。

Generally it is a good practice to try to use INNER JOIN instead of the different kind of joins. (Of course if it is possible considering the expected result set.)

通常，最好使用内部连接，而不是使用不同类型的连接。(当然，如果可能的话，考虑到预期的结果集。)

There are a couple of good examples and explanation here about this strange associative behavior:

关于这种奇怪的联想行为，这里有几个很好的例子和解释:

• Are left outer joins associative?
• 左外连接是结合性的吗?
• Does the join order matter in SQL?
• 连接顺序对SQL有影响吗?

#19

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.

JOIN与内部连接相同，也意味着只显示两个表共有的记录。记录是否通用取决于join子句中的字段。

For example:

例如:

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

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

它意味着只显示两个表中存在相同ID值的记录。

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

左连接与左外连接相同，意味着显示来自左表的所有记录(即SQL语句前面的记录)，而不管右表中是否存在匹配记录。

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

右连接与右外连接相同，意思是左连接的相反，即显示来自第二个(右)表的所有记录，只显示来自第一个(左)表的匹配记录。

#20

Having criticized the much-loved red-shaded Venn diagram, I thought it only fair to post my own attempt.

在批评了备受喜爱的红色阴影维恩图之后，我认为我自己的尝试是公平的。

Although @Martin Smith's answer is the best of this bunch by a long way, his only shows the key column from each table, whereas I think ideally non-key columns should also be shown.

尽管@Martin Smith的回答是这一组中最好的，但是他只显示了每个表中的键列，而我认为理想的非键列也应该显示出来。

The best I could do in the half hour allowed, I still don't think it adequately shows that the nulls are there due to absence of key values in TableB or that OUTER JOIN is actually a union rather than a join:

在允许的半小时内，我所能做的最好的事情是，我仍然不认为它充分显示了null的存在是因为表b中没有键值，或者外部连接实际上是一个联合而不是连接:

#21

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.

左外连接(或左连接)右外连接(或右连接)完整外连接(或全连接)每个外连接都指正在比较、组合和返回的数据的部分。有时在这个过程中会产生nulls，因为有些数据是共享的，而其他数据则不是。

#22

Simplest Definitions

简单的定义

Inner Join: Returns matched records from both tables.

内部连接:返回来自两个表的匹配记录。

Full Outer Join: Returns matched and unmatched records from both tables with null for unmatched records from Both Tables.

完整外连接:返回两个表中匹配的和不匹配的记录，两个表中的不匹配记录为空。

Left Outer Join: Returns matched and unmatched records only from table on Left Side.

左外连接:只从左侧的表返回匹配和不匹配的记录。

Right Outer Join: Returns matched and unmatched records only from table on Right Side.

右外连接:仅从右侧的表返回匹配和不匹配的记录。

In-Short

简而言之

Matched + Left Unmatched + Right Unmatched = Full Outer Join

匹配+左不匹配+右不匹配=完整的外部连接。

Matched + Left Unmatched = Left Outer Join

匹配+左不匹配=左外连接。

Matched + Right Unmatched = Right Outer Join

匹配+右不匹配=右外连接

Matched = Inner Join

匹配=内连接

#23

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

内连接、左/右外连接的精确算法如下:

1. Take each row from the first table: a
2. 从第一张表中取每一行:a
3. Consider all rows from second table beside it: (a, b[i])
4. 考虑它旁边第二个表的所有行:(a, b[i])
5. Evaluate the ON clause against each pair: ON(a, b[i]) = true/false?
   • When the condition evaluates to true, return it.
   • 当条件计算为true时，返回它。
   • 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.
   • 对于(左/右)外连接:如果到达第二个表的末尾时没有任何匹配，则返回一个(虚拟)对，使用Null对第二个表的所有列:(a, Null)。这是为了确保第一个表的所有行都存在于最终结果中。
6. 对每一对进行评估:ON(a, b[i]) = true/false?当条件计算为true时，返回它。对于(左/右)外连接:如果到达第二个表的末尾时没有任何匹配，则返回一个(虚拟)对，使用Null对第二个表的所有列:(a, Null)。这是为了确保第一个表的所有行都存在于最终结果中。

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! For example:

注意:ON子句中指定的条件可以是任何东西，不需要使用主键，也不需要总是引用两个表中的列!例如:

• ... 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)
• …在T1。title = T2。标题和T1。版本< T2。version(=>，见本文示例用法:只选择列上值最大的行)
• ... ON T1.y IS NULL
• …在T1。y为空
• ... ON 1 = 0 (just as sample)
• …1 = 0时(与样本相同)

---

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

您还可以在当前页面中看到更多示例的答案。

#24

INNER JOIN

内连接

An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for. If you don't know which kind of join you need, this will usually be your best bet.

内部连接产生一个结果集，该结果集被限制在两个表中有匹配项的行，以满足我们所要查找的内容。如果你不知道你需要哪种加入，这通常是你最好的选择。

LEFT OUTER JOIN

左外连接

A left outer join, or left join, results in a set where all of the rows from the first, or left hand side, table are preserved. The rows from the second, or right hand side table only show up if they have a match with the rows from the first table. Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set.

左外连接或左连接产生一个集合，其中保存了来自第一个或左手边的所有行。来自第二个或右手边表的行只有在与第一个表中的行匹配时才会显示出来。如果在左边的表中有值，而不是右边的值，那么表将读取null，这意味着该值没有被设置。

RIGHT OUTER JOIN

右外连接

A right outer join, or right join, is the same as a left join, except the roles are reversed. All of the rows from the right hand side table show up in the result, but the rows from the table on the left are only there if they match the table on the right. Empty spaces are null, just like with the the left join.

右外连接或右连接与左连接相同，只是角色颠倒了。右边表的所有行都显示在结果中，但是左边表的行只有在与右边的表匹配时才在那里。空空间是空的，就像左连接一样。

FULL OUTER JOIN

全外连接

A full outer join, or just outer join, produces a result set with all of the rows of both tables, regardless of whether there are any matches. Similarly to the left and right joins, we call the empty spaces null.

一个完整的外部连接，或者仅仅是外部连接，会产生一个结果集，其中包含两个表的所有行，而不管是否有任何匹配。类似于左和右连接，我们将空空间称为null。

For More Reference

更多的参考

#25

• INNER JOIN most typical join for two or more tables. It returns data match on both table ON primarykey and forignkey relation.
• 两个或多个表的最典型连接。在primarykey和forignkey关系上返回两个表上的数据匹配。
• 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.
  • 全连接=内连接+右表和左表上的不匹配数据，具有空匹配。
• 外部连接与内部连接相同，但它也包含ResultSet上的空数据。左连接=内连接+右表空匹配的左表不匹配数据。右连接=内连接+左表空匹配的右表不匹配数据。全连接=内连接+右表和左表上的不匹配数据，具有空匹配。
• 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.
• Self join不是SQL中的关键字，当一个表引用数据时，它本身就知道作为Self join。使用内部连接和外部连接，我们可以编写自连接查询。

For example:

例如:

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 

#26

What is the difference between “INNER JOIN” and “OUTER JOIN”?

“内连接”和“外连接”的区别是什么?

They are the most commonly used existential operators in SQL, where INNER JOIN is used for 'exists' and LEFT OUTER JOIN is used for 'does not exist'.

它们是SQL中最常用的存在操作符，其中内连接用于“存在”，左外连接用于“不存在”。

Consider these queries:

考虑这些查询:

users who have posted and have votes
users who have posted but have no badges

People who look for set-based solutions (an industry term) would recognise the respective queries as:

寻找基于集合的解决方案(行业术语)的人将会识别出相应的查询:

users who have posted INTERSECT users who have votes
users who have posted MINUS users who have badges

Translating these into standard SQL:

将这些转换为标准SQL:

SELECT UserId FROM Posts
INTERSECT 
SELECT UserId FROM Votes;

SELECT UserId FROM Posts
EXCEPT 
SELECT UserId FROM Badges;

Others will think along similar lines of set inclusion:

另一些人则会按照类似的思路考虑集合包含:

users who have posted and IN the set of users who have votes
users who have posted and NOT IN the set of users who have badges

Translating these into standard SQL:

将这些转换为标准SQL:

SELECT UserId 
  FROM Posts
 WHERE UserId IN ( SELECT UserId FROM Votes );

SELECT UserId 
  FROM Posts
 WHERE UserId NOT IN ( SELECT UserId FROM Badges );

Some will think in terms of 'existance' within sets e.g.

有些人会在集合中考虑“存在”。

users who have posted and EXIST in the set of users who have votes
users who have posted and do NOT EXIST in the set of users who have badges

Translating these into standard SQL (note we now need to use range variables i.e. p, v, b):

将这些转换为标准SQL(注意，我们现在需要使用范围变量，即p、v、b):

SELECT p.UserId 
  FROM Posts p
 WHERE EXISTS ( SELECT *
                  FROM Votes v
                 WHERE v.UserId = p.UserId );

SELECT p.UserId 
  FROM Posts p
 WHERE NOT EXISTS ( SELECT *
                      FROM Badges b
                     WHERE b.UserId = p.UserId );

However, I've found that the "industry standard" approach is to exclusively use joins. I don't know what the thinking is here (Law of the Instrument? Premature optimization?), so I'll go straight to the syntax:

但是，我发现“行业标准”方法只使用连接。我不知道这里的想法是什么(乐器的定律?不成熟的优化)，因此我将直接使用语法:

SELECT p.UserId 
  FROM Posts p
       INNER JOIN Votes v ON v.UserId = p.UserId;

SELECT p.UserId 
  FROM Posts p
       LEFT JOIN Badges b ON b.UserId = p.UserId
 WHERE b.UserId IS NULL;

Things to note:

注意事项:

• The only projection is from Users but we still need all those range variables (p, v, b) for search conditions.
• 唯一的投影来自用户，但是我们仍然需要所有的范围变量(p, v, b)来搜索条件。
• The UserId IS NULL search condition 'belongs' to the the OUTER JOIN but is disconnected in the query.
• UserId为空搜索条件“所属”，属于外部连接，但在查询中断开连接。
• LEFT is the industry standard: professionals will rewrite a query to avoid using RIGHT!
• 左边是行业标准:专业人员将重写查询以避免使用右边!
• The OUTER keyword from LEFT OUTER JOIN is omitted.
• 从左外连接的外部关键字被省略。

Closing remarks:

结束语:

Sometimes joins are used in queries solely to determine whether values exist or do not exists in another set. Learn to look carefully at the attributes being projected (the columns in the SELECT clause): if there are none from the joined table then they are simply being used as existential operators. Additionally for outer join, look for instances of <key_column> IS NULL in the WHERE clause.

有时连接用于查询仅仅是为了确定是否存在或不存在于另一组值。学会仔细观察属性被投射(SELECT子句中的列):如果从加入表没有那么他们只是被用作存在运营商。此外，对于外部连接，在WHERE子句中查找 为NULL的实例。

#27

• Inner join - An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. 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.

  左外连接——左外连接将给出A中的所有行，加上B中的任何公共行。

• Full outer join - A full outer join will give you the union of A and B, i.e. 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

  完整的外部连接——一个完整的外部连接将会给你一个A和B的结合，即A和B中的所有行。如果A中没有相应的B的数据，那么B部分是空的，而vice versay。

#28

1.Inner Join: Also called as Join. It returns the rows present in both the Left table, and right table only if there is a match. Otherwise, it returns zero records.

1。内部连接:也称为联接。它只在有匹配的情况下返回左表和右表中的行。否则，它将返回零记录。

Example:

例子:

SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
INNER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id

2.Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table, and right table.

2。完全外部连接:也称为完全连接。它返回左表和右表中存在的所有行。

Example:

例子:

SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
FULL OUTER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id

3.Left Outer join: Or simply called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).

3所示。左外连接:或简单地称为左连接。它返回左表中存在的所有行，以及来自右表的匹配行(如果有的话)。

4.Right Outer Join: Also called as Right Join. It returns matching rows from the left table (if any), and all the rows present in the Right table.

4所示。右外连接:也称为右连接。它从左表(如果有)返回匹配的行，并返回右表中出现的所有行。

Advantages of Joins

连接的优点

1. Executes faster.
2. 执行得更快。

#29

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.

内部和外部连接都用于将来自两个或多个表的行合并为单个结果。这是使用连接条件完成的。join条件指定每个表的列如何相互匹配。在大多数情况下，目标是在表之间找到相等的值，并包含这些匹配项。

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.

最常见的情况是，当您将一个表的外键与另一个表的主键匹配时，例如使用和ID查找一个值时。

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) !

在最简单的情况下，如果没有连接条件，内部连接将把来自一个表的所有行与来自另一个表的所有行合并在一起。如果第一个表包含3行，第二个表包含4行，那么最终结果将包含12行(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:

反过来，如果一个人没有电话号码，那么在PersonPhone中就不会有输入，也不会有匹配。这个特定的人不会被包括在结果中，因为只有那些有匹配的人被包括在内。让我们来做一个例子。假设人力资源经理想要创建一个电话目录。他们想要这个人的名字、姓氏、头衔和电话号码。您可以使用什么查询来创建它?这里有一个技巧:

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:

内部连接指定要联接哪些表以及要联接的匹配条件。条件PH.Phone NumberTyeID = 3将查询限制为工作号。如果您运行上述，您将得到以下结果:

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.

内连接结果记住内连接只返回匹配条件为true的行。在本例中，不包含BusinessEntityID不匹配的行。如果一个人没有电话号码，这可能是个问题，因为这些员工不会出现在名单上。如果希望包含这些员工，可以使用外部连接。

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:

在本文中，您可以了解更多关于左外连接和右外连接的知识，因为现在您只需理解，当使用左外连接时，FROM子句中的表的所有行都包含在结果中，即使与另一个表没有匹配。如果没有找到匹配项，则在列中放置NULL。你可以在以下的行动中看到:

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.

外部连接结果注意，在示例中，Catherine Abel的PhoneNumber为空。这是因为没有列出Catherine的工作编号，并且在join过程中没有找到匹配。如果这是一个内部连接，那么这一行就不会包含在结果中。

#30

In Simple Terms,

简而言之,

1.INNER JOIN OR EQUI JOIN : Returns the resultset that matches only the condition in both the tables.

1。内连接或EQUI联接:返回只匹配表中的条件的resultset。

2.OUTER JOIN : Returns the resultset of all the values from both the tables even if there is condition match or not.

2。外部连接:返回来自两个表的所有值的resultset，即使条件是否匹配。

3.LEFT JOIN : Returns the resultset of all the values from left table and only rows that match the condition in right table.

3所示。左连接:返回左表中所有值的resultset，并且只返回与右表中的条件匹配的行。

4.RIGHT JOIN : Returns the resultset of all the values from right table and only rows that match the condition in left table.

4所示。右连接:从右表返回所有值的resultset，并且只返回与左表中的条件匹配的行。

5.FULL JOIN : Full Join and Full outer Join are same.

5。全连接:全连接和外连接是相同的。