SQL 左联接、右联接、外联接和内联接有什么区别？

What is the difference between Left, Right, Outer and Inner Joins?

提问by MrM

I am wondering how to differentiate all these different joins ...

我想知道如何区分所有这些不同的连接......

回答by BradC

Simple Example: Lets say you have a Studentstable, and a Lockerstable. In SQL, the first table you specify in a join, Students, is the LEFTtable, and the second one, Lockers, is the RIGHTtable.

简单示例：假设您有一张Students桌子和一张Lockers桌子。在 SQL 中，您在连接中指定的第一个表Students是LEFT表，第二个表Lockers是RIGHT表。

Each student can be assigned to a locker, so there is a LockerNumbercolumn in the Studenttable. More than one student could potentially be in a single locker, but especially at the beginning of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.

每个学生都可以分配到一个储物柜，因此表中有一个LockerNumber列Student。一个储物柜中可能有不止一个学生，但尤其是在学年开始时，您可能会有一些没有储物柜的新生学生和一些没有分配学生的储物柜。

For the sake of this example, lets say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 lockers have no student.

为了这个例子，假设你有100 名学生，其中 70 人有储物柜。你总共有50 个储物柜，其中 40 个至少有 1 个学生，10 个储物柜没有学生。

INNER JOINis equivalent to "show me all students with lockers".
Any students without lockers, or any lockers without students are missing.
Returns 70 rows

INNER JOIN相当于“向我展示所有带储物柜的学生”。
任何没有储物柜的学生，或任何没有学生的储物柜都丢失了。
返回 70 行

LEFT OUTER JOINwould be "show me all students, with their corresponding locker if they have one".
This might be a general student list, or could be used to identify students with no locker.
Returns 100 rows

LEFT OUTER JOIN将是“向我展示所有学生，如果他们有相应的储物柜”。
这可能是一个一般的学生名单，或者可以用来识别没有储物柜的学生。
返回 100 行

RIGHT OUTER JOINwould be "show me all lockers, and the students assigned to them if there are any".
This could be used to identify lockers that have no students assigned, or lockers that have too many students.
Returns 80 rows(list of 70 students in the 40 lockers, plus the 10 lockers with no student)

RIGHT OUTER JOIN将是“向我展示所有储物柜，以及分配给它们的学生（如果有的话）”。
这可用于识别没有分配学生的储物柜，或有太多学生的储物柜。
返回 80 行（40 个储物柜中 70 个学生的列表，加上没有学生的 10 个储物柜）

FULL OUTER JOINwould be silly and probably not much use.
Something like "show me all students and all lockers, and match them up where you can"
Returns 110 rows(all 100 students, including those without lockers. Plus the 10 lockers with no student)

FULL OUTER JOIN会很傻，可能没有多大用处。
类似“给我展示所有学生和所有储物柜，并尽可能匹配它们”
返回 110 行（所有 100 名学生，包括没有储物柜的学生。加上没有学生的 10 个储物柜）

