何时使用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

这似乎也可能是左"吗?

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

嗯，三行.

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

哇，五排！发生什么事了?

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屋！