什么时候用LEFT JOIN，什么时候用INNER JOIN? [英] When to use LEFT JOIN and when to use INNER JOIN?

问题描述

我觉得我总是被教导使用 LEFT JOIN 并且我经常看到它们与 INNER 混合使用以在几段代码中完成相同类型的查询应该在不同的页面上做同样的事情.这是:

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:

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:

我看到很多:

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

这看起来像 LEFT 也可能是 INNER 有什么问题吗?

This seems like the LEFT may as well be INNER is there any catch?

推荐答案

有什么问题吗?是的 - 左联接是外联接的一种形式，而内联接是内联接的一种形式.

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 revoir |
|  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 revoir |
+----+-----------+----+-----------+

嗯，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 revoir |
|  5 | buongiorno | NULL | NULL      |
+----+------------+------+-----------+

哇，5 行！发生了什么?

Wow, 5 rows! What happened?

外部连接，例如 left join 保留不匹配的行——因此左连接查询保留 id 为 2 和 5 的行.其余列用 NULL 填充.

Outer joins such as left join preserve 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.

换句话说，左连接和内连接不可互换.

In other words, left and inner joins are not interchangeable.

这篇关于什么时候用LEFT JOIN，什么时候用INNER JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！