CROSS JOINis also fairly silly in this scenario.
It doesn't use the linked lockernumberfield in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.
Returns 5000 rows(100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.

在这种情况下，CROSS JOIN也相当愚蠢。
它不使用lockernumber学生表中的链接字段，因此您基本上最终会得到每个可能的学生与储物柜配对的巨大列表，无论它是否确实存在。
返回 5000 行（100 名学生 x 50 个储物柜）。作为将新学生与空储物柜相匹配的起点，可能很有用（通过过滤）。

回答by Joel Coehoorn

There are three basic types of join:

共有三种基本类型的连接：

• INNERjoin compares two tables and only returns results where a match exists. Records from the 1st table are duplicated when they match multiple results in the 2nd. INNER joins tend to make result sets smaller, but because records can be duplicated this isn't guaranteed.
• CROSSjoin compares two tables and return every possible combination of rows from both tables. You can get a lot of results from this kind of join that might not even be meaningful, so use with caution.
• OUTERjoin compares two tables and returns data when a match is available or NULL values otherwise. Like with INNER join, this will duplicate rows in the one table when it matches multiple records in the other table. OUTER joins tend to make result sets larger, because they won't by themselves remove any records from the set. You must also qualify an OUTER join to determine when and where to add the NULL values:
  • LEFTmeans keep all records from the 1st table no matter what and insert NULL values when the 2nd table doesn't match.
  • RIGHTmeans the opposite: keep all records from the 2nd table no matter what and insert NULL values whent he 1st table doesn't match.
  • FULLmeans keep all records from both tables, and insert a NULL value in either table if there is no match.

• INNERjoin 比较两个表，只返回存在匹配项的结果。当第一个表中的记录与第二个表中的多个结果匹配时，它们将被复制。INNER 连接倾向于使结果集更小，但因为记录可以复制，所以不能保证。
• CROSSjoin 比较两个表并返回两个表中行的所有可能组合。您可以从这种连接中获得很多甚至可能没有意义的结果，因此请谨慎使用。
• OUTERjoin 比较两个表并在匹配可用时返回数据，否则返回 NULL 值。与 INNER join 一样，当它匹配另一个表中的多条记录时，这将复制一个表中的行。OUTER joins 倾向于使结果集更大，因为它们本身不会从集合中删除任何记录。您还必须限定 OUTER 连接以确定添加 NULL 值的时间和位置：
  • LEFT意味着无论如何都要保留第一个表中的所有记录，并在第二个表不匹配时插入 NULL 值。
  • RIGHT意思相反：无论如何都保留第二个表中的所有记录，并在第一个表不匹配时插入 NULL 值。
  • FULL表示保留两个表中的所有记录，如果没有匹配项，则在任一表中插入一个 NULL 值。

Often you see will the OUTERkeyword omitted from the syntax. Instead it will just be "LEFT JOIN", "RIGHT JOIN", or "FULL JOIN". This is done because INNER and CROSS joins have no meaning with respect to LEFT, RIGHT, or FULL, and so these are sufficient by themselves to unambiguously indicate an OUTER join.

您经常会看到OUTER语法中省略了关键字。相反，它只会是“LEFT JOIN”、“RIGHT JOIN”或“FULL JOIN”。这样做是因为 INNER 和 CROSS 连接对于 LEFT、RIGHT 或 FULL 没有意义，因此它们本身就足以明确指示 OUTER 连接。

Here is an example of when you might want to use each type:

以下是您可能想要使用每种类型的示例：

• INNER: You want to return all records from the "Invoice" table, along with their corresponding "InvoiceLines". This assumes that every valid Invoice will have at least one line.
• OUTER: You want to return all "InvoiceLines" records for a particular Invoice, along with their corresponding "InventoryItem" records. This is a business that also sells service, such that not all InvoiceLines will have an IventoryItem.
• CROSS: You have a digits table with 10 rows, each holding values '0' through '9'. You want to create a date range table to join against, so that you end up with one record for each day within the range. By CROSS-joining this table with itself repeatedly you can create as many consecutive integers as you need (given you start at 10 to 1st power, each join adds 1 to the exponent). Then use the DATEADD() function to add those values to your base date for the range.

• INNER：您想要从“Invoice”表中返回所有记录，以及它们对应的“InvoiceLines”。这假设每个有效发票至少有一行。
• OUTER：您想要返回特定发票的所有“InvoiceLines”记录及其对应的“InventoryItem”记录。这是一项也销售服务的业务，因此并非所有 InvoiceLine 都具有 IventoryItem。
• CROSS：您有一个包含 10 行的数字表，每行都包含从“0”到“9”的值。您想要创建一个日期范围表以进行联接，以便您最终在该范围内的每一天都有一条记录。通过反复将这个表与其自身交叉连接，您可以根据需要创建任意数量的连续整数（假设您从 10 的 1 次方开始，每个连接都将指数加 1）。然后使用 DATEADD() 函数将这些值添加到范围的基准日期。

回答by j_random_hacker

There are only 4 kinds:

只有4种：

1. Inner join: The most common type. An output row is produced for every pair of input rows that match on the join conditions.
2. Left outer join: The same as an inner join, except that if there is any row for which no matching row in the table on the right can be found, a row is output containing the values from the table on the left, with NULLfor each value in the table on the right. This means that every row from the table on the left will appear at least once in the output.
3. Right outer join: The same as a left outer join, except with the roles of the tables reversed.
4. Full outer join: A combination of left and right outer joins. Every row from both tables will appear in the output at least once.

1. 内连接：最常见的类型。为符合连接条件的每对输入行生成一个输出行。
2. 左外连接：与内连接相同，除了如果在右侧表中找不到匹配行的任何行，则输出包含左侧表中的值的行，NULL对于每个右表中的值。这意味着左侧表格中的每一行都将在输出中至少出现一次。
3. 右外连接：与左外连接相同，只是表的角色颠倒了。
4. 全外连接：左右外连接的组合。两个表中的每一行都将在输出中至少出现一次。

A "cross join" or "cartesian join" is simply an inner join for which no join conditions have been specified, resulting in all pairs of rows being output.

“交叉连接”或“笛卡尔连接”只是一个没有指定连接条件的内部连接，导致输出所有行对。

Thanks to RusselH for pointing out FULL joins, which I'd omitted.

感谢 RusselH 指出 FULL 连接，我已经省略了。

回答by Laxmi

SQL JOINS difference:

SQL JOINS 区别：

Very simple to remember :

很容易记住：

INNER JOINonly show records common to both tables.

INNER JOIN只显示两个表共有的记录。

OUTER JOINall the content of the both tables are merged together either they are matched or not.

OUTER JOIN两个表的所有内容都合并在一起，要么匹配要么不匹配。

LEFT JOINis same as LEFT OUTER JOIN- (Select records from the first (left-most) table with matching right table records.)

LEFT JOIN与LEFT OUTER JOIN- （从第一个（最左边）表中选择记录与匹配的右表记录。）

RIGHT JOINis same as RIGHT OUTER JOIN- (Select records from the second (right-most) table with matching left table records.)

RIGHT JOIN与RIGHT OUTER JOIN- （从第二个（最右边）表中选择记录与左表记录匹配。）

回答by JoshBerke

Check out Join (SQL) on Wikipedia

查看Wikipedia 上的 Join (SQL)

• Inner join - Given two tables an inner join returns all rows that exist in both tables

• left / right (outer) join - Given two tables returns all rows that exist in either the left or right table of your join, plus the rows from the other side will be returned when the join clause is a match or null will be returned for those columns

• Full Outer - Given two tables returns all rows, and will return nulls when either the left or right column is not there

• Cross Joins - Cartesian join and can be dangerous if not used carefully

• 内连接 - 给定两个表，内连接返回两个表中存在的所有行

• 左/右（外）连接 - 给定两个表返回您的连接的左表或右表中存在的所有行，另外当连接子句匹配时将返回另一侧的行，否则将返回 null那些列

• Full Outer - 给定两个表返回所有行，当左列或右列不存在时将返回空值

• 交叉连接 - 笛卡尔连接，如果不小心使用可能会很危险

回答by lfvv

Making it more visible might help. One example:

让它更明显可能会有所帮助。一个例子：

Table 1:

表格1：

ID_STUDENT STUDENT_NAME

ID_STUDENT STUDENT_NAME

1               Raony
2               Diogo
3               Eduardo
4               Luiz

Table 2:

表 2：

ID_STUDENT LOCKER

ID_学生储物柜

3               l1
4               l2
5               l3

What I get when I do:

当我这样做时我得到了什么：

-Inner join of Table 1 and Table 2: 

    - Inner join returns both tables merged only when the key 
      (ID_STUDENT) exists in both tables

    ID_STUDENT       STUDENT_NAME      LOCKER   

        3               Eduardo          l1
        4               Luiz             l2

-Left join of Table 1 and Table 2:

    - Left join merges both tables with all records form table 1, in 
      other words, there might be non-populated fields from table 2

    ID_ESTUDANTE    NOME_ESTUDANTE     LOCKER   

        1               Raony            -
        2               Diogo            -
        3               Eduardo          l1
        4               Luiz             l2

-Right join of table 1 and table 2:

    - Right join merges both tables with all records from table 2, in 
      other words, there might be non-populated fields from table 1

    ID_STUDENT        STUDENT_NAME     LOCKER   

        3               Eduardo          l1
        4               Luiz             l2
        5               -                l3

-Outter join of table 1 and table 2:

    - Returns all records from both tables, in other words, there
      might be non-populated fields either from table 1 or 2.

    ID_STUDENT        STUDENT_NAME     LOCKER   
        1               Raony            -
        2               Diogo            -
        3               Eduardo          l1
        4               Luiz             l2
        5               -                l3

回答by RussellH

LEFT JOINand RIGHT JOINare types of OUTER JOINs.

LEFT JOIN并且RIGHT JOIN是类型OUTER JOIN秒。

INNER JOINis the default -- rows from both tables must match the join condition.

INNER JOIN是默认值——来自两个表的行必须匹配连接条件。

回答by Peth? Jonatán

Inner join: Only show rows, when has it data from both of the tables.

内连接：只显示行，当有来自两个表的数据时。

Outer join: (left/right): Show the all result from the left / righttable with the paired row(s), if it existsor not.

外连接：（左/右）：显示来自左/右表的所有结果和成对的行（s），如果它存在或不存在。

回答by HM Nayem

At first you have to understand what does join do? We connect multiple table and get specific result from the joined tables. The simplest way to do this is cross join.

首先你要明白join是做什么的？我们连接多个表并从连接的表中获得特定的结果。最简单的方法是交叉连接。

Lets say tableA has two column A and B. And tableB has three column C and D. If we apply cross join it will produce lot of meaningless row. Then we have to match using primary key to get actual data.

假设 tableA 有两列 A 和 B。而 tableB 有三列 C 和 D。如果我们应用交叉连接，它会产生很多无意义的行。然后我们必须使用主键进行匹配以获取实际数据。

Left:it will return all records from left table and matched record from right table.

左：它将返回左表中的所有记录和右表中匹配的记录。

Right:it will return opposite to Left join. It will return all records from right table and matched records from left table.

右：它将返回与左连接相反。它将返回右表中的所有记录和左表中匹配的记录。

Inner:This is like intersection. It will return only matched records from both table.

内：这就像交叉点。它将只返回两个表中匹配的记录。

Outer:And this is like union. It will return all available record from both table.

外层：这就像工会。它将返回两个表中的所有可用记录。

Some times we don't need all of the data, and also we should need only common data or records. we can easily get it using these join methods. Remember left and right join also are outer join.

有时我们不需要所有的数据，而且我们应该只需要通用的数据或记录。我们可以使用这些连接方法轻松获得它。记住左右连接也是外连接。

You can get all records just using cross join. But it could be expensive when it comes to millions of records. So make it simple by using left, right, inner or outer join.

您只需使用交叉连接即可获取所有记录。但是当涉及数百万条记录时，它可能会很昂贵。因此，通过使用左联接、右联接、内联接或外联接来使其变得简单。

thanks

谢谢