MySQL 什么时候用LEFT JOIN，什么时候用INNER JOIN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9798020/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When to use LEFT JOIN and when to use INNER JOIN?
提问by cerd
I feel like I was always taught to use LEFT JOINs and I often see them mixed with INNERs to accomplish the same type of query throughout several pieces of code that are supposed to do the same thing on different pages. Here goes:
我觉得我总是被教导使用LEFT JOINs 并且我经常看到它们与INNERs混合在一起以在几段代码中完成相同类型的查询，这些代码应该在不同的页面上做同样的事情。开始：
SELECT ac.reac, pt.pt_name, soc.soc_name, pt.pt_soc_code
FROM
AECounts ac
INNER JOIN 1_low_level_term llt on ac.reac = llt.llt_name
LEFT JOIN 1_pref_term pt ON llt.pt_code = pt.pt_code
LEFT JOIN 1_soc_term soc ON pt.pt_soc_code = soc.soc_code
LIMIT 100,10000
Thats one I am working on:
那是我正在研究的一个：
I see a lot like:
我看到很多类似：
SELECT COUNT(DISTINCT p.`case`) as count
FROM FDA_CaseReports cr
INNER JOIN ae_indi i ON i.isr = cr.isr
LEFT JOIN ae_case_profile p ON cr.isr = p.isr
This seems like the LEFT may as well be INNER is there any catch?
这似乎 LEFT 也可能是 INNER 有什么问题吗？
回答by Matt Fenwick
Is there any catch? Yes there is -- left joins are a form of outer join, while inner joins are a form of, well, inner join.
有什么收获吗？是的，有 - 左联接是外联接的一种形式，而内联接是内联接的一种形式。
Here's examples that show the difference. We'll start with the base data:
以下是显示差异的示例。我们将从基础数据开始：
mysql> select * from j1;
+----+------------+
| id | thing |
+----+------------+
| 1 | hi |
| 2 | hello |
| 3 | guten tag |
| 4 | ciao |
| 5 | buongiorno |
+----+------------+
mysql> select * from j2;
+----+-----------+
| id | thing |
+----+-----------+
| 1 | bye |
| 3 | tschau |
| 4 | au revtheitroad |
| 6 | so long |
| 7 | tschuessi |
+----+-----------+
And here we'll see the difference between an inner join and a left join:
在这里我们将看到内连接和左连接之间的区别：
mysql> select * from j1 inner join j2 on j1.id = j2.id;
+----+-----------+----+-----------+
| id | thing | id | thing |
+----+-----------+----+-----------+
| 1 | hi | 1 | bye |
| 3 | guten tag | 3 | tschau |
| 4 | ciao | 4 | au revtheitroad |
+----+-----------+----+-----------+
Hmm, 3 rows.
嗯，3 行。
mysql> select * from j1 left join j2 on j1.id = j2.id;
+----+------------+------+-----------+
| id | thing | id | thing |
+----+------------+------+-----------+
| 1 | hi | 1 | bye |
| 2 | hello | NULL | NULL |
| 3 | guten tag | 3 | tschau |
| 4 | ciao | 4 | au revtheitroad |
| 5 | buongiorno | NULL | NULL |
+----+------------+------+-----------+
Wow, 5 rows! What happened?
哇，5排！发生了什么？
Outer joins such as left joinpreserve rows that don't match -- so rows with id 2 and 5 are preserved by the left join query. The remaining columns are filled in with NULL.
外部联接，例如left join保留不匹配的行——因此左联接查询保留 id 为 2 和 5 的行。其余列用 NULL 填充。
In other words, left and inner joins are not interchangeable.
换句话说，左连接和内连接不可互换。
回答by rob
Here's a rough answer, that is sort of how I think about joins. Hoping this will be more helpful than a very precise answer due to the aforementioned math issues... ;-)
这是一个粗略的答案，这就是我对连接的看法。由于上述数学问题，希望这比非常精确的答案更有帮助...... ;-)
Inner joins narrow down the set of rows returns. Outer joins (left or right) don't change number of rows returned, but just "pick up" additional columns if possible.
内部连接缩小了返回的行集。外连接（左或右）不会改变返回的行数，但如果可能的话只是“选择”额外的列。
In your first example, the result will be rows from AECountsthat match the conditions specified to the 1_low_level_termtable. Then for those rows, it triesto join to 1_pref_termand 1_soc_term. But if there's no match, the rows remain and the joined in columns are null.
在您的第一个示例中，结果将是AECounts与指定给1_low_level_term表的条件匹配的行。然后对于这些行，它尝试加入1_pref_term和1_soc_term。但是，如果没有匹配项，则行将保留，并且加入的列为空。
回答by philf2b
An INNER JOIN will only return the rows where there are matching values in both tables, whereas a LEFT JOIN will return ALL the rows from the LEFT table even if there is no matching row in the RIGHT table
INNER JOIN 将只返回两个表中有匹配值的行，而 LEFT JOIN 将返回 LEFT 表中的所有行，即使 RIGHT 表中没有匹配的行
A quick example
一个简单的例子
TableA
ID Value
1 TableA.Value1
2 TableA.Value2
3 TableA.Value3
TableB
ID Value
2 TableB.ValueB
3 TableB.ValueC
An INNER JOIN produces:
INNER JOIN 产生：
SELECT a.ID,a.Value,b.ID,b.Value
FROM TableA a INNER JOIN TableB b ON b.ID = a.ID
a.ID a.Value b.ID b.Value
2 TableA.Value2 2 TableB.ValueB
3 TableA.Value3 3 TableB.ValueC
A LEFT JOIN produces:
LEFT JOIN 产生：
SELECT a.ID,a.Value,b.ID,b.Value
FROM TableA a LEFT JOIN TableB b ON b.ID = a.ID
a.ID a.Value b.ID b.Value
1 TableA.Value1 NULL NULL
2 TableA.Value2 2 TableB.ValueB
3 TableA.Value3 3 TableB.ValueC
As you can see, the LEFT JOIN includes the row from TableA where ID = 1 even though there's no matching row in TableB where ID = 1, whereas the INNER JOIN excludes the row specifically because there's no matching row in TableB
如您所见，LEFT JOIN 包括 TableA 中 ID = 1 的行，即使 TableB 中 ID = 1 没有匹配行，而 INNER JOIN 专门排除该行，因为 TableB 中没有匹配行
HTH
HTH
回答by Scott
For newbies, because it helped me when I was one: an INNER JOIN is always a subset of a LEFT or RIGHT JOIN, and all of these are always subsets of a FULL JOIN. It helped me understand the basic idea.
对于新手来说，因为它在我小时候对我有帮助：INNER JOIN 始终是 LEFT 或 RIGHT JOIN 的子集，而所有这些始终是 FULL JOIN 的子集。它帮助我理解了基本思想。
回答by Seung Bae Im
Use an inner join when you want only the results that appear in both tables that matches the Join condition.
当您只想要出现在与连接条件匹配的两个表中的结果时，使用内部连接。
Use a left join when you want all the results from Table A, but if Table B has data relevant to some of Table A's records, then you also want to use that data in the same query.
当您想要来自表 A 的所有结果时使用左联接，但如果表 B 具有与表 A 的某些记录相关的数据，那么您还希望在同一查询中使用该数据。
Use a full join when you want all the results from both Tables.
当您想要来自两个表的所有结果时，请使用完全联接。
